July 17, 2006 at 2:03 pm
I'm needing to basically create a minor audit trail for a column that I have in a database.
The table name is Employees and the column I'm needing to audit for changes is one called TeamID.
What I'm needing to know is :
Can someone give me a fairly quick sample create trigger statement for this. The table I'm wanting would store the following: TeamUpdate, Employee ID, Old Team. I'm having trouble determining how to reference the exact record I'm updating and want to make sure rather than fumbling around tryin to figure this out.
Another question I have is if someone is using an Access front-end Form and "updates" the record, does this also cause the update trigger to fire off in SQL or do I need to worry about trapping this behavior somewhere else?
Thanks in advance.
July 17, 2006 at 2:10 pm
Create trigger tr_upd_employee on Employees for insert
as
if @@rowCount = 0 return
if Update ( TeamID )
begin
Insert into AuditTable ( EmlpoyeeID, OldTeam, NewTeam)
select new.EmployeeID, ot.TeamName, nt.TeamName
from inserted new join deleted old on new.EmployeeID = old.EmployeeID
left join Teams ot on old.TeamID = ot.TeamName
left join Teams nt on new.TeamID = nt.TeamName
end
GO
And The Trigger will fire regardless what front-end you use
Cheers,
* Noel
July 17, 2006 at 7:30 pm
The code above is correct, except that the requirements seem to specify that should apply to table updates. It does seem reasonable to capture the initial insert as well, which the current code does. To capture updates, simply change the first line to read:
Create trigger tr_upd_employee on Employees for insert, update
Don't forget to make OldTeam nullable in audit table for the inserts.
Scott Thornburg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply