none
is it good to refrence the table in sp if the sp is called from the trigger of that table it self. RRS feed

  • Question

  • hi,

    i have three tables "a", "b", "c ".

    i want to make one trigger on table "a" and one on table "b", the trigger inturn calls a stored proc(SP) to perform insertion deletion task on table "c".

    when some body inserts or updates  table "a" , the trigger on table "a"  inserts or updates table "c" by calling the  stored proc(SP) ,

    and there after updates table "b" from trigger it self (this stmt is below the call of (SP) .

    ---------

    Q1) i am using table "a" in (SP), i wanted to know that, is it correct to use the table "a" in stored proc(SP) to get the new inserted record or updated record to insert or update table "c" as the control is till in tigger of table "a"?

    ----------

    well when table "b" gets updated from the trigger of table  "a" , then the trigger of table "b" gets activated and it inserts or update the table "c" by calling (SP).

    Q2) at this place when trigger of table"b" calls the same (SP) to insert table "c", it uses table "a" and "b" to insert update tabel "c", at this point also the control is still in trigger, so i whould like to know is it correct (technically).

    i know that  by changing few things in tables  i can make things easy

     i read it in msdn

    "AFTER specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires."

    but i would to know that, what exactally happens in above case , how and when  event are fired and gets affected. and finally is it correct?

    yours sincerelly.


    Thursday, June 6, 2013 5:40 AM

Answers

All replies

  • Why are you creating a separate Stored Proc to fire from the trigger?

    You can have the SP logic in the Trigger itself. If you use an SP, then you would not get the INSERTED & DELETED Trigger/Magic tables that you can get in the Trigger.

    Also you can avoid Triggers and create single SP to add records in Table a & b and update the table c in single transaction.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Thursday, June 6, 2013 5:51 AM
  • You can take the newly inserted record in SP by other relevant criteria, like a row update date_time column.
    There will be cascade action/constraint check before trigger is fired.

    Many Thanks & Best Regards, Hua Min


    • Edited by Ai Guo Thursday, June 6, 2013 6:29 AM
    Thursday, June 6, 2013 6:28 AM
  • How about using MERGE Statement to synchronize the changes 

    Thanks and regards, Rishabh K

    Thursday, June 6, 2013 7:15 AM