Trigger that updates a field - 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)

Trigger that updates a field

Author

Topic

flamz
Starting Member

21 Posts

Posted - 2008-04-02 : 09:58:50

Hi,
I need to setup a simple trigger that will update a field every time any of the other fields in that record changes (or when a new record is inserted).

Basically, every record will have a last_modified date that will be automatically written to by this trigger.

Any tips? I am new to triggers. I've seen a few examples out there, but they all seam to update a singleton field in another table. Id like my trigger to update the very record being udpated.

tx!

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 10:02:29

create trigger x on tbl for inert, update
as
update tbl
set lastupdated = getdate()
from tbl t
join insertd i
on t.pk = i.pk
go

That will also update the date if the row is updated but no data is changed - is that what you want? Otyherwise you will have to check all the values.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

flamz
Starting Member

21 Posts

Posted - 2008-04-03 : 11:01:42

Thanks, that is exactly what I wanted and it works perfectly.

Now I need a new trigger that will track records that are deleted. Basically, whenever a record gets deleted (in a set of tables, or all tables), I would like to insert a new record in a table.

Is there such a thing as a "global" or "multi-table" trigger? Or will I have to create a delete trigger for each table I wish to track this way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 11:04:57

quote:
Originally posted by flamz

Thanks, that is exactly what I wanted and it works perfectly.

Now I need a new trigger that will track records that are deleted. Basically, whenever a record gets deleted (in a set of tables, or all tables), I would like to insert a new record in a table.

Is there such a thing as a "global" or "multi-table" trigger? Or will I have to create a delete trigger for each table I wish to track this way?

just like insert,update trigger you need to write a delete trigger for each table you want to track deletion action. You cant have a global trigger as trigger is always created for a table to monitor events happening for that table like insert,update,delete. So you need to create a new one for each table.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-04 : 13:57:52

You can generate a trigger for each table automatically quite easily.
You can also log all insert/update/deletes to a single table (but beware of efficiency issues)
see
http://www.nigelrivett.net/#Triggers

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources