By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,928 Members | 1,810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,928 IT Pros & Developers. It's quick & easy.

Big problem with triggers

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.