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?

  • 1
    Unrelated, but: dba.stackexchange.com/questions/154251/… – a_horse_with_no_name Jul 18 '18 at 14:49
  • I can think of 2 things that might be different from the original insert: the order by is lost (if any) and there's the infamous * that might break the insert if you have an identity column which can't be inserted. Both things are negative so I can't think of a good reason to use this. – EzLo Jul 18 '18 at 14:51
  • @Ezlo - good points, although inserts actually ignore the ORDER BY clause, if it's included in the INSERT statement. – Max Vernon Jul 18 '18 at 14:52
  • 1
    @MaxVernon good thing to point out, didn't know that rows weren't ordered if there is no identity column or TOP (partially), and it makes sense to ignore it. – EzLo Jul 18 '18 at 15:01
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.