Update Trigger 1

[mentasses]

I have a simple update query that runs as a job each evening.The query adds information from the Employee Master table to the Employee Leavers Table:

UPDATE EMPLOYEE.dbo.LEAVERS
SET JOB = JOBTITLE
STARTDATE = CONVERT(Char,b.DATEOFJOIN,103)
FROM EMPLOYEE.dbo.LEAVERS a JOIN EMPLOYEE.dbo.MASTER b
ON a.STAFFNO = b.STAFFNO
WHERE LEAVEDATE IS NOT NULL

I would like to create an update trigger to do this as soon as the LEAVEDATE is entered but have no knowledge of triggers. How do I change the code?

 

[netw3rker]

nice that noone responded to ya

try this:

create trigger moveLeavers on employee.dbo.master
for update as

if (select leaveDate from inserted) < getDate()
UPDATE EMPLOYEE.dbo.LEAVERS
SET JOB = JOBTITLE
STARTDATE = CONVERT(Char,b.DATEOFJOIN,103)
FROM EMPLOYEE.dbo.LEAVERS a JOIN inserted b
ON a.STAFFNO = b.STAFFNO
WHERE LEAVEDATE IS NOT NULL



i'm not super up on the 'create trigger' script command, so you might want to just cut and paste everything from below this into a new update trigger in the enterprise manager create trigger screen.

basically the modified query winds up grabbing the record that was updated, and tests to see if 'leaveDate' is before getDate() meaning that they were just marked as 'outta here' you can really change that to whatever you want though.

the real peice of info you needed for this is that when inside a trigger for either insert or update, a table is created called 'inserted' that has exactly the same structure as the table you are working with, but only contains the data that was inserted on this particular instance. makes things a bit easier

now, your homework is to figure out what happens if more than one record is updated in that particular query (hint: it crashes) so you get to work out how to fix that but this should get you well on your way.

BTW, the sql200 Books online that comes with sql enterprise manager is very through and well documented.. but the help files in enterprise manager itself suck. so be sure to when doing your homework, read up by going to start->programs->sql server 2000 (or 7) -> books online instead of file->help

Hope this helps
-Chris