Update Trigger - Handle Single & Multirow Updates

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)

Update Trigger - Handle Single & Multirow Updates

Author

Topic

Apollois
Starting Member

49 Posts

Posted - 2002-10-14 : 20:57:13

Hi All,

I need some help in optmizing an UPDATE TRIGGER that should handle both updates to a single record and to muiltiple records.

The purpose of the trigger is to set the column "Last_Update" to the current date/time whenever any column in the record is changed.

Is there a better/faster way of handling this?

Here's my current code:


===============================
ALTER  TRIGGER Users_Update ON Users
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
UPDATE Users 
   SET Last_Update=GetDate()
   FROM Inserted i
      Inner Join Users u on i.User_PK = u.User_PK
=====================================

TIA.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-14 : 21:09:33

Looks Good.

Could you add GetDate() to your insert and update queries?

Apollois
Starting Member

49 Posts

Posted - 2002-10-14 : 23:25:07

quote:
Looks Good.
Could you add GetDate() to your insert and update queries?

Thanks for your reply. I could add GetDate(), but I want to make sure that the Last_Update col is always updated regardless of the application. I don't want to depend on any ones code.

Plus, the trigger also takes care of changes from EM and QA.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-18 : 18:59:55

Why not control changes to your table by giving users permissions to use sp's but not edit table directly?

Apollois
Starting Member

49 Posts

Posted - 2002-10-18 : 22:39:24

quote:

Why not control changes to your table by giving users permissions to use sp's but not edit table directly?

I'm not sure I understand your question.

The purpose of my trigger doesn't have any thing to do with conrolling access to the data. I just want to make sure the column Last_Update is always updated to the current D/T whenever a change occurs to the record.

Best Regards,
Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources