Does a trigger in a transaction only fire when the transaction is committed?

Question

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

Answer 1

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

Answer 2

There are different types of triggers. Some would fire even before the INSERT

But on any case, the INSERT and the related triggers would be part of the same transaction, so you would only see the database status before all transaction is run, or after all of it is run.

However, as your test is using the Entity Framework, you are probably examining the object in memory, before it is sent to the database. So you can query the object (to be inserted) while you haven't called context.SaveChanges() to persist the data, which would be the time you get the INSERT to the database (and the trigger executed).

To test this, execute queries from SQL Server Management Studio. You should find either there is data in both tables, or in neither.

Answer 3

Triggers fire immediately upon the event(s) that they are created for. Whether or not the operation is wrapped in an explicit Transaction does not affect this.

Whether or not you can see anything in Table B all depends on when Check table B --> insert hasn't been executed happens. If the "Check" happens either after the ROLLBACK, or after a Connection.Close and Connection.Open -- even if using Connection Pooling and getting the same ConnectionID -- as that causes a ROLLBACK of the uncommitted Transaction, then the "effect(s)" of the Trigger will not be seen as they were rolled-back.