Trigger on INSERT that UPDATES record just inserted

Trigger on INSERT that UPDATES record just inserted

Post by bigbrorp » Tue, 28 Nov 2006 22:57:17


Hi -

I'm using SQL 2K5 and am trying to create a trigger that updates a
field of a record that was just inserted (based on a lookup in another
table). I'm having trouble getting the identity value of the record I
just inserted (the identity field is a primary key that
auto-increments).

I have the following:

ALTER TRIGGER [Ticket] ON [dbo].[Ticket]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
UPDATE Ticket SET [IPAddress]='1.2.3.4' WHERE
[TicketID]=SCOPE_IDENTITY();
END

Can anyone tell me why this isn't working?

Thanks!
B
 
 
 

Trigger on INSERT that UPDATES record just inserted

Post by Dan Guzma » Tue, 28 Nov 2006 23:02:39

Your trigger should be able to handle multi-row updates. Try:

UPDATE Ticket
SET [IPAddress]='1.2.3.4'
WHERE [TicketID]=IN
(
SELECT [TicketID]
FROM inserted
)


--
Hope this helps.

Dan Guzman
SQL Server MVP

 
 
 

Trigger on INSERT that UPDATES record just inserted

Post by Aaron Bert » Tue, 28 Nov 2006 23:09:52

Does the insert statement include a value for the IP address? If not, then
I would suggest considering a default value on the column instead of a
trigger.
 
 
 

Trigger on INSERT that UPDATES record just inserted

Post by Uri Diman » Tue, 28 Nov 2006 23:11:50

Hi

In your case SCOPE_IDENTITY() returns NULL
Try using the below statement ( take away an UPDATE statement from the
triggers code)
insert into [dbo].[Ticket] ([IPAddress] ) values ('0000')

select SCOPE_IDENTITY()



Another example

create table t ( i int not null );
create table t_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );

update t
set i = i + 1
output deleted.i, inserted.i into t_audit
where i = 1;

delete from t
output deleted.i, NULL into t_audit
where i = 2;

select * from t;
select * from t_audit;

drop table t, t_audit;
go
 
 
 

Trigger on INSERT that UPDATES record just inserted

Post by SU5UUDU » Tue, 28 Nov 2006 23:13:02

SCOPE_IDENTITY() won't help here because you did no inserts in this scope.
Plus, this assumes only one row is inserted at a time.

Since this is an after trigger, the identity fields should already be
populated. Use the information in the inserted table to help update the base
table. You'll want to update all IP fields where the record shows up in the
inserted table.

HTH,

Bob
 
 
 

Trigger on INSERT that UPDATES record just inserted

Post by bigbrorp » Wed, 29 Nov 2006 04:54:11

Thank you all for your help. The key that I was missing was the
inserted table!
It works great now.

Thanks

B