updating a column in a trigger after insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

updating a column in a trigger after insert

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-11-06 : 17:01:06

I'd like to update a column after an insert using a trigger.

It'll look like this:

Create Trigger TR_SomeName
ON SomeTable
After INSERT
AS

IF Exists (Select * From Inserted)
BEGIN

--Update SomeColumn from SomeTable

END
GO

My question is since this is an AFTER INSERT, can I still update the column from the Inserted table? That is the whole point of this exercise. If I must use ON INSERT to modify the contents of the insert table, how would I go about updating a column?

Thank you very much.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-06 : 18:16:46

SQL Server knows:
Create Trigger TR_SomeName
ON SomeTable
After INSERT / INSTEAD OF INSERT
(AFTER is the default, if FOR is the only keyword specified: FOR INSERT is same to AFTER INSERT and FOR AFTER INSERT)
and ON INSERT is not known

AFTER INSERT: use information from INSERTED to make, for example, your additional updates on SomeTable
INSTEAD OF INSERT: Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements. But INSERTED (and DELETED when updating or deleting) are filled with information like it is using AFTER INSERT trigger.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-06 : 18:35:27

[code]
Update MyTable
set
MyColumn = <some value>
where
MyTable.PKColumn in
( select inserted.PKColumn from inserted )
[/code]

CODO ERGO SUM

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-11-10 : 14:34:10

Thank you very much for the help, for I got it to work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources