Trigger: Proper error handler need help 1

[SteveMe]

CREATE TRIGGER Hedge_CodePassword_DateUser_Change
ON [Hedge_CodePassword]
FOR update
AS

--update [Hedge_CodePassword] set TimeStamp = getdate(), CreateUserID = user_name()

UPDATE [Hedge_CodePassword]
set TimeStamp = getdate(), CreateUserID = user_name()
from [Hedge_CodePassword], inserted
where inserted.UserID = [Hedge_CodePassword].UserID



What would be the proper error handling that I would need here? Also I have both updates that work. I commented the one out. Which one is better? Is there a way to do to use a variable that implies the current table or does someone have a tsql script that can place this code into each table in a database? Any help would be greatly appreciated. Thanks

 

[rdroske]

The first thing you want to watch out for is that this trigger updates the same table that is is defined on. Will loop forever.

 

[SteveMe]

rdroske I'm unaware of this. It appears not to loop. Can you further explain or point me to reference material. Thanks

Steve

 

[JohnDTampaBay]

I suppose the only error handling you may need is to make sure the UPDATE was successful. The first UPDATE statement will update all rows in the Hedge_CodePassword table, not just the ones that were updated; your second example is correct.

CREATE TRIGGER Hedge_CodePassword_DateUser_Change
ON [Hedge_CodePassword]
FOR update
AS UPDATE [Hedge_CodePassword] 
SET TimeStamp = getdate(),  CreateUserID = user_name() 
FROM [Hedge_CodePassword], inserted 
WHERE inserted.UserID = [Hedge_CodePassword].UserID

IF @@Error <> 0
  ROLLBACK TRANSACTION
ELSE
  COMMIT TRANSACTION

Assuming that all the tables have TimeStamp, CreateUserID, amd UserID columns, this code should write the code for the triggers on each table. Run this script, then copy and paste the results in a new query window and execute. Good luck!

SELECT 'CREATE TRIGGER [' + Table_Name + '_DateUser_Change]
ON [' + Table_Name + ']
FOR UPDATE AS
UPDATE [' + Table_Name + '] 
SET TimeStamp = getdate(),  CreateUserID = user_name() 
FROM [' + Table_Name + '] a, inserted 
WHERE inserted.UserID = a.UserID

IF @@Error <> 0
  ROLLBACK TRANSACTION
ELSE
  COMMIT TRANSACTION
GO'
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[JohnDTampaBay]

Direct recursion is prevented. Check out Using Nested Triggers in BOL.

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[rdroske]

Thanks AngelTampaBay, did not know that.

This is actually recursive not nested but I see what you mean.

As long as the "Recursive Trigger" Option is not checked (not by default) this Update trigger doing an Update on the same table it is defined on will not cause the trigger to fire again.

 

[SteveMe]

Angel the syntax looks correct. I even matched it up with generate scripts. But for some reason it doesn't appear to create the triggers. Would this syntax work with SQL 7? Also is their a possibility that the string is too long? Any help would be appreciated. Thanks

 

[rdroske]

What do you mean by won't create the trigger? When you go to that table and select manage triggers it is not listed in the dropdown or it is there but seems to not be firing?

Are you trying to add it by issuing the SQL from QA or through the "manage triggers" in Enterprise manager.

If through QA you should at least get an error message if it is not adding the trigger?

 

[JohnDTampaBay]

My script only writes the code to create the triggers. Run the script, then copy and paste the results in a new query window, and execute.

Hmm, I can't remember if the Information_Schema views were available in 7. If not, you may have to modify the line

FROM Information_Schema.Tables WHERE Table_Type = 'BASE TABLE'

to access SysObjects directly.

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[rdroske]

You do know about the "Manage triggers" under all tasks?

Can just paste the trigger definition in there.