7

I stumble on this trigger when trying to analyze some DB:

Create trigger [tbl_Details_Trigger] on [tbl_Details]
    Instead of Insert
    As
      Insert into [tbl_Details]
      Select * from inserted

To me it looks like this trigger doesn't do anything special, and if it will be dropped the outcome will be the same. Am I right? Am I missing something?

0

1 Answer 1

3

You are correct; since this trigger is an INSTEAD OF INSERT trigger that simply does an insert of inserted data into the table the trigger is defined on, the only outcome will be to insert the data as if the trigger did not exist.

As with any and all changes, before you delete this trigger in production, ensure you test the change in your development/test environment.

Interestingly, if the table includes an IDENTITY column, you won't be able to create the trigger. Consider this repro, which runs on SQL Server 2016+:

USE tempdb;
DROP TABLE IF EXISTS dbo.insert_test;
CREATE TABLE dbo.insert_test
(
    i int NOT NULL
        CONSTRAINT PK_insert_test
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , d varchar(100) NOT NULL
);
GO
CREATE TRIGGER insert_test_trigger ON dbo.insert_test
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.insert_test (i, d)
    SELECT i, d
    FROM inserted;
END
GO

INSERT INTO dbo.insert_test (d)
VALUES ('test');

SELECT *
FROM dbo.insert_test;

Results:

Msg 544, Level 16, State 1, Procedure insert_test_trigger, Line 8 [Batch Start Line 24]
Cannot insert explicit value for identity column in table 'insert_test' when IDENTITY_INSERT is set to OFF.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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