Trigger Value

[moone009]

Basically I have a trigger that is fired off whenever a workorder is reopened to close it again.

The trigger works perfectly but I want to track the workorders where there is an attempt to reopen it.

so basically if workorderid 8 is attempted to open again I want to insert this value into triggerLajob along with the date.

I cannot figure out how to pull that actual value of when the trigger is fired.

any ideas.

SQL SERVER 2005 Dev edition

ALTER TRIGGER WorkOrderClosedStatus ON Lajob
AFTER UPDATE 
AS
IF  UPDATE(statusid)


Update Lajob
set statusid = '39A80787-B084-DD11-9BCC-0014221E87F0'
where statusid = '38A80787-B084-DD11-9BCC-0014221E87F0'
print 'Trigger has been fired'

Insert into triggerLajob
--values I want inserted into table select workorderid , getdate() 


GO

[BitBlt]

You can make use of the special tables "inserted" and "deleted" to get the values you're interested in. "Inserted" gives you the post-update value. "Deleted" gives you the pre-update value. Alter your trigger thusly:

ALTER TRIGGER WorkOrderClosedStatus ON Lajob
AFTER UPDATE 
AS
IF  UPDATE(statusid)
begin

Insert into triggerLajob
(workorderid, statusid, triggerfiredate)
select workorderid, statusid, getdate() from inserted

Insert into triggerLajob
(workorderid, statusid, triggerfiredate)
select workorderid, statusid, getdate() from deleted

Update Lajob
set statusid = '39A80787-B084-DD11-9BCC-0014221E87F0'
where statusid = '38A80787-B084-DD11-9BCC-0014221E87F0'
print 'Trigger has been fired'
end
go

Then, just run a query and look at what got inserted into table triggerLajob. Decide which one you want to keep, and alter your trigger accordingly.

Hope this helps! Good luck!