June 13, 2011 at 6:36 pm
Do you always have to use the INSERTED and DELETED tables when creating a trigger? As a temporary solution to fix an application issue, I have been asked to create a trigger on INSERT or UPDATE. The trigger needs to update a column with the value from another column in the same table. Here is some sample code of what I have done.
-- Create Sample Table
create table dbo.MyTable(
Col1 int not null,
Col2 datetime not null,
Col3 int null default(0),
Col4 int null default(0),
Col5 int null default(0),
constraint PK_MyTable primary key (Col1,Col2)
)
go
-- Insert Test Data
insert into dbo.MyTable values (2,'20030902',1867,0,1);
insert into dbo.MyTable values (2,'20031002',2457,0,1);
insert into dbo.MyTable values (2,'20031102',3140,0,1);
insert into dbo.MyTable values (2,'20031202',2539,0,1);
insert into dbo.MyTable values (2,'20040102',1792,0,1);
insert into dbo.MyTable values (2,'20040202',513,0,1);
insert into dbo.MyTable values (2,'20040302',1685,0,2);
insert into dbo.MyTable values (2,'20040402',1697,0,2);
insert into dbo.MyTable values (2,'20040502',1530,0,2);
insert into dbo.MyTable values (2,'20040602',910,0,2);
insert into dbo.MyTable values (2,'20040702',1520,0,2);
go
-- Create Trigger
create trigger TR_MyTable_I_U
on dbo.MyTable
for INSERT, UPDATE
as
begin
set nocount on;
update dbo.MyTable
set Col3 = Col4
where Col5 = 1
end
go
-- Statements to Test Trigger
-- Should update Col3
update dbo.MyTable set Col4 = 777 where Col2 = '20030902';
go
-- Should not update Col3
update dbo.MyTable set Col4 = 777 where Col2 = '20040302';
go
-- Should update Col3
insert into dbo.MyTable values (2,'20000102', 0, 888,1);
-- Should not update Col3
insert into dbo.MyTable values (2,'20040802', 0, 888,2);
go
select * from dbo.MyTable
go
It seems to work just fine without having to refer to the INSERTED or DELETED tables in my trigger, but I'm afraid I might be missing something.
I'm using SQL Server 2008 R2.
THX
June 14, 2011 at 12:01 am
You don't have to use them, but in your case you could be updating far more of the table than you need. Say someone comes and does a one row insert, the table is 200 000 rows and half the table has a Col5 = 1 (but all except that one new row already has Col3 = Col4)
Your trigger, instead of updating just the one newly inserted row, updates every single row in the table where Col5 = 1, so it updates 100 000 rows instead of 1.
Wrong? No. Inefficient? Hell, yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2011 at 3:56 am
you can create trigger like this for your purpose
create trigger TR_MyTable_I_U
on dbo.MyTable
for INSERT, UPDATE
as
begin
set nocount on;
update dbo.MyTable
set Col3 = M.Col4
FROM dbo.MyTable M
join inserted I on I.col1= M.Col1 and I.Col2= M.Col2
where M.Col5 = 1
end
June 14, 2011 at 5:59 am
Wow, Gail Shaw on the first response. I feel honored.
I think I must have worked too late last night. When I first wrote the trigger I fully expected it to run that statement for every row in the table for each insert/update where Col5 = 1. After testing it with fresh eyes this morning, that's exactly what it does. I could have sworn it was only doing it for the row that was just updated or deleted last night.
Look for me on the next SQL Cruise. I'll be the guy with the red face.
Thx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply