Simple Trigger Question - SQL Server Forums

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)

Simple Trigger Question

Author

Topic

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2003-03-26 : 01:54:37

Hi,
Say I have a trigger firing upon UPDATE, INSERT, DELETE on the ProductID of the Products table.
Now in the trigger, how do I grab the ProductID of the row that just got affected? Thanks

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-03-26 : 01:57:45

In a trigger you have the Inserted and Deleted table, these virtual tables contain the rows just affected.

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2003-03-26 : 13:45:33

So what about the Updated virtual table? If Inserted, Updated, Deleted all will have a ProductID, what is the best practice to select the field based on which action is taken? Thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-26 : 13:47:47

There is no 'updated' table.

On update, 'inserted' will have the new values and 'deleted' will have the old.

Jay White
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-26 : 15:35:09

CREATE TRIGGER Company_UpdTr ON Company
FOR INSERT, UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

-- UPDATE Action. Delete contains replaced values, INSERTED Contains new values

Select *
From Deleted
END

If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

BEGIN
--Deleted Action. There are no Rows in INSERTED
Select *
From Deleted
END

If Exists (Select 1 From Inserted) And Not Exists (Select 1 From Deleted)

BEGIN
--Insert Action. There are no Rows in DELETED
Select *
From Deleted
END

Brett

8-)

Edited by - x002548 on 03/26/2003 15:35:32

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources