SQL with Manoj

–> Question:

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 this 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?
 

–> My Answer:

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 a single transaction.
 

Ref Link.