You’re offline. This is a read only version of the page.
Hello,
Terry Healy provided my team with a script for having an audit table created when changes are made to specific tables. My team is looking to add a table HRORI012.
Here is an example of the triggers on UPR00100 my question how do I create it on this new table?
--begin---
create trigger [trackEmployeeMasterChanges]
on UPR00100
for update
as
if update(EMPLCLAS)
begin
insert trackPayrollChanges
select system_user,
'UPR00100',
'EMPLCLAS',
getdate(),
cast(a.EMPLCLAS as char(50)),
cast(b.EMPLCLAS as char(50)),
a.EMPLOYID,
'Employee Class changed for Employee: ' + a.EMPLOYID
from deleted a
inner join inserted b
on a.EMPLOYID = b.EMPLOYID
end
if update(INACTIVE)
begin
insert trackPayrollChanges
select system_user,
'UPR00100',
'INACTIVE',
getdate(),
cast(a.INACTIVE as char(50)),
cast(b.INACTIVE as char(50)),
a.EMPLOYID,
'Inactive changed for Employee: ' + a.EMPLOYID
from deleted a
inner join inserted b
on a.EMPLOYID = b.EMPLOYID
end
if update(LASTNAME)
begin
insert trackPayrollChanges
select system_user,
'UPR00100',
'LASTNAME',
getdate(),
cast(a.LASTNAME as char(50)),
cast(b.LASTNAME as char(50)),
a.EMPLOYID,
'Last Name changed for Employee: ' + a.EMPLOYID
from deleted a
inner join inserted b
on a.EMPLOYID = b.EMPLOYID
end
if update(FRSTNAME)
begin
insert trackPayrollChanges
select system_user,
'UPR00100',
'FRSTNAME',
getdate(),
cast(a.FRSTNAME as char(50)),
cast(b.FRSTNAME as char(50)),
a.EMPLOYID,
'First Name changed for Employee: ' + a.EMPLOYID
from deleted a
inner join inserted b
on a.EMPLOYID = b.EMPLOYID
end
if update(MIDLNAME)
begin
insert trackPayrollChanges
select system_user,
'UPR00100',
'MIDLNAME',
getdate(),
cast(a.MIDLNAME as char(50)),
cast(b.MIDLNAME as char(50)),
a.EMPLOYID,
'Middle Name changed for Employee: ' + a.EMPLOYID
from deleted a
inner join inserted b
on a.EMPLOYID = b.EMPLOYID
end
\........there is more, but you get the idea......../
*This post is locked for comments
I have the same question (0)
You really should ask Terry for this.
This should work:
CREATE TABLE trackPayrollChanges (
USERID CHAR(50),
TABLENME CHAR(30),
CHNGFELD CHAR(30),
CHGDDATE DATETIME,
OLDDATA CHAR(255),
NEWDATA CHAR(255),
EMPLOYID CHAR(15),
MESSAGE CHAR(255)
)
GO
GRANT SELECT ON trackPayrollChanges TO DYNGRP
GO
Remember a trigger that fails will interrupt GP (never good).
Remember to test before applying to a production environment.
Tim
P.S. I make no guarantees for this code.
Share
Report
All responses (
Answers (
8,515
