Big problem with triggers

[BUSHII]

I have little problem and I dont have any idea how to make my trigger.

I have table MyTable where I have many column with almost same name and same
type (Grp1,Grp2,Grp3,Grp4...Grp50 char(1)).
I`d like to make some trigger on UPDATE this table. I must to check which
column was changed.

For example to check if Grp1 was changed I can try this:

ALTER TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
DECLARE @OldValue char(1)
DECLARE @NewValue char(1)
BEGIN
SET @OldValue = (SELECT Grp1 FROM DELETED)
SET @NewValue = (SELECT Grp1 FROM INSERTED)
IF (@OldValue <> @NewValue)
BEGIN
....
....
....
END
END
Of course I can do this step by step, for all columns, but it`s not good
option.
I tried to make this trigger more dynamicaly, but this not worked (Server:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DELETED'.)

ALTER TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
DECLARE @OldValue char(1)
DECLARE @NewValue char(1)
DECLARE @MyQry VarChar(1000)
DECLARE @ActGrp VarChar(2)
BEGIN
@ActGrp = '1'
SET @MyQry ='
SET @OldValue = (SELECT Grp'+@ActGrp+' FROM DELETED)
SET @NewValue = (SELECT Grp'+@ActGrp+' FROM INSERTED)
IF (@OldValue <> @NewValue)
BEGIN
....
....
....
END
'
END

:(

Can anybody help me? How can I easy check all Grp1...Grp50 to know where
colums was changed?

[John Bell]

Hi

Triggers are activated on a statement and not per row changed, therefore the
use of variables in your trigger to store old and new values will not
produce predictable results.

In general it is better to keep your triggers as short as possible to avoid
prolonging your transactions. If you are trying to produce and audit trail
then I would keep the comparisons external to the trigger and follow the
example of auditing in
http://msdn.microsoft.com/library/de...reate_4hk5.asp

John

"BUSHII" <pi****@robcom.com.pl> wrote in message
news:cb**********@atlantis.news.tpi.pl...

I have little problem and I dont have any idea how to make my trigger.

I have table MyTable where I have many column with almost same name and same type (Grp1,Grp2,Grp3,Grp4...Grp50 char(1)).
I`d like to make some trigger on UPDATE this table. I must to check which
column was changed.

For example to check if Grp1 was changed I can try this:

ALTER TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
DECLARE @OldValue char(1)
DECLARE @NewValue char(1)
BEGIN
SET @OldValue = (SELECT Grp1 FROM DELETED)
SET @NewValue = (SELECT Grp1 FROM INSERTED)
IF (@OldValue <> @NewValue)
BEGIN
....
....
....
END
END
Of course I can do this step by step, for all columns, but it`s not good
option.
I tried to make this trigger more dynamicaly, but this not worked (Server:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DELETED'.)

ALTER TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS
DECLARE @OldValue char(1)
DECLARE @NewValue char(1)
DECLARE @MyQry VarChar(1000)
DECLARE @ActGrp VarChar(2)
BEGIN
@ActGrp = '1'
SET @MyQry ='
SET @OldValue = (SELECT Grp'+@ActGrp+' FROM DELETED)
SET @NewValue = (SELECT Grp'+@ActGrp+' FROM INSERTED)
IF (@OldValue <> @NewValue)
BEGIN
....
....
....
END
'
END

:(

Can anybody help me? How can I easy check all Grp1...Grp50 to know where
colums was changed?