Abt stored procs
-
Hi all,
Just two questions to answer.
1)Can i have a trigger inside a stored procedure.if "NO" then why.
2)I have a trigger2 which fires when trigger1(fired after insert operation on first table) inserts a record inside the second table.the trigger2 further fires trigger3 which contains a stored procedure which unfortunately encounters a erroneous transaction and rollbacks the transaction.For the entire operation to rollback ie including the triggers how r u goin to avoid the first two trigger operations .
Plz reply,
Sandeep
-
Hi Sundeep,
Pleae find the ans for your Queries
1)Can i have a trigger inside a stored procedure.if "NO" then why.
If you have insert statment into a table ( on which trigger is written) inside the SP, trigger fires automatically .
2) By default all are in single transactions and all are rollbacked if it failed in any of the trigger.
Regards,
Ramesh K
-
1) A trigger is code that is executed when an action occurs on the table that the trigger is created on. How could you 'have a trigger inside a stored procedure'? The question does not make sense.
2) Not sure exactly what you mean. But if the stored procedure rolls back the transaction then no value will be inserted in any of the tables and the work of all triggers will be rolled back automatically.
-
On item 2,
Transaction is there to maintain atomicity. That means either 100% of the code is executed without error or none.
If you have only one direct insertion to table and the rest is through triggers, and the error occurs as a result of multiple level of triggers, The best way is to stop Nested triggers. Create single triggers which does all inserts.
Hope this helps!
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply