Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Working with triggers in mySql Database

    • 0
    • 2
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 343
    Comment on it

    A trigger is SQL code which runs just before or just after an INSERT, UPDATE or DELETE event occurs on a particular database table.

    Creating a Trigger:

    Consider we have two tables:

    CREATE TABLE `blog` (
        `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='Blog posts';
    
    
    CREATE TABLE `audit` (
        `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
        `blog_id` mediumint(8) unsigned NOT NULL,
        `changetype` enum('NEW','EDIT','DELETE') NOT NULL,
        CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    

    We need to do is:

    When a record is UPDATED in the blog table, we want to add a new entry into the audit table containing the blog ID and a action type i.e a simple text- "Edit".

    Each trigger requires:

    1) A unique name. I prefer to use a name which describes the table and action, e.g. blog_before_insert or blog_after_update.
    
    2) The table which triggers the event. A single trigger can only monitor a single table.
    
    3) 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.
    
    4) 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.
    

    Syntax:

    CREATE
        TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
        ON `database`.`table`
        FOR EACH ROW BEGIN
            -- trigger body
        END;
    

    Implementation:

    DELIMITER $$
    
    CREATE
        TRIGGER `blog_after_update` AFTER UPDATE 
        ON `blog` 
        FOR EACH ROW BEGIN
    
            INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, 'Edit');
    
        END$$
    

 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: