Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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