Trigger in MySQL

@bhagwan.khichar

Follow Recommendations Offline Message

about 9 years ago

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 ;