September 6, 2002 at 1:35 pm
Hi Guys,
Does any know whether it is possible to create one trigger on multiple tables. What I mean to say is :
Assume ther are 3 tables X, Y, Z.
I want to insert a row into the table A for any UPDATE, INSERT or DELETE action on any of the X, Y, Z tables.
I want to accomplish the above using one trigger only.
Please help me or point me to any references.
thanx..
-Bheemsen
September 6, 2002 at 6:28 pm
Sorry you do have to put a trigger on all three.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 6, 2002 at 11:06 pm
What you could do to avoid redundancy and improve maintainability is putting your code in a stored proc and calling that SP from your 3 triggers. That way if you ever want to change the common code, you will only have to do it once.
Hope this helps.
Oliv'
September 9, 2002 at 12:20 pm
Thanx..
Can you please send me the syntax on how to call a stored procedure from a trigger.
-Bheemsen
September 9, 2002 at 12:48 pm
OK, you just use the EXEC statement like that:
EXEC pMyStoredProc
If you want to use parameters or have a little error handling, your call would look more like something like that:
DECLARE @intReturn INT, @intSum OUTPUT
EXEC @intReturn = pMyStoredProc @strLetter = 'A', @intNumber = 12, @intSum OUTPUT
IF @intReturn <> 0
BEGIN
RAISERROR ('An error occured in MyTrigger', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
September 10, 2002 at 4:05 am
Also if you are INSERTING, UPDATING, or DELETING multiple records at a time you will have to look thru the records in either the inserted and/or the deleted tables either with cursor or while loop to pass and handle each record with the Proc.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 28, 2002 at 11:39 am
Isn't it better to use functions instead of stored proc. in this case?
September 28, 2002 at 1:29 pm
No, because the original goal was to insert a row in a table. Functions cannot have side-effects and that includes inserting a row in a table.
quote:
Isn't it better to use functions instead of stored proc. in this case?
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy