Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    1

    Unanswered: Trigger Insert Record on Update

    I have a parent table with 27 Columns and Child Table with 37 colums - when even there is an update in any of the columns on Parent or Child table, I require new record inserted into Audit_Parent and Audit_child table. Please help with
    SQL Code on Create Trigger and insert records into Audit_parent and Audit_child when an Update occurs on any of the columns.
    Insert into AuditParent and AuditChild should occur whenever there is an update on either Parent or child table.

    Thanks


  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Books Online has an example of this very thing. This one is for update only. To make it for both, just add ", INSERT" after the FOR UPDATE and wah lah, you are done.

    USE pubs
    IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'employeeData')
    DROP TABLE employeeData
    IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'auditEmployeeData')
    DROP TABLE auditEmployeeData
    GO
    CREATE TABLE employeeData (
    emp_id int NOT NULL,
    emp_bankAccountNumber char (10) NOT NULL,
    emp_salary int NOT NULL,
    emp_SSN char (11) NOT NULL,
    emp_lname nchar (32) NOT NULL,
    emp_fname nchar (32) NOT NULL,
    emp_manager int NOT NULL
    )
    GO
    CREATE TABLE auditEmployeeData (
    audit_log_id uniqueidentifier DEFAULT NEWID(),
    audit_log_type char (3) NOT NULL,
    audit_emp_id int NOT NULL,
    audit_emp_bankAccountNumber char (10) NULL,
    audit_emp_salary int NULL,
    audit_emp_SSN char (11) NULL,
    audit_user sysname DEFAULT SUSER_SNAME(),
    audit_changed datetime DEFAULT GETDATE()
    )
    GO
    CREATE TRIGGER updEmployeeData
    ON employeeData
    FOR update AS
    /*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/

    IF (COLUMNS_UPDATED() & 14) > 0
    /*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
    BEGIN
    -- Audit OLD record.
    INSERT INTO auditEmployeeData
    (audit_log_type,
    audit_emp_id,
    audit_emp_bankAccountNumber,
    audit_emp_salary,
    audit_emp_SSN)
    SELECT 'OLD',
    del.emp_id,
    del.emp_bankAccountNumber,
    del.emp_salary,
    del.emp_SSN
    FROM deleted del

    -- Audit NEW record.
    INSERT INTO auditEmployeeData
    (audit_log_type,
    audit_emp_id,
    audit_emp_bankAccountNumber,
    audit_emp_salary,
    audit_emp_SSN)
    SELECT 'NEW',
    ins.emp_id,
    ins.emp_bankAccountNumber,
    ins.emp_salary,
    ins.emp_SSN
    FROM inserted ins
    END
    GO

    /*Inserting a new employee does not cause the UPDATE trigger to fire.*/
    INSERT INTO employeeData
    VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)
    GO

    /*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/

    UPDATE employeeData
    SET emp_salary = 51000
    WHERE emp_id = 101
    GO
    SELECT * FROM auditEmployeeData
    GO

    /*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/

    UPDATE employeeData
    SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
    WHERE emp_id = 101
    GO
    SELECT * FROM auditEmployeeData
    GO
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •