September 27, 2007 at 4:34 am
I'm using MSSQL 2005 and the scenario is like this:
1. Insert a single row into a table
2. Retrieve my last inserted row (mine, I don't care if meanwhile someone inserted another row into the same table)
Sound's simple, but I have problems with getting values back from tables with INSERT triggers.
I tried using the OUTPUT clause of the INSERT command, and what I came up with use something like this:
CREATE TABLE [dbo].[X_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[NAME] [varchar](30) NULL,
[CDATE] [datetime] NOT NULL
)
CREATE TRIGGER [dbo].[X_TEST_INSERT]
ON [dbo].[X_TEST]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE X SET X.CDATE=GETDATE()
FROM X_TEST X (NOLOCK), INSERTED I
WHERE X.ID=I.ID
END
DECLARE @X TABLE(
ID int NULL
,NAME varchar(30) NULL
,CDATE datetime NULL)
INSERT INTO X_TEST WITH (ROWLOCK) OUTPUT INSERTED.* INTO @X select
'My Name'
,'2007-01-01'
SELECT * FROM @X
The script is generated automatically by the client application who gets the table name and the values to be inserted.
At first, everything seemed to be OK, I got one line back, EXCEPT that the value for CDATE was '2007-01-01' (my inserted value), not the GETDATE() value at the time of INSERT.
So, my question is: how do I get the correct value for the columns affected by a trigger?
P.S. The IDENTITY column above is only for example. I already considered the scenario with each table having and IDENTITY column and getting back the row with IDENTITY_COLUMN = SCOPE_IDENTITY(). What I'm trying to do is to add a feature to an application (the users inserts a record and gets back the real values, without refreshing the data) and adding a column to each table would be a huge amount of work.
September 27, 2007 at 4:52 am
HI there,
It seems that you already have an Identity Column on your table?
What don't you use that and then retrieve the dat from there?
Also does this have to be done in a trigger?
Why don't you do it in your Insert Procedure after you run the insert statement?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 27, 2007 at 4:54 am
As I said in P.S., not all tables have an IDENTITY column.
And I don't have an Insert Procedure.
September 27, 2007 at 5:41 am
Unfortunately, the OUTPUT clause will always return the data from your action, not the trigger. If you have a primary key or something that uniquely identifies your records (which it is always good practice to have), and your trigger does not update these fields, simply join your OUTPUT data to the table to return the results.
CREATE TABLE [dbo].[X_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[NAME] [varchar](30) NULL,
[CDATE] [datetime] NOT NULL
)
GO
CREATE TRIGGER [dbo].[X_TEST_INSERT]
ON [dbo].[X_TEST]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE X SET X.CDATE=GETDATE()
FROM X_TEST X (NOLOCK), INSERTED I
WHERE X.ID=I.ID
END
GO
DECLARE @X TABLE(
ID int NULL
,NAME varchar(30) NULL
,CDATE datetime NULL)
INSERT INTO X_TEST WITH (ROWLOCK) OUTPUT INSERTED.* INTO @X select
'My Name'
,'2007-01-01'
SELECT T.* FROM @X X
INNER JOIN X_TEST T ON X.[ID] = T.[ID]
September 27, 2007 at 5:56 am
Adding an Identity column to each table isn't that much work if you write a little dynamic SQL to do it for you... especially if you have it all worked out as to how to use it to do the things you say.
And, if there's ever a chance that you'll insert more than one row into the table in a given Insert (like, during a batch process), writting a trigger to handle a single row will become "Death by SQL" sometime in the near future. 😉
--Jeff Moden
September 28, 2007 at 6:31 am
Not sure, but how about using the 'insert' table in SQL? I'm sure of one thing, I'll be corrected if I'm wrong.
Tim
What do you call a Local Area Network in Australia? A LAN down under.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply