|
Subject:
|
INSERT ... OUTPUT INTO in INSTEAD OF TRIGGER
|
|
Posted By:
|
kpkirilov
|
Post Date:
|
7/25/2008 1:03:39 AM
|
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.
|
|
Reply By:
|
Peso
|
Reply Date:
|
7/31/2008 4:21:18 PM
|
http://weblogs.sqlteam.com/peterl/archive/2007/10/03/New-OUTPUT-operator.aspx
|
|
Reply By:
|
kpkirilov
|
Reply Date:
|
8/5/2008 1:13:41 AM
|
quote:
Originally posted by Peso
http://weblogs.sqlteam.com/peterl/archive/2007/10/03/New-OUTPUT-operator.aspx
I know how to use OUTPUT INTO. Link you gave me no respect in the question.
I understand why this is obtained, a problem. OUTPUT INTO be taken as SCOPE_IDENTITY, that is, relates to the current request.
|
|
