January 9, 2012 at 12:24 pm
Hi All,
I have a client that has separate HR and Payroll systems each with their own database. There is a requirement to update some employee details from one system to the other, however this process needs to be audited. If a value is updated this needs to be logged in a separate table.
This process needs to be done via a SQL script and I can not change this. Ideally this would be done by the application for the destination system, however this application can not be changed.
The only way in which I have managed to achieve this is using a cursor, I would appreciate any suggestions on how to approach this differently.
-- Setup tables and sample data
CREATE TABLE #HR_employees (
employee_code INT PRIMARY KEY,
firstname VARCHAR(20),
surname VARCHAR(20),
department VARCHAR(15),
email VARCHAR(200)
)
CREATE TABLE #Payroll_employees (
employee_code INT PRIMARY KEY ,
firstname VARCHAR(20),
surname VARCHAR(20),
department VARCHAR(15),
email VARCHAR(200)
)
CREATE TABLE #log (
id INT IDENTITY(1,1) PRIMARY KEY,
Date_of_change DATETIME NOT NULL,
Field VARCHAR(20) NOT NULL,
OldValue VARCHAR(40) NOT NULL,
NewValue VARCHAR(40) NOT NULL
)
INSERT INTO #HR_employees (employee_code, firstname, surname, department, email)
VALUES (1, 'Dave', 'Smith', 'IT', 'dave.smith@email.com'),
(2, 'John', 'Smith', 'Admin', 'john.smith@email.com'),
(3, 'Luke', 'Donald', 'IT', 'luke.donald@email.com'),
(4, 'Rob', 'Hurst', 'IT', 'rob.hurst@email.com'),
(5, 'Garry', 'Hilburt', 'IT', 'garry.hilburt@email.com')
INSERT INTO #Payroll_employees(employee_code, firstname, surname, department, email)
VALUES (1, 'Dave', 'Smith', 'IT', 'dave.smith@email.com'),
(2, 'John', 'Smith', 'Admin', 'john.smith@email.com'),
(3, 'Luke', 'Donald', 'IT', 'luke.donald@email.com'),
(4, 'rob', 'Smith', 'IT', 'rob.smith@email.com'),
(5, 'Harry', 'Hilburt', 'IT', 'harry.hilburt@email.com')
-- Cursor
DECLARE @HR_employee_code INT,
@HR_firstname VARCHAR(20),
@HR_surname VARCHAR(20),
@HR_department VARCHAR(15),
@HR_email VARCHAR(200),
@Payroll_firstname VARCHAR(20),
@Payroll_surname VARCHAR(20),
@Payroll_department VARCHAR(15),
@Payroll_email VARCHAR(200)
DECLARE update_employees CURSOR
FOR
SELECT HR.employee_code,
HR.firstname,
HR.surname,
HR.department,
HR.email,
Payroll.firstname,
Payroll.surname,
Payroll.department,
Payroll.email
FROM #HR_employees AS HR
INNER JOIN #Payroll_employees AS Payroll ON Payroll.employee_code = HR.employee_code
OPEN update_employees
FETCH NEXT FROM update_employees INTO
@HR_employee_code,
@HR_firstname,
@HR_surname,
@HR_department,
@HR_email,
@Payroll_firstname,
@Payroll_surname,
@Payroll_department,
@Payroll_email
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Payroll_firstname != @HR_firstname
BEGIN
UPDATE #Payroll_employees
SET firstname = @HR_firstname
WHERE #Payroll_employees.employee_code = @HR_employee_code
INSERT INTO #log (Date_of_change, Field, OldValue, NewValue)
VALUES (GETDATE(), 'Firstname', @Payroll_firstname, @HR_firstname)
END
IF @Payroll_surname != @HR_surname
BEGIN
UPDATE #Payroll_employees
SET surname = @HR_surname
WHERE #Payroll_employees.employee_code = @HR_employee_code
INSERT INTO #log (Date_of_change, Field, OldValue, NewValue)
VALUES (GETDATE(), 'Surname', @Payroll_surname, @HR_surname)
END
-- You get the idea
FETCH NEXT FROM update_employees INTO
@HR_employee_code,
@HR_firstname,
@HR_surname,
@HR_department,
@HR_email,
@Payroll_firstname,
@Payroll_surname,
@Payroll_department,
@Payroll_email
END
CLOSE update_employees
DEALLOCATE update_employees
SELECT * FROM #log
DROP TABLE #HR_employees
DROP TABLE #Payroll_employees
DROP TABLE #log
January 9, 2012 at 1:36 pm
Not sure I fully understand the requirements, but why don't you just declare your parameters and then do your If/Else statements, and drop the cursor all together?
Stephen
January 9, 2012 at 1:44 pm
If all you're doing is updates, you can so this with an update trigger and a single update statement:
The trigger:
ALTER TRIGGER upd_PayrollEmployee
ON Payroll_employees
AFTER update
AS
IF COLUMNS_UPDATED() & 2 = 2
INSERT mylog (Date_of_change, Field, OldValue, NewValue)
SELECT GETDATE(), 'firstname', d.firstname, i.firstname
FROM inserted i JOIN deleted d on i.employee_code = d.employee_code
AND d.firstname <> i.firstname
IF COLUMNS_UPDATED() & 4 = 4
INSERT mylog (Date_of_change, Field, OldValue, NewValue)
SELECT GETDATE(), 'surname', d.surname, i.surname
FROM inserted i JOIN deleted d on i.employee_code = d.employee_code
AND d.surname <> i.surname
IF COLUMNS_UPDATED() & 8 = 8
INSERT mylog (Date_of_change, Field, OldValue, NewValue)
SELECT GETDATE(), 'department', d.department, i.department
FROM inserted i JOIN deleted d on i.employee_code = d.employee_code
AND d.department <> i.department
IF COLUMNS_UPDATED() & 16 = 16
INSERT mylog (Date_of_change, Field, OldValue, NewValue)
SELECT GETDATE(), 'email', d.email, i.email
FROM inserted i JOIN deleted d on i.employee_code = d.employee_code
AND d.email <> i.email
GO
The update:
UPDATE Payroll_employees SET
firstname = h.firstname, surname = h.surname, department =h.department , email=h.email
FROM HR_employees h
WHERE Payroll_employees.employee_code = h.employee_code
and (Payroll_employees.firstname <> h.firstname
OR Payroll_employees.surname <> h.surname
OR Payroll_employees.department <> h.department
OR Payroll_employees.email <> h.email)
January 9, 2012 at 1:46 pm
This method would have to go through the whole of both tables each time it's run, even if you just change one value in one table.
I would use an Output clause on your Update statement to just grab the rows and values that are actually changed, for each transaction.
Example:
Update
dbo.Employees
Set
NameLast = @NameLast,
NameFirst = @NameFirst
Output
deleted.EmployeeNumber,
deleted.NameLast,
deleted.NameFirst
Into dbo.LogTable (
EmployeeNumber,
OldNameLast,
OldNameFirst)
Where
EmployeeNumber = @EmployeeNumber
Variations on that will log just the rows that were actually changed.
Would that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2012 at 1:51 pm
To expand on Gus solution:
(please note I added employee_code to the internal table variable since I think it might be helpful to know what row the change refers to 😉 )
DECLARE @MyTableVar table(
employee_code int NOT NULL,
Date_of_change DATETIME NOT NULL,
firstname_new VARCHAR(20),
firstname_old VARCHAR(20),
surname_new VARCHAR(20),
surname_old VARCHAR(20)
);
UPDATE #Payroll_employees
SET
firstname = HR.firstname,
surname = HR.surname
OUTPUT
INSERTED.employee_code,
GETDATE(),
INSERTED.firstname,
DELETED.firstname,
INSERTED.surname,
DELETED.surname
INTO @MyTableVar
FROM #HR_employees AS HR
INNER JOIN #Payroll_employees AS Payroll ON Payroll.employee_code = HR.employee_code
WHERE
(
Payroll.firstname != HR.firstname
OR Payroll.surname != HR.surname
)
INSERT INTO #log (Date_of_change, Field, OldValue, NewValue)
SELECT
--employee_code,
Date_of_change,
ColName AS ColumnName,
CASE WHEN ColName = 'Firstname' AND firstname_old != firstname_new
THEN firstname_old
ELSE surname_old
END AS OldValue,
CASE WHEN ColName = 'Firstname' AND firstname_old != firstname_new
THEN firstname_new
ELSE surname_new
END AS OldValue
FROM @MyTableVar MTV
CROSS APPLY
(
SELECT 'Firstname' AS ColName UNION ALL
SELECT 'Surname'
)sub
WHERE
(
ColName = 'Firstname' AND firstname_old != firstname_new
OR
ColName = 'Surname' AND surname_old != surname_new
)
January 9, 2012 at 1:53 pm
This is sometimes done using a trigger, but you may not have rights to create a trigger on the table being updated. Another option is to use the OUTPUT clause of the UPDATE statement to output the necessary columns from the INSERTED and/or DELETED special tables.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 9, 2012 at 1:58 pm
Audit logging doesn't need to use the "inserted" data. You already have that in the table you just updated. Logging it just increases the size of the log and doesn't actually add any value to it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2012 at 3:09 pm
Thanks for all replies, it looks like the OUTPUT clause will be the way to go on this one.
Many thanks.
January 9, 2012 at 3:15 pm
GSquared (1/9/2012)
Audit logging doesn't need to use the "inserted" data. You already have that in the table you just updated. Logging it just increases the size of the log and doesn't actually add any value to it.
If you're using the OUTPUT clause, you may want to capture both, because it's possible that there are other queries that won't create the audit information and you may want to capture all of the information at the time of the original update.
Using a trigger, it will obviously fire any time there is an update, so you wouldn't need to capture the inserted data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 11, 2012 at 2:13 pm
drew.allen (1/9/2012)
GSquared (1/9/2012)
Audit logging doesn't need to use the "inserted" data. You already have that in the table you just updated. Logging it just increases the size of the log and doesn't actually add any value to it.If you're using the OUTPUT clause, you may want to capture both, because it's possible that there are other queries that won't create the audit information and you may want to capture all of the information at the time of the original update.
Using a trigger, it will obviously fire any time there is an update, so you wouldn't need to capture the inserted data.
Drew
True. And in those cases, you definitely need a Rowversion column, which needs to be captured in the audit, so that you can tell if data was changed outside the auditing scope.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 11, 2012 at 2:22 pm
If you are running Enterprise Edition, you also have the option of Change Data Capture.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply