CODEWITHSUNDEEP
mysqlsql-serverstored-procedurestriggers
Vicky Thakor
Vicky Thakor

Reputation: 3916

Trigger called from Stored Procedure(ROLLBACK for Trigger will happen?)

Consider a scenario. I've table T1 that has trigger created on INSERT operation. And Stored Procedure as follow.

//StoredProcedure.sql

BEGIN TRANSACTION;
...
...
INSERT INTO TABLE T1
/* Trigger will be executed on record insertion in Table T1 */
.....
.....
.....
.....
Consider some error here...
.....
.....
COMMIT TRANSACTION;
.....
.....
ROLLBACK TRANSACTION;

On Error in Stored Procedure will call ROLLBACK TRANSACTION, Will it ROLLBACK operation performed in Trigger?

MSSQL and MySQL: Considering Ideal case, It'll ROLLBACK operation performed by Trigger but I want your views on the question.

Upvotes: 0

Views: 678

Answers (2)

Samay
Samay

Reputation: 503

Yes, it will surely rollback the operation performed by Trigger.

I just tested it. :)

create table test (id int, testval varchar(20))
create table dummytest (dummyid int, dummytestval varchar(20))

CREATE TRIGGER trg_ins_test
ON test
FOR INSERT
AS
  BEGIN
      INSERT INTO dummytest
                  (dummyid,
                   dummytestval)
      SELECT i.id,
             i.testval
      FROM   inserted i
             LEFT JOIN test t
                    ON i.id = t.id
                       AND i.testval = t.testval
  END 

CREATE PROCEDURE usp_test
AS
  BEGIN
      BEGIN try
          BEGIN TRANSACTION

          INSERT INTO test
          VALUES     (1,
                      'a')

          SELECT 1 / 0

          COMMIT
      END try

      BEGIN catch
          IF @@TRANCOUNT > 0
            ROLLBACK
      END catch
  END

You can use above code to test at your end.

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82524

Why don't you simply test? It took me less then 10 minutes to test on sql server, including creating the tables, trigger and stored procedure.

For sql server, the answer is that the rollback will also rollback the trigger action. for MySql I'll leave the testing to you.

Upvotes: 1

Related Questions