Last Modified Trigger - 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 2008 Forums

Transact-SQL (2008)

Last Modified Trigger

Author

Topic

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-03-23 : 16:52:46

So, I have a front-end that users access to update records in a database. I have a column that I want to use to track when the record was last modified, which i want to do with a trigger. However, i'm confused on how to do this.

[Code]
CREATE TRIGGER [dbo].[Modified_Date] ON [dbo].[TableA]
FOR UPDATE AS

UPDATE TableA
SET Last_Modified_Date = getdate()
FROM TableA
[/Code]

Obviously this is going to update every record's Modified_Date with getdate()(which i do not want), because there is no WHERE statement. But what do i put in my WHERE statement....i'm passing a parameter from the form to the database? I only want to do this to the record i modified.

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-03-23 : 17:11:30

UPDATE TableA
SET Last_Modified_Date = getdate()
FROM TableA A join inserted i on a.primarykeycolumn = i.primarykeycolumn

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-03-23 : 17:17:06

It's updated, not inserted, so I don't see how JOIN INSERTED would work?

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-23 : 17:21:38

[Code]
CREATE TRIGGER [dbo].[Modified_Date] ON [dbo].[TableA]
FOR UPDATE AS

UPDATE TableA
SET Last_Modified_Date = getdate()
FROM TableA A INNER JOIN INSERTED I ON A.ID=I.ID AND
INNER JOIN DELETED D ON D.ID=I.ID WHERE I.COLUMN1 <> D.COLUMN1
[/Code]

You will need to make use of the INSERTED and DELETED tables.
I've just drafted this but not too sure if its going to work.
The ID represented here is the Primary Key of table A.
When an update happens, 2 entries would be made, 1 to Inserted table and 1 to Deleted table.You should be able to find the same ID in both tables. Also where clause [Underlined] is used if you need to capture specific condition, In this example COLUMN1 value has been updated

Other Guru's : Go ahead and correct me if Im mistaken.

Brittney10
Posting Yak Master

154 Posts

Posted - 2011-03-23 : 17:39:17

Okay, I got it. Thanks guys! This helped clear things up for me....

[url]http://msdn.microsoft.com/en-us/library/ms191300.aspx[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources