How to execute a query just before insert in MSSQL Server-CodePudding

I need to execute a query before every insert into the table. I try to use instead of

--insert into tbl_Exlog(ActionName) values('Insert')
--select * from tbl_Exlog

ALTER Trigger [dbo].[trgExLogTest] on [dbo].[tbl_ExLog] 
Instead of Insert
as

begin
    insert into tbl_ExLog (ActionName) values('trigger')
end


 but it restricts the actual insert which I don't want.

Output : ActionName trigger

Insert is missing

CodePudding user response：

The INSERT is missing, yes, in your TRIGGER. You never told SQL Server to INSERT the data, so it doesn't INSERT it. An INSTEAD OF INSERT "does exactly what it says on the tin"; 'do this instead of the INSERT'. You tell SQL Server to INSERT a row into tbl_ExLog instead but don't tell it to INSERT into the table you were actually inserting again.

Define the INSERT in your trigger:

ALTER TRIGGER [dbo].[trgExLogTest] ON [dbo].[tbl_ExLog] 
INSTEAD OF INSERT AS
BEGIN
    INSERT INTO dbo.tbl_ExLog (ActionName) VALUES ('trigger');

    INSERT INTO dbo.trgExLogTest ({Column List})
    SELECT {Column List}
    FROM inserted;
END;