5

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
2
  • 3
    Which RDBMS are you using? Are you checking the insert into table B via the same database connection, or a different one?
    – Philᵀᴹ
    Nov 10, 2016 at 11:05
  • I'm using MS SQL and using Entity Framework. The check is using the same database connection, because of the way my test is built up, but I could use a new one if necessary.
    – Peter
    Nov 10, 2016 at 12:30

3 Answers 3

8

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
3
  • Hm, ok I will look into this. Thanks. I'll keep you posted.
    – Peter
    Nov 10, 2016 at 15:53
  • @Peter and Scott: This is correct in that the Trigger is based on the event (i.e. the DML statement) and has nothing to do with the Transaction. Nov 10, 2016 at 20:09
  • I had same question and was concerned this test just represented a race where the simple insert is observed to win. What made it definitive for me was: if code in trigger causes error, the insert statement fails.
    – ttugates
    Jun 10, 2020 at 14:05
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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.