SET IDENTITY_INSERT inside INSTEAD OF INSERT trigger not working

Question

I'm trying to perform a indentity insert inside a instead of insert trigger, but when I try to insert data into the data, it throws and error msg "Msg 544, Level 16, State 1, Line 36 Cannot insert explicit value for identity column in table 'PixTest' when IDENTITY_INSERT is set to OFF.".

I'm using SQL SERVER 2014 Developer Edition 64 Bit installed on Windows 8 64 bit.

Here is some sample code that might help:

CREATE TABLE PixTest
(
      ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
      TESTE VARCHAR(10)
)
GO

CREATE TRIGGER AutoIncrement_Trigger ON PixTest
instead OF INSERT AS
BEGIN

    DECLARE @ID INT;
    SELECT @ID=ID FROM inserted;



    IF COALESCE(@ID,0)<> 0
        BEGIN

            SET IDENTITY_INSERT PixTest ON
            INSERT INTO PixTest(ID, TESTE) 
            SELECT ID,TESTE FROM inserted
            SET IDENTITY_INSERT PixTest off

        END

    ELSE

        INSERT INTO PixTest(Teste) 
        SELECT TESTE FROM inserted;

END;
go

INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1')
GO

I googled about using SET IDENTITY_INSERT inside of a trigger, and couldn't find anything saying it is not possible, but the code above doesnt seem to work.

Any ideas?

Help me understand why you would want to do this and I might be able to give you a better way. What you are doing here will not work. The reason you are getting errors is because of the last insert and your error is telling you exactly what is going on - you are explicitly trying to insert a value of 1 into an identity field. Just because you set the IDENTITY_INSERT off in the trigger doesn't mean it will happen outside of the trigger. Assuming you got the last insert to work, you'd end up with a double insert which still wouldn't work because of the PK constraint. — Steve Mangiameli, Oct 28 '14 at 13:11

score 2 · Answer 1 · answered Oct 28 '14 at 13:30

2

The error isn't coming from the trigger. It's coming from your insert statement.

INSERT INTO PixTest(ID, TESTE) VALUES (1, 'TESTE 1')

It's getting parsed and returning an error before you ever get to the trigger. Change it to this and it works.

INSERT INTO PixTest(TESTE) VALUES ('TESTE 1')

answered Oct 28 '14 at 13:30

Kenneth Fisher

24,127
12
61
114

Kenneth, I guess you misunderstood the problem. I'm using the trigger to detect when an insert is being fired and a explicit value is informed to the id column, wich has the identity property set. When no vaue is informed to it, the identity should cover it. – Rodrigo Ribeiro Oct 28 '14 at 14:17
3

You can tell if a value is being inserted into a table using the UPDATE(column) function. However it still isn't going to work. If you try to insert into an identity column it's going to throw an error before you ever get to the trigger. You can use a TRY CATCH to check for it but again not inside the trigger. – Kenneth Fisher Oct 28 '14 at 14:31
Kenneth, I just got what you are saying. I'm affraid what I'm trying to do is unfeasable. Thanks anyway! – Rodrigo Ribeiro Oct 28 '14 at 14:53

score -1 · Answer 2 · edited Oct 28 '14 at 17:08

-1

Executed this script :

DBCC CHECKIDENT (PixTest, NORESEED)

Result says Identity value is NULL.

Checking identity information: current identity value 'NULL', current column value 'NULL'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Then we believe you need to follow either of the other two ways stated by others above.

edited Oct 28 '14 at 17:08

Aasim Abdullah

2,915
4
23
40

answered Oct 28 '14 at 15:15

kunal gurnani S

23
1
1
5

SET IDENTITY_INSERT inside INSTEAD OF INSERT trigger not working

2 Answers2