Friday, March 23, 2012

How to make a trigger refer to the current record

I have a table full of items that have a "date_updated" field. I'd like this field to be set to GETDATE() whenever a record is updated. I've got this trigger:

CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
update cp_shiptos set date_updated = GETDATE()

Problem is, of course, there's no WHERE clause..yet. I don't know how to refer to the record that was updated... for example:

CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
update cp_shiptos set date_updated = GETDATE()
where shipto_id = @.THIS_ID

I imagine there's some kind of builtin variable or something like that. How is this done?

Thanks in advance.CREATE TRIGGER tr_cp_shiptos_u ON dbo.cp_shiptos
FOR UPDATE
AS
BEGIN
UPDATE c
SET date_updated = GETDATE()
FROM cp_shiptos c
JOIN inserted i
ON i.key = c.key
END
GO

No comments:

Post a Comment