INSERT ... OUTPUT INTO in INSTEAD OF TRIGGER
Hello.
I have the following problem with the use of INSERT ... OUTPUT INTO in INSTEAD OF TRIGGER.
Example:
CREATE TABLE T1(
T1_ID INT IDENTITY(1, 1),
Name VARCHAR(100),
CONSTRAINT PK_T1_ID PRIMARY KEY (T1_ID)
);
GO
CREATE TRIGGER TRG_INSD_INS_T1
ON T1
INSTEAD OF INSERT
AS
INSERT INTO T1(Name)
SELECT Name FROM Inserted;
GO
CREATE TABLE T2(
T2_ID INT,
CONSTRAINT PK_T2_ID PRIMARY KEY (T2_ID),
CONSTRAINT FK_T2_ID FOREIGN KEY (T2_ID) REFERENCES T1(T1_ID)
);
GO
CREATE VIEW VW_T(T1_ID, Name, T2_ID)
AS
SELECT T1.T1_ID, T1.Name, T2.T2_ID FROM T2
LEFT JOIN T1 ON T1.T1_ID = T2.T2_ID;
GO
CREATE TRIGGER TRG_INSD_INS_VW_T
ON VW_T
INSTEAD OF INSERT
AS
IF @@ROWCOUNT = 0
RETURN;
SET NOCOUNT ON;
DECLARE @tb TABLE(Id INT);
INSERT INTO T1(Name)
OUTPUT INSERTED.T1_ID INTO @tb
SELECT Name FROM Inserted;
SELECT @@ROWCOUNT;
SELECT * FROM @tb;
--slq code;
GO
Having run sql clause: INSERT INTO VW_T(Name, T2_ID) VALUES ('Test', 1)
The result is 0 instead of 1 (SELECT * FROM @tb in trigger of table T2 return one record).
When removed trigger TRG_INSD_INS_T1 from table T1 everything is working normally.
That is, in the performance of INSERT INTO VW_T(Name, T2_ID) VALUES ('Test', 1) returns the value of IDENTITY column T1_ID.
What is due this? How can otherwise choose their problem: get the value of identity column in INSERT.
|
