Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need to audit update without cursor Expand / Collapse
Author
Message
Posted Monday, January 9, 2012 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 9, 2012 3:37 PM
Points: 2, Visits: 7
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

Post #1232730
Posted Monday, January 9, 2012 1:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 18, 2012 10:58 PM
Points: 63, Visits: 216
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
Post #1232774
Posted Monday, January 9, 2012 1:44 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 317, Visits: 875
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)





Colleen M. Morrow
Cleveland DBA
Post #1232780
Posted Monday, January 9, 2012 1:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1232782
Posted Monday, January 9, 2012 1:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:43 AM
Points: 6,826, Visits: 13,279
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
)





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1232787
Posted Monday, January 9, 2012 1:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1232788
Posted Monday, January 9, 2012 1:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1232792
Posted Monday, January 9, 2012 3:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 9, 2012 3:37 PM
Points: 2, Visits: 7
Thanks for all replies, it looks like the OUTPUT clause will be the way to go on this one.

Many thanks.
Post #1232837
Posted Monday, January 9, 2012 3:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1232840
Posted Wednesday, January 11, 2012 2:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1234376
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse