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