Updating current record via a trigger 1

[Kelin]

I am trying to update the current record a user is working on via a trigger but it is updating all of the records since I cannot designate the actual record in the trigger.

CREATE TRIGGER TimeDate ON dbo.Asgnmnt
FOR INSERT, UPDATE, DELETE
AS
Update Asgnmnt
set TimeAssign = (select convert(varchar(8),getdate(),108) as [time])
*where callid = 'whatever the callid is the client is working on'*

*This is where I would need it to update the particular record the user is working on, in this case a callid.*

 

[JohnDTampaBay]

Two virtual tables are accessable within a trigger: Inserted and Deleted. Updates are a combination of the two statements. These virtual tables only exist during the transaction and have the same structure as the table being modified. Join to these tables to "touch" the rows being modified. In your case, I suspect the trigger would look something like this:

CREATE TRIGGER TimeDate ON dbo.Asgnmnt
FOR INSERT, UPDATE, DELETE 
AS
BEGIN
Update Asgnmnt
set TimeAssign = (select convert(varchar(8),getdate(),108) as [time])
FROM Asgnmnt a JOIN Inserted i ON a.CallID = i.CallID

Update Asgnmnt
set TimeAssign = (select convert(varchar(8),getdate(),108) as [time])
FROM Asgnmnt a JOIN Deleted d ON a.CallID = d.CallID
END

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[Kelin]

That worked perfectly. Thank you very much for the assistance.

Kelin