INSERT ... OUTPUT INTO in INSTEAD OF TRIGGER

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INSERT ... OUTPUT INTO in INSTEAD OF TRIGGER

Author

Topic

kpkirilov
Starting Member

10 Posts

Posted - 2008-07-29 : 02:04:03

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 03:44:41

didnt understand the purpose of instead of trigger on T1. you're simply inserted value onto table from inserted then why use it?

kpkirilov
Starting Member

10 Posts

Posted - 2008-07-29 : 04:28:11

Hello visakh16.

This is an example that I created to show the problem.
In real trigger on my table T1 is unnecessary because it generated unique number for each document, based on a mask set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 10:34:58

quote:
Originally posted by kpkirilov

Hello visakh16.

This is an example that I created to show the problem.
In real trigger on my table T1 is unnecessary because it generated unique number for each document, based on a mask set.

unnecessary or necessary?

kpkirilov
Starting Member

10 Posts

Posted - 2008-07-29 : 10:38:37

quote:
Originally posted by visakh16

quote:
Originally posted by kpkirilov

Hello visakh16.

This is an example that I created to show the problem.
In real trigger on my table T1 is unnecessary because it generated unique number for each document, based on a mask set.

unnecessary or necessary?

Trigger(TRG_INSD_INS_T1) I need.

kpkirilov
Starting Member

10 Posts

Posted - 2008-07-30 : 02:58:20

Hi visakh16

Do you have received an explanation why so?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources