[ask] trigger in sqlserver not working, HELPP!

i have this trigger in my database :

ALTER TRIGGER dbo.AddVoucher
ON dbo.User_AddVoucher
AFTER INSERT
AS
SET NOCOUNT ON;

DECLARE @.UserId int,
@.Add_id int,
@.voucher_id char,
@.Kredit money,
@.date smalldatetime,
@.last_balance money,
@.voucher_status char

SELECT @.UserId = UserId,
@.voucher_id = Voucher_ID,
@.Add_id = Add_id,
@.date = Deposit_Date
FROM Inserted

SELECT @.Kredit= Voucher_Value,
@.voucher_status = Voucher_Status
FROM Voucher
WHERE Voucher_ID = @.voucher_id

INSERT INTO User_Balance(AddVoucher_ID, UserId, Update_Type, Update_Date)
VALUES (@.Add_id,@.UserId, 'Kredit',@.date)

select @.last_balance = Balance
from User_Balance
WHERE UserId = @.UserId and Balance = (select TOP 1 Balance User_Balance where UserId = @.UserId order by Update_Id DESC)

if (@.voucher_status = 'active')

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

-- Insert statements for trigger here
BEGIN
update User_Balance
set Balance = @.last_balance + @.Kredit
where AddVoucher_ID = @.Add_id

update Voucher
set Sold_Date = @.date
where Voucher_ID = @.voucher_id

END

ELSE
BEGIN
raiserror ('Voucher is not valid',0,1)
rollback transaction
END
go

the problem is the update function is not working and the if statement always put to 'FALSE'

do you think anything wrong with the code

Thats perhaps because of the way you have declared the @.voucher_status parameter. char by default is same as char(1). So your @.voucher_status will only have an "a" if the status was "active". So change your datatype to @.varchar(10).

|||

oh my god...i didn't notice it...just a little mistake there, thanks bro for your help....

it runs smoothly now...

[ask] how to run a trigger or store precedure in certain date?

hello, i have a database that will be updated on a certain date

i have a column "UPDATE_DATE" which specifies the updating date, my question is

"how should i make the trigger or stored procedure runs only on the date that has been specified"

thanks for the assistance

Create a job that runs once everyday and compares the date on the dat with the value in the column, and calls the proc if they match.|||

ok, i think that will be good idea since sqlexpress doesn't come with sqlagent

ok then, i'll try to code by that algorithm. thx anyway bro

@louis :: SQL - Cascading Delete, or Delete Trigger, maintaining Referential Integrity - PLEASE

I am having great difficulty with cascading deletes, delete triggers and referential integrity.

The database is in First Normal Form.

I have some tables that are child tables with two foreign keyes to two different parent tables, for example:

Table A

/ \

Table B Table C

\ /

Table D

So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.

SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.

Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.

When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?

This is an example of my delete trigger:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;

And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.

So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.

So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).

Hope this makes sense...

Thanks,

Josh

It is hard to advise how to impove abstract structure. No other answers that create all references with "do not enforce" clause and maintain all integrity by the triggers.|||

Yeah, the whole cascading thing can get confusing. Can you post a more complete sample? This trigger will not work as it is:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;

You need to do something more like:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete B
FROM B
join deleted
on B.Akey = deleted.Akey

Delete C
FROM C
join deleted
on C.Akey = deleted.Akey
go

|||correct me if im wrong.... the triggers in sql server r 'after triggers' i.e after the delete operation in parent table is performed, only then will the trigger fire...now while deleting from parent table itself, it gives an error as at that time the reference is their.....sorry i havent tried it, but this is wat shud be hapenning .....|||

if u know the parent table and the primary keys refered by the child(fk) tables ... use queries to get rid of the child data first..somethin like

BEGIN TRANSACTION

delete from child1 where refered_column in (select pk_values from parent where value='i wanna delete' ) --nest to more levels if required...

select pk_values from parent where value='i wanna delete'

END

this may seem to be manual work but can be automized......

|||

Well, sure I can do it manually, but that is not really the question.

In SQL there are AFTER triggers, so one would assume that the ON trigger happens as, or before the delete, providing us with the ability to prevent the restraint errors within the database. Otherwise what is the point? I was always taught that there are things that the databae does well, and this is supposed to be one of those things: maintain referential integrity. However MS SQL seems to not be able to do one of the main things a database is supposed to do really well. Even access does this better.

Here is the revised trigger that actualy works:

CREATE TRIGGER [MeetingDelete]
ON [coordinateameeting].[tblMeetings]
FOR DELETE
AS
Delete from tblMeetingAttendees where MeetingID in (select ID from deleted)
Delete from tblMeetingLocations where MeetingID in (select ID from deleted)

However, it only works if I turn RI off completely. At that point I might as well just be using text files.

Does anyone have an explanation why I can only get the delete triggers to fire if I turn referential integrity off?

Thanks,

Josh

|||thats wat josh.... u need to turn off the RI if u want the trigger to work... as the trigger is AFTER , it will delete from the parent table, then the child table, but in the first statement itself it'll get an error msg for RI. had SQLSERVER had BEFORE trigger(no offence to MS but ORACLE has them) , this would have worked...it'll then delete from the child tables first before parent...so while deleting from parent there would be no error....evn with the RI on.

@louis :: SQL - Cascading Delete, or Delete Trigger, maintaining Referential Integrity - PLE

I am having great difficulty with cascading deletes, delete triggers and referential integrity.

The database is in First Normal Form.

I have some tables that are child tables with two foreign keyes to two different parent tables, for example:

Table A

/ \

Table B Table C

\ /

Table D

So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.

SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.

Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.

When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?

This is an example of my delete trigger:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;

And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.

So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.

So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).

Hope this makes sense...

Thanks,

Josh

It is hard to advise how to impove abstract structure. No other answers that create all references with "do not enforce" clause and maintain all integrity by the triggers.|||

Yeah, the whole cascading thing can get confusing. Can you post a more complete sample? This trigger will not work as it is:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;

You need to do something more like:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete B
FROM B
join deleted
on B.Akey = deleted.Akey

Delete C
FROM C
join deleted
on C.Akey = deleted.Akey
go

|||correct me if im wrong.... the triggers in sql server r 'after triggers' i.e after the delete operation in parent table is performed, only then will the trigger fire...now while deleting from parent table itself, it gives an error as at that time the reference is their.....sorry i havent tried it, but this is wat shud be hapenning .....|||

if u know the parent table and the primary keys refered by the child(fk) tables ... use queries to get rid of the child data first..somethin like

BEGIN TRANSACTION

delete from child1 where refered_column in (select pk_values from parent where value='i wanna delete' ) --nest to more levels if required...

select pk_values from parent where value='i wanna delete'

END

this may seem to be manual work but can be automized......

|||

Well, sure I can do it manually, but that is not really the question.

In SQL there are AFTER triggers, so one would assume that the ON trigger happens as, or before the delete, providing us with the ability to prevent the restraint errors within the database. Otherwise what is the point? I was always taught that there are things that the databae does well, and this is supposed to be one of those things: maintain referential integrity. However MS SQL seems to not be able to do one of the main things a database is supposed to do really well. Even access does this better.

Here is the revised trigger that actualy works:

CREATE TRIGGER [MeetingDelete]
ON [coordinateameeting].[tblMeetings]
FOR DELETE
AS
Delete from tblMeetingAttendees where MeetingID in (select ID from deleted)
Delete from tblMeetingLocations where MeetingID in (select ID from deleted)

However, it only works if I turn RI off completely. At that point I might as well just be using text files.

Does anyone have an explanation why I can only get the delete triggers to fire if I turn referential integrity off?

Thanks,

Josh

|||thats wat josh.... u need to turn off the RI if u want the trigger to work... as the trigger is AFTER , it will delete from the parent table, then the child table, but in the first statement itself it'll get an error msg for RI. had SQLSERVER had BEFORE trigger(no offence to MS but ORACLE has them) , this would have worked...it'll then delete from the child tables first before parent...so while deleting from parent there would be no error....evn with the RI on.

@@TRANCOUNT difference between SQL 7 and SQL 2000 in trigger

During testing of an application, i noticed a difference between
SQL 2000 and SQL 7, both with identical config.

In a nutshell:
A table has a trigger for UPDATE and DELETE.
When a column in the table is UPDATED the following happens:

In autocommit mode, when entering a trigger the trancount equals
1 for both SQL 7 and 2000.

When the same update is performed in an explicit transaction
in SQL 7 @.@.TRANCOUNT equal 2, and in SQL 2000 @.@.TRANCOUNT equals 1.

Configuration is the same and there are no implicit transactions.

I don't need a work around as this will invalidate the migration
process as both products should behave identically.
What would influence the difference or why is there a difference?
Is there something which has been overlooked?

================================================== =======

The following code replicates the problem

Ensure implicit transactions are off in both versions at the server
level, thus defaulting to autocommitted mode.
Ensure sp_configure settings are identical.

Step 1: Create a DB called test:

Step 2: Execute the following under the context of test DB.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, Outrigger') = 1)

drop trigger [dbo].[trigtest]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[test]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[trancount]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)

drop table [dbo].[trancount]
GO

CREATE TABLE [dbo].[test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[text] [char] (10) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[trancount] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[trancount] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TRIGGER trigtest ON [dbo].[test]
FOR UPDATE, DELETE
AS
declare @.trancount int

select @.trancount = @.@.TRANCOUNT

insert into trancount ( trancount ) values ( @.trancount )

Step 3: Run the following against the DB, then check trancount table.

-- Add a record to the test table (trigger will not fire)
insert into test (text) values ( 'xxxx' );
go

-- Update the value (autocommit mode) to fire trigger
-- Under SQL 7 and 2000, trancount table will only indicate 1
tranaction open.
-- This is being performed in autocommit mode.
update test set text = 'test1'
go

-- Update value using an explicit transaction
-- Under SQL 7, trancount will equal 2 in trigger, in SQL 2000
trancount equals 1
begin transaction
update test set text = 'test2'
commit work
goNeil Rutherford (neil_rutherford@.yahoo.com) writes:
> During testing of an application, i noticed a difference between
> SQL 2000 and SQL 7, both with identical config.
> In a nutshell:
> A table has a trigger for UPDATE and DELETE.
> When a column in the table is UPDATED the following happens:
> In autocommit mode, when entering a trigger the trancount equals
> 1 for both SQL 7 and 2000.
> When the same update is performed in an explicit transaction
> in SQL 7 @.@.TRANCOUNT equal 2, and in SQL 2000 @.@.TRANCOUNT equals 1.
> Configuration is the same and there are no implicit transactions.
> I don't need a work around as this will invalidate the migration
> process as both products should behave identically.
> What would influence the difference or why is there a difference?
> Is there something which has been overlooked?

Apparently there was - consciously or by chance - a change in SQL2000.
I cannot say why, and indeed 2 would be a more expected result in
this situation.

But I would be interesting to know why this would be an issue? It sounds
to me like your triggers must be doing something quite interesting.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||BOL says:
"Microsoft SQL Server 2000 increments the transaction count within a
statement only when the transaction count is 0 at the start of the
statement. In SQL Server version 7.0, the transaction count is always
incremented, regardless of the transaction count at the start of the
statement. This can cause the value returned by @.@.TRANCOUNT in triggers to
be lower in SQL Server 2000 than it is in SQL Server version 7.0.

In SQL Server 2000, if a COMMIT TRANSACTION or COMMIT WORK statement is
executed in a trigger, and there is no corresponding explicit or implicit
BEGIN TRANSACTION statement at the start of the trigger, users may see
different behavior than on SQL Server version 7.0. Placing COMMIT
TRANSACTION or COMMIT WORK statements in a trigger is not recommended."

HTH
Igor

neil_rutherford@.yahoo.com (Neil Rutherford) wrote in message news:<d6fdc377.0408070451.3d79fc95@.posting.google.com>...
> During testing of an application, i noticed a difference between
> SQL 2000 and SQL 7, both with identical config.
> In a nutshell:
> A table has a trigger for UPDATE and DELETE.
> When a column in the table is UPDATED the following happens:
> In autocommit mode, when entering a trigger the trancount equals
> 1 for both SQL 7 and 2000.
> When the same update is performed in an explicit transaction
> in SQL 7 @.@.TRANCOUNT equal 2, and in SQL 2000 @.@.TRANCOUNT equals 1.
> Configuration is the same and there are no implicit transactions.
> I don't need a work around as this will invalidate the migration
> process as both products should behave identically.
> What would influence the difference or why is there a difference?
> Is there something which has been overlooked?
> ================================================== =======
> The following code replicates the problem
> Ensure implicit transactions are off in both versions at the server
> level, thus defaulting to autocommitted mode.
> Ensure sp_configure settings are identical.
> Step 1: Create a DB called test:
> Step 2: Execute the following under the context of test DB.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, Outrigger') = 1)
> drop trigger [dbo].[trigtest]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[test]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[trancount]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[trancount]
> GO
> CREATE TABLE [dbo].[test] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [text] [char] (10) NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[trancount] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [trancount] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TRIGGER trigtest ON [dbo].[test]
> FOR UPDATE, DELETE
> AS
> declare @.trancount int
> select @.trancount = @.@.TRANCOUNT
> insert into trancount ( trancount ) values ( @.trancount )
>
> Step 3: Run the following against the DB, then check trancount table.
> -- Add a record to the test table (trigger will not fire)
> insert into test (text) values ( 'xxxx' );
> go
> -- Update the value (autocommit mode) to fire trigger
> -- Under SQL 7 and 2000, trancount table will only indicate 1
> tranaction open.
> -- This is being performed in autocommit mode.
> update test set text = 'test1'
> go
> -- Update value using an explicit transaction
> -- Under SQL 7, trancount will equal 2 in trigger, in SQL 2000
> trancount equals 1
> begin transaction
> update test set text = 'test2'
> commit work
> go|||Igor Raytsin (igorray@.yahoo.com) writes:
> BOL says:
> "Microsoft SQL Server 2000 increments the transaction count within a
> statement only when the transaction count is 0 at the start of the
> statement. In SQL Server version 7.0, the transaction count is always
> incremented, regardless of the transaction count at the start of the
> statement. This can cause the value returned by @.@.TRANCOUNT in triggers to
> be lower in SQL Server 2000 than it is in SQL Server version 7.0.
> In SQL Server 2000, if a COMMIT TRANSACTION or COMMIT WORK statement is
> executed in a trigger, and there is no corresponding explicit or implicit
> BEGIN TRANSACTION statement at the start of the trigger, users may see
> different behavior than on SQL Server version 7.0. Placing COMMIT
> TRANSACTION or COMMIT WORK statements in a trigger is not recommended."

It's even documented! I didn't know that. Thanks, Igor!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your help guys.

Someone put logic in a trigger to only continue
if the @.@.TRANCOUNT came from an explicit transaction in
SQL Server 7 and the @.@.TRANCOUNT > 1

Like mentioned, in SQL 7 this works, but in SQL 2000..
it breached the integrity of a whole data warehouse system
test.

I have to convince the developers and management that
there is a change between versions. The developers
are convinced there is difference between the server
config and they believe that both versions should
work identically.

Unless I'm going blind.. where in books on-line is
the passage above?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Neil Rutherford (neil_rutherford@.yahoo.com) writes:
> Someone put logic in a trigger to only continue
> if the @.@.TRANCOUNT came from an explicit transaction in
> SQL Server 7 and the @.@.TRANCOUNT > 1

Dubious usage. I have not checked, but recursive trigger calls
may have slipped.

True, I have stored procedures which barf if you call them without
a transaction in progress, but that is because they perform only
half the job.

> I have to convince the developers and management that
> there is a change between versions. The developers
> are convinced there is difference between the server
> config and they believe that both versions should
> work identically.

Obviously there is a difference between versions. This is nothing you
configure.

> Unless I'm going blind.. where in books on-line is
> the passage above?

I searched for the string "the transaction count is always incremented"
and found two hits.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

@@ROWCOUNT = 0 in a trigger

I'm going over some triggers in an old database we have and have come
accross the following...
...
IF (@.ROWCOUNT = 0)
RETURN
...
Can a trigger ever fire if zero rows were affected? Should I remove this
from the trigger?
Thanks.>> Can a trigger ever fire if zero rows were affected?
Yes, the trigger will fire once the corresponding DML is invoked. The number
of rows has no impact on its execution.
As a recommended practice, you should not.
Anith|||> Can a trigger ever fire if zero rows were affected? Should I remove this
> from the trigger?
Yes it will be fired. Just be careful using "set nocount on" before
inquiring for @.@.rowcount.
Example:
create table t1 (
c1 int not null default(0)
)
go
create trigger tr_t1_ins on t1
for insert
as
set nocount on
print '(' + ltrim(@.@.rowcount) + ' row(s) inserted' + ')'
go
create trigger tr_t1_ins_1 on t1
for insert
as
print '(' + ltrim(@.@.rowcount) + ' row(s) inserted' + ')'
go
create trigger tr_t1_upd on t1
for update
as
print '(' + ltrim(@.@.rowcount) + ' row(s) updated' + ')'
go
create trigger tr_t1_del on t1
for delete
as
print '(' + ltrim(@.@.rowcount) + ' row(s) deleted' + ')'
go
insert into t1 default values
go
update t1
set c1 = 1
where c1 = 2
go
delete t1
where c1 = 2
go
drop table t1
go
AMB
"C-W" wrote:

> I'm going over some triggers in an old database we have and have come
> accross the following...
> ...
> IF (@.ROWCOUNT = 0)
> RETURN
> ...
> Can a trigger ever fire if zero rows were affected? Should I remove this
> from the trigger?
> Thanks.
>
>|||Thanks for the information,
Chris
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:u25d8jPnFHA.3480@.TK2MSFTNGP10.phx.gbl...
> I'm going over some triggers in an old database we have and have come
> accross the following...
> ...
> IF (@.ROWCOUNT = 0)
> RETURN
> ...
> Can a trigger ever fire if zero rows were affected? Should I remove this
> from the trigger?
> Thanks.
>|||As others mentioned, the trigger will fire once per the corresponding
statement. This includes 0, 1, >1 affected rows.
In some cases you may want to apply different logic depending on the number
of affected rows. @.@.rowcount is your best tool to achieve this:
IF @.@.rowcount - 0 RETURN;
IF @.@.rowcount = 1
BEGIN -- 1 affected row logic
..
END
ELSE
BEGIN -- >1 affected rows logic
..
END
BG, SQL Server MVP
www.SolidQualityLearning.com
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:u25d8jPnFHA.3480@.TK2MSFTNGP10.phx.gbl...
> I'm going over some triggers in an old database we have and have come
> accross the following...
> ...
> IF (@.ROWCOUNT = 0)
> RETURN
> ...
> Can a trigger ever fire if zero rows were affected? Should I remove this
> from the trigger?
> Thanks.
>