March 22, 2011 at 10:49 am
how to retrieve primary key's value of an inserted row inside a trigger?
March 22, 2011 at 10:50 am
Use the "inserted" table in the trigger.
I'd have to actually see what you have for your trigger to suggest anything more concrete than that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 22, 2011 at 10:57 am
Run the following code.
create table demo(id int primarykey,number nvarchar(max))
go
create trigger dbodemo
on dbo.demo
for insert
as begin
declare @pk Nvarchar(max)
declare @pkey nvarchar(max)
declare @str nvarchar(max)
select @pk = column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'demo'
select @pkey = @pk from inserted
print @pkey
end
now do following:-
insert into dbo.demo values(1,'1234')
Output will be:- id
but i want following output:- 1
how can I do that?
March 22, 2011 at 12:44 pm
prikshna67 (3/22/2011)
Run the following code.create table demo(id int primarykey,number nvarchar(max))
go
create trigger dbodemo
on dbo.demo
for insert
as begin
declare @pk Nvarchar(max)
declare @pkey nvarchar(max)
declare @str nvarchar(max)
select @pk = column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'demo'
select @pkey = @pk from inserted
print @pkey
end
now do following:-
insert into dbo.demo values(1,'1234')
Output will be:- id
but i want following output:- 1
how can I do that?
Looks like there a quite a few thing wrong with this. First, the create table is not going to work as coded. Secondly, and probably more important, is if you are trying to get the value from the column this view is not going to get you anywhere near close to what you are looking for. It tells you things about the column, not about the contents.
This trigger also will not work for inserts that insert more than 1 value at a time.
Take a look this code and see if you can get what you need from here. I can't really figure out why you would to create a trigger that does nothing more than a print statement.
CREATE TABLE [dbo].[demo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[textField] [varchar](25) NULL,
CONSTRAINT [PK_demo] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
create trigger tr_demo on dbo.demo
for insert
as begin
select * from inserted
end
go
insert into dbo.demo values('1234')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2011 at 1:08 pm
Here's where your trigger is breaking:
select @pk = column_name
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_NAME = 'demo'
select @pkey = @pk from inserted
What that does is assign the column name to the @pk variable (as you expect), and then assign that again to the @pkey variable. "select @pk from inserted" doesn't select the value of the column, it selects the value of the variable.
Try this, you'll see what I mean:
create table #T (ID int primary key);
insert into #T (ID)
values 1;
declare @ID sql_variant;
select @ID = 'Bob';
select @ID from #T;
There isn't a column named "Bob", so "select Bob from #T" would give an error about a missing column, but what it's doing instead is selecting the variable.
Try this to see why it works that way:
create table #T (ID int primary key);
insert into #T (ID)
values 1;
declare @ID sql_variant;
select @ID = 'Bob';
select @ID, ID from #T;
You'll see that you can select the variable value, and actual columns. That's what that kind of statement is meant to do.
To do what you're trying, you'd need to do something like this:
CREATE TABLE dbo.DropMe
(ID INT IDENTITY
PRIMARY KEY,
Col1 CHAR(1)) ;
go
CREATE TRIGGER DropMeTrigger ON dbo.DropMe
AFTER INSERT
AS
SET nocount ON ;
DECLARE @Cmd VARCHAR(1000) ;
SELECT @Cmd = 'select [' + column_name + '] from inserted;'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'DropMe' ;
EXEC (@Cmd) ;
GO
INSERT INTO dbo.DropMe (Col1)
VALUES ('A');
GO
DROP TABLE dbo.DropMe ;
But even that won't work, because you can't use the "inserted" table in dynamic SQL called by a trigger, you can only use it directly in the trigger itself (or in an Output clause in SQL 2005 and later, but that's a different matter entirely).
It's kind of assumed that, if you're building a trigger on a table, you'll know what the columns are, because otherwise you really can't build a trigger that makes sense.
What is it you're trying to do, that requires dynamically finding out what the PK column is during the runtime of the trigger? What problem are you trying to solve?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply