467,921 Members | 1,408 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,921 developers. It's quick & easy.

Big problem with triggers

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?
Jul 20 '05 #1
  • viewed: 1732
Share:
1 Reply
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?

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by raulgz | last post: by
4 posts views Thread by Mark Flippin | last post: by
1 post views Thread by tim.pascoe | last post: by
1 post views Thread by Christoph Graf | last post: by
8 posts views Thread by =?Utf-8?B?SmFrb2IgTGl0aG5lcg==?= | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.