#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535

    Creating audit trails using triggers


    I originally asked this question in the PHP Forum. Based on those discussions, I wish to pursue using triggers.

    I would like to log all changes to my database on a per user basis. When I say "per user", I mean a user who logs on to my website, not a system or MySQL user. My goal is if some asks who changed a record to a given value, I would be able to research and provide an answer. As such, it is not enough to log "Bob made a change to table ABC on April 4th", but I need to be able to deduce that "Bob changed field XYZ of table ABC to 123 on April 4th".

    My plan is to use two tables to store all my audit trails from any of my tables which I want to audit. To do so, I will use one table called "audits" which will have a column for the name of the table to audit and another column for the PK value of the record to audit. I will then store any changes to any columns of the above references table and associated PK in another table called "auditFields".

    Please see the below script for details. Can anyone comment on my approach?

    Thank you


    Code:
    -- -----------------------------------------------------
    -- Table `auditTasks`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTasks` (
      `task` CHAR(1) NOT NULL ,
      PRIMARY KEY (`task`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `audits`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `audits` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `tableName` VARCHAR(45) NOT NULL ,
      `tablePK` VARCHAR(45) NOT NULL ,
      `task` CHAR(1) NOT NULL ,
      `dateChanged` DATETIME NOT NULL ,
      `requesting_ip` CHAR(15) NULL ,
      `users_id` INT UNSIGNED NULL ,
      `dbUser` VARCHAR(45) NOT NULL ,
      PRIMARY KEY (`id`) ,
      INDEX `fk_audits_users1_idx` (`users_id` ASC) ,
      INDEX `fk_audits_auditTasks1_idx` (`task` ASC) ,
      CONSTRAINT `fk_audits_users1`
        FOREIGN KEY (`users_id` )
        REFERENCES `users` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_audits_auditTasks1`
        FOREIGN KEY (`task` )
        REFERENCES `auditTasks` (`task` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditFields`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditFields` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `audits_id` INT UNSIGNED NOT NULL ,
      `fieldName` VARCHAR(45) NOT NULL ,
      `oldValue` VARCHAR(45) NULL ,
      `newValue` VARCHAR(45) NULL ,
      PRIMARY KEY (`id`, `fieldName`) ,
      INDEX `fk_auditFields_audits1_idx` (`audits_id` ASC) ,
      CONSTRAINT `fk_auditFields_audits1`
        FOREIGN KEY (`audits_id` )
        REFERENCES `audits` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    -- -----------------------------------------------------
    -- Table `contacts`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `contacts` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `firstname` VARCHAR(45) NOT NULL ,
      `lastname` VARCHAR(45) NOT NULL ,
      `email` VARCHAR(45) NULL ,
      `fax` CHAR(10) NULL ,
      `phone` CHAR(10) NULL ,
      `mobile_phone` CHAR(10) NULL ,
      `username` VARCHAR(45) NULL ,
      `old_username` VARCHAR(45) NULL ,
      `pass_word` VARCHAR(45) NULL ,
      `date_created` DATETIME NOT NULL ,
      `date_modified` TIMESTAMP NOT NULL ,
      `communication_method_id` INT UNSIGNED NOT NULL ,
      `accounts_id` INT UNSIGNED NOT NULL ,
      `sites_id` INT UNSIGNED NOT NULL ,
      `title` VARCHAR(45) NULL ,
      `user_deleted_by` INT UNSIGNED NULL ,
      `record_status` VARCHAR(8) NOT NULL ,
      PRIMARY KEY (`id`) ,
      INDEX `fk_contacts_accounts1_idx` (`accounts_id` ASC) ,
      INDEX `fk_contacts_sites1_idx` (`sites_id` ASC) ,
      INDEX `fk_contacts_users1_idx` (`user_deleted_by` ASC) ,
      INDEX `fk_contacts_record1_idx` (`record_status` ASC) ,
      CONSTRAINT `fk_contacts_accounts1`
        FOREIGN KEY (`accounts_id` )
        REFERENCES `accounts` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_contacts_sites1`
        FOREIGN KEY (`sites_id` )
        REFERENCES `sites` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_contacts_users1`
        FOREIGN KEY (`user_deleted_by` )
        REFERENCES `users` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_contacts_record1`
        FOREIGN KEY (`record_status` )
        REFERENCES `record` (`status` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    DELIMITER $$
    
    
    CREATE TRIGGER tg_contacts_ins AFTER INSERT ON contacts
    FOR EACH ROW 
    BEGIN
        INSERT INTO audits(tableName,tablePK,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('contacts', NEW.id, 'i', NOW(), @users_id, USER(), @requesting_ip );
        SET @AID=LAST_INSERT_ID();
        IF NEW.firstname IS NOT NULL THEN
            INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'firstname',NULL,NEW.firstname);
        END IF;
        IF NEW.lastname IS NOT NULL THEN
            INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'lastname',NULL,NEW.lastname);
        END IF;
        IF NEW.email IS NOT NULL THEN
            INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'email',NULL,NEW.email);
        END IF;
        IF NEW.title IS NOT NULL THEN
            INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'title',NULL,NEW.title);
        END IF;
        IF NEW.phone IS NOT NULL THEN
            INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'phone',NULL,NEW.phone);
        END IF;
        IF NEW.mobile_phone IS NOT NULL THEN
            INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'mobile_phone',NULL,NEW.mobile_phone);
        END IF;
        IF NEW.fax IS NOT NULL THEN
            INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'fax',NULL,NEW.fax);
        END IF;
    END$$
    
    
    
    CREATE TRIGGER tg_contacts_upd AFTER UPDATE ON contacts
    FOR EACH ROW 
    BEGIN
        IF NEW.date_modified <> OLD.date_modified AND (NEW.firstname <> OLD.firstname OR NEW.lastname <> OLD.lastname)THEN
            INSERT INTO audits(tableName,tablePK,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('contacts', NEW.id, 'u', NOW(), @users_id, USER(), @requesting_ip );
            SET @AID=LAST_INSERT_ID();
            IF NEW.firstname <> OLD.firstname THEN
                INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'firstname',OLD.firstname,NEW.firstname);
            END IF;
            IF NEW.lastname <> OLD.lastname THEN
                INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'lastname',OLD.lastname,NEW.lastname);
            END IF;
            IF NEW.email <> OLD.email THEN
                INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'email',OLD.email,NEW.email);
            END IF;
            IF NEW.title <> OLD.title THEN
                INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'title',OLD.title,NEW.title);
            END IF;
            IF NEW.phone <> OLD.phone THEN
                INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'phone',OLD.phone,NEW.phone);
            END IF;
            IF NEW.mobile_phone <> OLD.mobile_phone THEN
                INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'mobile_phone',OLD.mobile_phone,NEW.mobile_phone);
            END IF;
            IF NEW.fax <> OLD.fax THEN
                INSERT INTO auditFields(audits_id,fieldName,oldValue,newValue) VALUES (@AID,'fax',OLD.fax,NEW.fax);
            END IF;
        END IF;
    END$$
    
    
    
    CREATE TRIGGER tg_contacts_del AFTER DELETE ON contacts
    FOR EACH ROW 
    BEGIN
    	INSERT INTO audits(tableName,tablePK,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('contacts', OLD.id, 'd', NOW(), @users_id, USER(), @requesting_ip );
    END$$
    
    
    DELIMITER ;
    
    INSERT INTO auditTasks (task) VALUES ('i');
    INSERT INTO auditTasks (task) VALUES ('u');
    INSERT INTO auditTasks (task) VALUES ('d');
    Last edited by NotionCommotion; April 8th, 2013 at 12:12 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    the approach appears sound

    one slight problem might arise if a column has a non-NULL DEFAULT, in which case your INSERT trigger should be testing for that instead of NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    the approach appears sound
    Thanks. I was afraid I would hear a different opinion. It doesn't provide referential integrity, tableName and fieldName are not normalized (unless you consider them natural keys in which I suppose they are), and it utilizes an Entity–attribute–value model (EAV). It does, however, seem to work (haven't really thought out how deletes will be reconstructed) and is relatively easy to maintain. Is its use very common?

    In its current state, this approach will not work on tables which use a compound PK. To handle them, I am thinking of adding several more tables. Something like audits_2, audits_3, etc where audits_2 will be identical to audits but will contain an extra column for the second key, audits_3 will have two extra columns, etc. I will need to also create similar tables as auditFields for each as well. The most common use will be a many-to-many junction table which will not have a corresponding record in the auditFields_2 table. Do you see any concerns?

    Lastly, audits and auditFields will get big fast. I don't care if SELECT queries take a long time, but I am concerned about INSERT queries taking a long time. As such, would you recommend getting rid of the indexes in audits and auditFields?

    Thanks
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by NotionCommotion
    As such, would you recommend getting rid of the indexes in audits and auditFields?
    only after volume testing shows that it might make an improvement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    Originally Posted by r937
    only after volume testing shows that it might make an improvement
    Good idea.

    Do you think my plan for dealing with tables with compound keys is sound?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by NotionCommotion
    Do you think my plan for dealing with tables with compound keys is sound?
    yes, provided that you have both audits and auditsfields tables for each table you're auditing (rather than one set for all tables, as your first post suggests)

    the audit tables would be customized, each having as many columns as FK for its respective table's PK

    make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    make sense?
    Not yet

    Why not have one audits and auditsfields tables which is common to all table that I am auditing which have a single primary key, another for all tables which I am auditing which have a duel primary key, etc, etc?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by NotionCommotion
    Why not have ...
    because i said so

    no, srsly... play with this for a while, get something going in a test database, and then after you see the effect on writing your code and pulling results, decide whether you might or might not want to generalize

    to me, the deal breaker is how to handle 2-column PK audit tables, and 3-column PK audit tables, and...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    because i said so
    That is typically a good enough reason for me! I am taking your response as "maybe you will be happy, maybe you will not", and not as "you will definitely not be happy using a common table for all single PK tables, all duel PK tables, etc". If you think I am wasting my time, please advise.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by NotionCommotion
    If you think I am wasting my time, please advise.
    not necessarily

    you might be able to make it work, who knows

    all i'm saying is, i wouldn't do it myself, because it just doesn't seem right to me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    because it just doesn't seem right to me
    There are other parts that don't seem right as well.

    For instance, I made audits.tablePK as varchar(45), however, the audited table's PK will often be an integer. For this one, no harm, no foul.

    I also, however, made auditFields.oldValue and auditFields.newValue as varchar(45). This will work if the audited table's column is an integer, datetime or something else which "fits" into varchar(45), but will not work if the column is text, blob, or something else which doesn't fit (text will somewhat work, but might truncate the audited data). A workaround is to use another table similar to auditFields which references the same audits table, but uses text for oldValue/newValue. As for blobs, I don't use them, and even if I did, wouldn't use such a strategy to audit them.

    So, end result with the added table to audit text data types, I require only 3 tables to audit any number of tables which use a single PK, another 3 to audit any number of tables which use a duel PK, etc. It also minimizes the amount of stored data as it only saves columns which have been changed.

    So, I agree, it doesn't seem right, but it appears to have some advantages and it might be worth doing.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,042
    Rep Power
    535
    to me, the deal breaker is how to handle 2-column PK audit tables, and 3-column PK audit tables, and...
    I ended up using a supertype "audits" table with subtype "audits_1pk", "audits_2pk", etc tables.

    I also created separate tables to archive integers, text, and varchar(45). This makes the union in the select kind of a pain, and I am not sure how best to handle it.

    The following script has been tested and works. That being said, I am kind of out on uncharted waters, and any opinions or words of advise would be very appreciated.

    Code:
    SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
    SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
    SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
    
    CREATE SCHEMA IF NOT EXISTS `auditTest` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
    USE `auditTest` ;
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`users`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`users` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `name` VARCHAR(45) NOT NULL ,
      PRIMARY KEY (`id`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`auditTasks`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`auditTasks` (
      `task` CHAR(1) NOT NULL ,
      `name` VARCHAR(45) NOT NULL ,
      PRIMARY KEY (`task`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`audits`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`audits` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `users_id` INT UNSIGNED NULL DEFAULT 0 ,
      `dateChanged` DATETIME NOT NULL ,
      `dbUser` VARCHAR(45) NOT NULL ,
      `requesting_ip` CHAR(15) NULL DEFAULT '000.000.000.000' ,
      `tableName` VARCHAR(45) NOT NULL ,
      `task` CHAR(1) NOT NULL ,
      PRIMARY KEY (`id`) ,
      INDEX `fk_audits_users_idx` (`users_id` ASC) ,
      INDEX `fk_audits_tasks1_idx` (`task` ASC) ,
      CONSTRAINT `fk_audits_users`
        FOREIGN KEY (`users_id` )
        REFERENCES `auditTest`.`users` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_audits_tasks1`
        FOREIGN KEY (`task` )
        REFERENCES `auditTest`.`auditTasks` (`task` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`audits_1pk`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`audits_1pk` (
      `audits_id` INT UNSIGNED NOT NULL ,
      `pk1` VARCHAR(45) NOT NULL ,
      PRIMARY KEY (`audits_id`) ,
      CONSTRAINT `fk_audits_1pk_audits1`
        FOREIGN KEY (`audits_id` )
        REFERENCES `auditTest`.`audits` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`audits_2pk`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`audits_2pk` (
      `audits_id` INT UNSIGNED NOT NULL ,
      `pk1` VARCHAR(45) NOT NULL ,
      `pk2` VARCHAR(45) NOT NULL ,
      PRIMARY KEY (`audits_id`) ,
      CONSTRAINT `fk_audits_2pk_audits1`
        FOREIGN KEY (`audits_id` )
        REFERENCES `auditTest`.`audits` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`audits_3pk`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`audits_3pk` (
      `audits_id` INT UNSIGNED NOT NULL ,
      `pk1` VARCHAR(45) NOT NULL ,
      `pk2` VARCHAR(45) NOT NULL ,
      `pk3` VARCHAR(45) NOT NULL ,
      PRIMARY KEY (`audits_id`) ,
      CONSTRAINT `fk_audits_3pk_audits1`
        FOREIGN KEY (`audits_id` )
        REFERENCES `auditTest`.`audits` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`audit_int`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`audit_int` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `audits_id` INT UNSIGNED NOT NULL ,
      `columnName` VARCHAR(45) NOT NULL ,
      `oldValue` INT NULL ,
      `newValue` INT NULL ,
      PRIMARY KEY (`id`) ,
      INDEX `fk_audit_int_audits1_idx` (`audits_id` ASC) ,
      CONSTRAINT `fk_audit_int_audits1`
        FOREIGN KEY (`audits_id` )
        REFERENCES `auditTest`.`audits` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`audit_text`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`audit_text` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `audits_id` INT UNSIGNED NOT NULL ,
      `columnName` VARCHAR(45) NOT NULL ,
      `oldValue` TEXT NULL ,
      `newValue` TEXT NULL ,
      PRIMARY KEY (`id`) ,
      INDEX `fk_audit_int_audits1_idx` (`audits_id` ASC) ,
      CONSTRAINT `fk_audit_int_audits10`
        FOREIGN KEY (`audits_id` )
        REFERENCES `auditTest`.`audits` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`audit_var_45`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`audit_var_45` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `audits_id` INT UNSIGNED NOT NULL ,
      `columnName` VARCHAR(45) NOT NULL ,
      `oldValue` VARCHAR(45) NULL ,
      `newValue` VARCHAR(45) NULL ,
      PRIMARY KEY (`id`) ,
      INDEX `fk_audit_int_audits1_idx` (`audits_id` ASC) ,
      CONSTRAINT `fk_audit_int_audits100`
        FOREIGN KEY (`audits_id` )
        REFERENCES `auditTest`.`audits` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`students`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`students` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `name` VARCHAR(45) NOT NULL ,
      `ssn` CHAR(10) NOT NULL ,
      `notes` TEXT NULL ,
      `nickname` VARCHAR(45) NULL ,
      PRIMARY KEY (`id`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`courses`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`courses` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `name` VARCHAR(45) NOT NULL ,
      `course_number` VARCHAR(45) NOT NULL ,
      PRIMARY KEY (`id`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `auditTest`.`courses_has_students`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `auditTest`.`courses_has_students` (
      `courses_id` INT UNSIGNED NOT NULL ,
      `students_id` INT UNSIGNED NOT NULL ,
      `other_int` INT NULL ,
      PRIMARY KEY (`courses_id`, `students_id`) ,
      INDEX `fk_courses_has_students_students1_idx` (`students_id` ASC) ,
      INDEX `fk_courses_has_students_courses1_idx` (`courses_id` ASC) ,
      CONSTRAINT `fk_courses_has_students_courses1`
        FOREIGN KEY (`courses_id` )
        REFERENCES `auditTest`.`courses` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_courses_has_students_students1`
        FOREIGN KEY (`students_id` )
        REFERENCES `auditTest`.`students` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    USE `auditTest`;
    
    DELIMITER $$
    USE `auditTest`$$
    
    
    CREATE TRIGGER tg_students_ins AFTER INSERT ON students
    FOR EACH ROW 
    BEGIN
        INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('students', 'i', NOW(), @users_id, USER(), @requesting_ip );
        SET @AID=LAST_INSERT_ID();
        INSERT INTO audits_1pk(audits_id,pk1) VALUES (@AID,NEW.id );
        IF NEW.name IS NOT NULL THEN
            INSERT INTO audit_var_45(audits_id,columnName,oldValue,newValue) VALUES (@AID,'name',NULL,NEW.name);
        END IF;
        IF NEW.ssn IS NOT NULL THEN
            INSERT INTO audit_var_45(audits_id,columnName,oldValue,newValue) VALUES (@AID,'ssn',NULL,NEW.ssn);
        END IF;
        IF NEW.notes IS NOT NULL THEN
            INSERT INTO audit_text(audits_id,columnName,oldValue,newValue) VALUES (@AID,'notes',NULL,NEW.notes);
        END IF;
    END$$
    
    USE `auditTest`$$
    
    
    CREATE TRIGGER tg_students_upd AFTER UPDATE ON students
    FOR EACH ROW 
    BEGIN
        IF NOT NEW.name <=> OLD.name OR NOT NEW.ssn <=> OLD.ssn OR NOT NEW.notes <=> OLD.notes THEN
            INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('students', 'u', NOW(), @users_id, USER(), @requesting_ip );
            SET @AID=LAST_INSERT_ID();
            INSERT INTO audits_1pk(audits_id,pk1) VALUES (@AID,NEW.id );
            IF NOT NEW.name <=> OLD.name THEN
                INSERT INTO audit_var_45(audits_id,columnName,oldValue,newValue) VALUES (@AID,'name',OLD.name,NEW.name);
            END IF;
            IF NOT NEW.ssn <=> OLD.ssn THEN
                INSERT INTO audit_var_45(audits_id,columnName,oldValue,newValue) VALUES (@AID,'ssn',OLD.ssn,NEW.ssn);
            END IF;
            IF NOT NEW.notes <=> OLD.notes THEN
                INSERT INTO audit_text(audits_id,columnName,oldValue,newValue) VALUES (@AID,'notes',OLD.notes,NEW.notes);
            END IF;
        END IF;
    END$$
    
    USE `auditTest`$$
    
    
    CREATE TRIGGER tg_students_del AFTER DELETE ON students
    FOR EACH ROW 
    BEGIN
        INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('students', 'd', NOW(), @users_id, USER(), @requesting_ip );
        SET @AID=LAST_INSERT_ID();
        INSERT INTO audits_1pk(audits_id,pk1) VALUES (@AID,OLD.id );
    END$$
    
    
    DELIMITER ;
    
    DELIMITER $$
    USE `auditTest`$$
    
    
    CREATE TRIGGER tg_courses_has_students_ins AFTER INSERT ON courses_has_students
    FOR EACH ROW 
    BEGIN
        INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('courses_has_students', 'i', NOW(), @users_id, USER(), @requesting_ip );
        SET @AID=LAST_INSERT_ID();
        INSERT INTO audits_2pk(audits_id,pk1,pk2) VALUES (@AID,NEW.courses_id,NEW.students_id);
        IF NEW.other_int IS NOT NULL THEN
            INSERT INTO audit_int(audits_id,columnName,oldValue,newValue) VALUES (@AID,'other_int',NULL,NEW.other_int);
        END IF;
    END$$
    
    USE `auditTest`$$
    
    
    CREATE TRIGGER tg_courses_has_students_upt AFTER UPDATE ON courses_has_students
    FOR EACH ROW 
    BEGIN
        IF NOT NEW.other_int <=> OLD.other_int THEN
            INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('courses_has_students', 'u', NOW(), @users_id, USER(), @requesting_ip );
            SET @AID=LAST_INSERT_ID();
            INSERT INTO audits_2pk(audits_id,pk1,pk2) VALUES (@AID,NEW.courses_id,NEW.students_id);
            IF NOT NEW.other_int <=> OLD.other_int THEN
                INSERT INTO audit_int(audits_id,columnName,oldValue,newValue) VALUES (@AID,'other_int',OLD.other_int,NEW.other_int);
            END IF;
        END IF;
    END$$
    
    USE `auditTest`$$
    
    
    CREATE TRIGGER tg_courses_has_students_del AFTER DELETE ON courses_has_students
    FOR EACH ROW 
    BEGIN
        INSERT INTO audits(tableName,task,dateChanged,users_id,dbUser,requesting_ip) VALUES ('courses_has_students', 'd', NOW(), @users_id, USER(), @requesting_ip );
        SET @AID=LAST_INSERT_ID();
        INSERT INTO audits_2pk(audits_id,pk1,pk2) VALUES (@AID,OLD.courses_id,OLD.students_id);
    END$$
    
    
    DELIMITER ;
    
    
    SET SQL_MODE=@OLD_SQL_MODE;
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
    
    
    
    -- Required by audit tables
    
    INSERT INTO auditTasks (task,name) VALUES ('i','insert');
    INSERT INTO auditTasks (task,name) VALUES ('u','update');
    INSERT INTO auditTasks (task,name) VALUES ('d','delete');
    
    -- Get some business records
    
    INSERT INTO users (id,name) VALUES (0,'John Doe');
    INSERT INTO users (id,name) VALUES (0,'Jane Doe');
    
    INSERT INTO courses (id,name,course_number) VALUES (0,'Math','123abc');
    INSERT INTO courses (id,name,course_number) VALUES (0,'English','123abc');
    
    -- Set by PHP application
    SET @requesting_ip='555.555.555.555';
    SET @users_id=1;
    
    -- Start normal routines
    INSERT INTO students (id,name,ssn,notes,nickname) VALUES (0,'Billy Bob','555-55-5555',NULL,'Bebop');
    UPDATE students SET name='Bill Bob',notes='Some notes' WHERE id=1;
    INSERT INTO courses_has_students (courses_id,students_id) VALUES (1,1);
    INSERT INTO courses_has_students (courses_id,students_id) VALUES (2,1);
    DELETE FROM courses_has_students WHERE courses_id=1 AND students_id=1;
    DELETE FROM courses_has_students WHERE courses_id=2 AND students_id=1;
    DELETE FROM students WHERE id=1;
    
    -- Audit database
    
    SELECT u.name AS user_name, at.name AS task, a.dateChanged, a.tableName, af.columnName, af.oldValue AS oldValue_text, af.newValue AS newValue_text, null AS oldValue_val_45, null AS newValue_val_45
    FROM audits AS a
    INNER JOIN users AS u ON u.id=a.users_id
    INNER JOIN auditTasks AS at ON at.task=a.task
    INNER JOIN audits_1pk AS apk ON apk.audits_id=a.id
    LEFT OUTER JOIN audit_text AS af ON af.audits_id=a.id
    WHERE a.tableName='students' AND apk.pk1=1
    UNION
    SELECT u.name AS user_name, at.name AS task, a.dateChanged, a.tableName, af.columnName, null AS oldValue_text, null AS newValue_text, af.oldValue AS oldValue_val_45, af.newValue AS newValue_val_45
    FROM audits AS a
    INNER JOIN users AS u ON u.id=a.users_id
    INNER JOIN auditTasks AS at ON at.task=a.task
    INNER JOIN audits_1pk AS apk ON apk.audits_id=a.id
    LEFT OUTER JOIN audit_var_45 AS af ON af.audits_id=a.id
    WHERE a.tableName='students' AND apk.pk1=1
    ORDER BY dateChanged ASC;
    
    SELECT u.name AS user_name, at.name AS task, a.dateChanged, a.tableName, af.columnName, af.oldValue, af.newValue
    FROM audits AS a
    INNER JOIN users AS u ON u.id=a.users_id
    INNER JOIN auditTasks AS at ON at.task=a.task
    INNER JOIN audits_2pk AS apk ON apk.audits_id=a.id
    LEFT OUTER JOIN audit_int AS af ON af.audits_id=a.id
    WHERE a.tableName='courses_has_students' AND apk.pk1=1 AND apk.pk2=1
    ORDER BY dateChanged ASC;

IMN logo majestic logo threadwatch logo seochat tools logo