Easy Trigger question probably

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

Transact-SQL (2000)

Easy Trigger question probably

Author

Topic

EvilTadpole
Starting Member

9 Posts

Posted - 2005-09-14 : 12:51:08

Okay, so I know there is probably a simple solution but something is not clicking in my mind.

I'm trying to create a simple Update trigger. I have a modified date field (smalldatetime) in my table. Every program that feeds into this table updates the modified date field to the current date.

I want a trigger that will update another field in the same table, a flag field (tinyint) to 0 when the modified date field is updated.

So I got as far as this

CREATE TRIGGER UpdateFlag ON [dbo].[TEST_TABLE]
FOR UPDATE
AS

IF UPDATE (MODIFIEDDATE)
BEGIN
Update TEST_TABLE
SET UPDFLG = 0
END

Now as it stands if a the modified date field is updated, the update flag is updated for every single record in the table, rather than just the record that had its modified date field updated. What do I do?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-14 : 12:59:10

You need to JOIN to the inserted table. The inserted table contains the rows that were affected by the UPDATE. Check out deleted and inserted tables in regards to trigger in SQL Server Books Online for more details.

UPDATE t
SET UPDFLG = 0
FROM TEST_TABLE t
INNER JOIN inserted i
ON t.YourPKColumn = i.YourPKColumn

Tara

EvilTadpole
Starting Member

9 Posts

Posted - 2005-09-15 : 11:32:49

Thanks so much, I never knew about the insert and delete tables, makes more sense now.
Thanks
Erika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources