SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to audit update without cursor


Need to audit update without cursor

Author
Message
MSSQLnewb
MSSQLnewb
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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


stephen99999
stephen99999
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 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
Colleen M. Morrow
Colleen M. Morrow
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 1060
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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23607 Visits: 9730
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
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10367 Visits: 13559
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
drew.allen
drew.allen
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6274 Visits: 10871
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23607 Visits: 9730
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
MSSQLnewb
MSSQLnewb
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 7
Thanks for all replies, it looks like the OUTPUT clause will be the way to go on this one.

Many thanks.
drew.allen
drew.allen
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6274 Visits: 10871
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23607 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search