SQL Server Performance

Working with triggers x Procedures

Discussion in 'General DBA Questions' started by sunda, Jan 23, 2009.

  1. sunda New Member

    Hello all!
    Once again i am very grateful to be part of this comunity.
    So, i have a question. I know how to call a procedure within a triggers, but how do i do this with a parameter?
    For exemple:
    In 'insert' trigger one of the fields was 'custumer_id' with value 'x-a'.
    How do i call the procedure Kill_M_F_Custumer with this especific field of the table, with is been insert?
    Thanks all for the help!
    Happy new Year!!!
  2. Elisabeth Redei New Member

    Hi,
    I am not 100% sure I get the question but if it is what I think... this will do it:create
    trigger trCallProc on tblInsertTrafter INSERT
    AS
    declare @p1 int
    select
    @p1 = col1 from insertedexec
    uspCallFromTrigger @p1
    If you look in BoL under "CREATE TRIGGER" you will see some examples of how to do errorhandling as well.
    HTH/Elisabeth
    go
  3. Adriaan New Member

    As always, remember that when multiple rows are inserted into the table, the trigger fires just once, and the inserted snapshot contains all the inserted rows.
    The posted code will handle just one of those rows, whichever happens to be the last row in the snapshot.
    To cover multi-row inserts, you'll have to use a cursor, loop through it, and call the sproc for each row in inserted.
    ... better still, check out what the sproc is doing with those values, and see if you can do the same with set-based processing inside the trigger.
  4. sunda New Member

    Thanks a lot!

Share This Page