Sql-server – Instead of insert trigger VS For insert Triggers in SQL server

sql servertrigger

Edited: My question is since there is an insert statement in the instead off insert trigger, then it should call insert trigger from inside the instead off insert trigger and which should avoid inserting row in the testtable. What my observation is that for insert trigger is firing but after instead off trigger which already has inserted the new row in the testable. For insert trigger is raising error but instead off trigger has already inserted a row in testtable. Why for insert trigger is not called from inside the instead off insert trigger?

I have two triggers on the table called TestTable. One is Instead of insert trigger and second is After insert trigger. First instead of insert trigger will fire and then for insert trigger.What it does that it inserts new row in TestTable and CopyTable, but not in AuditTable.

My question is why insert trigger is not fired from instead of trigger? It should because it is also using insert query in it. Why TestTable get the new inserted row?

My table script

CREATE TABLE [dbo].[TestTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Age] [int] NULL,
    [DOB] [date] NULL,
    [Address] [nvarchar](50) NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED

My SQL Query:

Insert into dbo.TestTable values ('Waleed',24,'10-2-2015','Lahore');

My instead of insert trigger is:

ALTER TRIGGER [dbo].[utInsteadoff]
   ON  [dbo].[TestTable] 
   Instead of Insert
AS 
    Declare @Name as nvarchar(50)
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Insert into dbo.TestTable (Name,Age,DOB,Address) Select Name,Age,DOB,Address From inserted; 
    Insert into dbo.CopyTable (Id,Name,Age,DOB,Address) Select Id,Name,Age,DOB,Address From inserted;  

END

After insert Trigger is:

ALTER TRIGGER [dbo].[insertTrigger] 
   ON  [dbo].[TestTable] 
   AFTER Insert
AS 
 Declare @Name as nvarchar(50)
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
     Set @Name = (Select Name from inserted)


 If (@Name = 'Waleed')
    Raiserror('You Cannot Enter Waleed Name',10,1)
  Else
 Begin 

    Insert into dbo.AuditTable (ID,Name,Age,DOB,Location)
    Select ID,Name,Age,DOB,Address from inserted
    -- Insert statements for trigger here
end
END

Best Answer

My question is why insert trigger is not fired from instead of trigger? It should because it is also using insert query in it. Why TestTable get the new inserted row?

The INSTEAD OF trigger replaces the original insert. So the code:

Insert into dbo.TestTable (Name,Age,DOB,Address) 
Select Name,Age,DOB,Address From inserted;

...is executed instead of the original insert:

Insert into dbo.TestTable values ('Waleed',24,'10-2-2015','Lahore');

The actions in the instead of trigger are completed before constraints are checked, and before any AFTER triggers fire.

When the instead of trigger completes, a row has been added to the TestTable and CopyTable tables. Note though that the transaction is still active at this point, so the changes are not yet permanent.

Next, the after trigger fires. It sees the name 'Waleed' in the inserted table, prints a message, but does not rollback the changes made so far in the transaction (including the rows added by the instead of trigger). No row is added to the AuditTable.

If the condition that checked for the name 'Waleed' also performed a rollback, everything would be rolled back, and no rows would be added to any table.

Related Question