Db Trigger Question

Hello,

I have a db trigger (example below) that is supposed to update a column with the current date. However, I need to know which row is being updated by a user so that I would update ONLY this row. Is there a straightforward way to figure out which row is updated by a user? My table includes a column called rowid which is a primary key.

Thanks for any help!

CREATE TRIGGER dbo.[DatetimeUpdate]

ON dbo.DepartmentMapping] AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

UPDATE dbo.DepartmentMapping

SET DateUpdated = GetDate()

Donnie:

You need to take advangage of the INSERTED pseudo-table in your trigger. Look up CREATE TRIGGER in books online and examine their examples. Your update ought to change to something like:

UPDATE dbo.DepartmentMapping
SET DateUpdated = GetDate()
FROM inserted i
INNER JOIN dbo.DepartmentMapping a
ON a.{departmentMappingKey} = i.{departmentMappingKey}

|||

When code inside a trigger is being executed, the code has access to two 'Virtual Tables' named Inserted and Deleted.

In the case of an UPDATE statement, the Deleted table contains the rows as they would have been before any changes and the Inserted table contains the rows as they are after the changes. The virtual tables have the same columns as the table on which the trigger is defined.

You could amend your code to that shown below.

Chris

UPDATE dm

SET DateUpdated = GetDate()

FROM dbo.DepartmentMapping dm

INNER JOIN deleted d ON d.DepartmentMappingID = dm.DepartmentMappingID

--or equally

--INNER JOIN inserted i ON i.DepartmentMappingID = dm.DepartmentMappingID

|||Great. Thank You!|||Of course you wouldn't want to use deleted in this case, since you would be trying to update a deleted row :)sql