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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply