Single Trigger on multiple tables

  • 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

  • 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)

  • 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'

  • Thanx..

    Can you please send me the syntax on how to call a stored procedure from a trigger.

    -Bheemsen

  • 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

  • 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)

  • Isn't it better to use functions instead of stored proc. in this case?

  • 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