Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Trigger in MySQL

    • 0
    • 2
    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 961
    Comment on it

    Here, I have created a small database for a book store application. In which two tables are required:-

  • book:- Stores a unique book ID, the title, content, and a deleted flag.
  • audit_book:- Stores a set of historical changes with a record ID, the book post ID, the change type (NEW, EDIT or DELETE) and the date/time of that change.

    The below SQL creates the book & indexes the deleted column:-

    CREATE TABLE `book` (
        `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
        `title` text,
        `content` text,
        `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`),
        KEY `ix_deleted` (`deleted`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='book posts';
    

    The below SQL creates the audit_book table. All columns are indexed and a foreign key is defined for audit_book.book_id which references book.id. Therefore, when we physically DELETE a book entry, its full audit_book history is also removed.

    CREATE TABLE `audit_book` (
        `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
        `book_id` mediumint(8) unsigned NOT NULL,
        `changetype` enum('NEW','EDIT','DELETE') NOT NULL,
        `changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `ix_book_id` (`book_id`),
        KEY `ix_changetype` (`changetype`),
        KEY `ix_changetime` (`changetime`),
        CONSTRAINT `FK_audit_book_book_id` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    

    Creating a Trigger:- We now require two triggers. When a record is inserted into the book table, we need to add a new entry into the audit_book table containing the book ID and a type of NEW or DELETE if it was deleted immediately. When a value is updated in the book table, we need to add a new entry into the audit_book table containing the book ID and a type of EDIT or DELETE if the deleted flag is set.


    Each trigger requires:-A unique name and I prefered to use a name which describes the table and action, e.g. book_before_insert or book_after_update. The table which triggers the event. A single trigger can only monitor a single table. When the trigger occurs. This can either be BEFORE or AFTER an INSERT, UPDATE or DELETE. A BEFORE trigger must be used if you need to modify incoming data. An AFTER trigger must be used if you want to reference the new/changed record as a foreign key for a record in another table.


    The trigger body:- A set of SQL commands to run. Note that you can refer to columns in the subject table using OLD.col_name (the previous value) or NEW.col_name (the new value). The value for NEW.col_name can be changed in BEFORE INSERT and UPDATE triggers.


    Our AFTER INSERT trigger can now be defined. It determines whether the deleted flag is set, sets the @changetype variable accordingly, and inserts a new record into the audit_book table:

    DELIMITER $$
    CREATE
        TRIGGER `book_after_insert` AFTER INSERT 
        ON `book` 
        FOR EACH ROW BEGIN
    
            IF NEW.deleted THEN
                SET @changetype = 'DELETE';
            ELSE
                SET @changetype = 'NEW';
            END IF;
    
            INSERT INTO audit_book (book_id, changetype) VALUES (NEW.id, @changetype);
    
        END$$
    
    DELIMITER ;
    


    The AFTER UPDATE trigger is almost identical:

    DELIMITER $$

    CREATE TRIGGER book_after_update AFTER UPDATE ON book FOR EACH ROW BEGIN

        IF NEW.deleted THEN
            SET @changetype = 'DELETE';
        ELSE
            SET @changetype = 'EDIT';
        END IF;
    
        INSERT INTO audit_book (book_id, changetype) VALUES (NEW.id, @changetype);
    
    END$$
    

    DELIMITER ;

Comment on it

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: