Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Calling a Stored Procedure From a Trigger

Is it possible to call a stored procedure from a trigger in TQL? If so, what are the implications of doing this while using transactions?

If a transaction is rolled back in a stored procedure is there any way of detecting this in the trigger?

Thanks.
0
AMLabels
Asked:
AMLabels
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is possible, the transaction will englobe the stored procedure.
if the procedure rolls back the transaction, the tigger will also rollback everything.


0
 
Anthony PerkinsCommented:
You may also have to call the Stored Procedure in a CURSOR, one time for each row added and / or deleted.
0
 
Einstine98Commented:
The way to call a stored procedure is

EXEC storedprocedurename

acperkins is right, however, I would personally try as much as possible avoid using a stored procedure call in a trigger... many complications (coding wise) including the use of a cursor or having to stage the INSERTED table...
0
 
AMLabelsAuthor Commented:
What do you mean by 'stage' the INSERTED table?

I'm still wondering about the use of transactions within the trigger and the subsequently called sp.

If this is the TRIGGER:

BEGIN TRIGGER

    BEGIN TRANSACTION

        CALL STORED PROCEDURE
        ADDITIONAL TRANSACTION STATEMENTS ...

     COMMIT TRANSACTION

END TRIGGER

How would it be possible to rollback this transaction in the stored procedure? Is the only way to do this by having the return value of the sp indicate it's success and then rollback inside the trigger?

Or could the sp just call the ROLLBACK statement without it's own BEGIN and END TRANSACTION block..? Is this what was meant by englobing?

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the stored procedure uses ROLLBACK, then in the trigger check @@TRANCOUNT  
if it is 0, the transaction got rolled back in the procedure
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now