Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 25th, 2008, 01:03 AM
Registered User
 
Join Date: Jun 2008
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.

Reply With Quote
  #2 (permalink)  
Old July 31st, 2008, 04:21 PM
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

http://weblogs.sqlteam.com/peterl/ar...-operator.aspx


Reply With Quote
  #3 (permalink)  
Old August 5th, 2008, 01:13 AM
Registered User
 
Join Date: Jun 2008
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Peso
 http://weblogs.sqlteam.com/peterl/ar...-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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Trigger debbiecoates SQL Server 2000 3 December 24th, 2007 08:00 AM
trigger to insert current date on insert kev_79 SQL Server 2000 3 January 23rd, 2006 05:58 PM
insert,update trigger rolle SQL Server 2000 3 September 29th, 2005 08:41 PM
Insert Trigger Arsi SQL Server 2000 2 February 25th, 2005 05:41 PM
Help With INSERT + UPDATE Trigger HenryE SQL Server 2000 1 December 11th, 2003 06:26 PM



All times are GMT -4. The time now is 01:27 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.