Need to audit update without cursor

  • 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

  • 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

  • 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

  • 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

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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

  • 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

  • Thanks for all replies, it looks like the OUTPUT clause will be the way to go on this one.

    Many thanks.

  • 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

  • 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

  • If you are running Enterprise Edition, you also have the option of Change Data Capture.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply