SQL Server Trigger – Does It Fire Only When Transaction is Committed?

sql servertransactiontrigger

I'm a developer, but I turn to the dba community for the following question.

I'm writing an integration test in C#. In this test, I start a transaction first, and always rollback so nothing is committed to the database, regardless of wether the test succeeds or fails.

After starting the transaction, I insert something in a table. There is a trigger for insert on this table, but I get the impression this trigger is only triggered when I commit my transaction.

Is this true?

Is there a way for me to make the trigger fire inside the transaction?

So what happens at runtime is:

  • Insert in table A
    • Trigger is fired and inserts in table B

What happens in my test is:

  • Open transaction
  • Insert in table A
  • Check table B –> insert hasn't been executed

Best Answer

I don't know anything about Entity Framework, but in the following basic test using Sql Server Management Studio against a local instance, it appears that the trigger fires upon the completion of the insert to table dbo.Test1 and I can immediately see rows in dbo.Test2 BEFORE issuing the COMMIT

USE [Test]
GO
IF OBJECT_ID('dbo.Test1', 'U') IS NOT NULL 
    DROP TABLE dbo.Test1
GO
IF OBJECT_ID('dbo.Test2', 'U') IS NOT NULL 
    DROP TABLE dbo.Test2
GO
CREATE TABLE [dbo].[test1](
    [col1] [char](3) NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[test2](
    [col1] [char](3) NULL
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.Test1Trigger 
   ON  dbo.Test1
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Test2 select * from inserted

END
GO
begin transaction
insert into dbo.test1 (col1) values('abc')
select * from dbo.test2     --The row comes out of the select without issuing a commit
--insert into dbo.test2 (col1) values('xyz')
--commit
--select * from dbo.test2
--rollback
Related Question