SET IDENTITY_INSERT inside INSTEAD OF INSERT trigger not working 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)&lt;&gt; 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?
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')