MySQL Trigger
=================

********************************************************************************
### Create Trigger 
    
    CREATE
        [DEFINER = { user | CURRENT_USER }]
        TRIGGER trigger_name trigger_time trigger_event
        ON tbl_name FOR EACH ROW trigger_body
    END  

    CREATE TRIGGER `menu_loger` BEFORE DELETE ON `menu` FOR EACH ROW INSERT INTO menu_log
    VALUES (
    0,OLD.menu_id, OLD.menu_name, OLD.menu_url, OLD.menu_section, OLD.parent
    );

    CREATE TRIGGER `menu_loger_insert` AFTER INSERT ON `menu`
    FOR EACH
    ROW INSERT INTO menu_log
    VALUES (
    0, NEW.menu_id, NEW.menu_name, NEW.menu_url, NEW.menu_section, NEW.parent
    );

    CREATE TRIGGER `menu_loger_update` AFTER UPDATE ON `menu`
    FOR EACH ROW
    BEGIN
        IF OLD.parent IS NULL AND NEW.parent NOT NULL THEN
            INSERT INTO menu_log VALUES ( 0, NEW.menu_id, NEW.menu_name, NEW.menu_url, NEW.menu_section, NEW.parent);
        END IF;   
    END


    CREATE TRIGGER `menu_loger_update` AFTER UPDATE ON `menu` FOR EACH ROW IF OLD.parent IS NULL AND NEW.parent IS NOT NULL THEN INSERT INTO menu_log
    VALUES (
    0, NEW.menu_id, NEW.menu_name, NEW.menu_url, NEW.menu_section, NEW.parent
    );

    END IF ;


    CREATE DEFINER = `root`@`localhost` TRIGGER `menu_loger_update` AFTER UPDATE ON `menu` FOR EACH ROW BEGIN IF OLD.parent IS NULL AND NEW.parent IS NOT NULL THEN INSERT INTO menu_log
    VALUES (
    0, NEW.menu_id, NEW.menu_name, NEW.menu_url, NEW.menu_section, NEW.parent
    );

    END IF ;

********************************************************************************
### Adder Trigger 

    ALTER TABLE users ADD download INT default 0;
    delimiter |
    CREATE
            TRIGGER `add_download` BEFORE UPDATE
            ON `users` FOR EACH ROW
            IF ( NEW.download != 0 ) THEN
                    set NEW.download = OLD.download + NEW.download;
            END IF;
    |
    delimiter ;
********************************************************************************
### Diff Date Time Trigger 
    DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

    DROP TRIGGER IF EXISTS diff_datetime;
    CREATE
            TRIGGER `diff_datetime` BEFORE UPDATE
            ON `problem_reports` FOR EACH ROW
                    set new.pr_duration = TIME_TO_SEC( TIMEDIFF( new.pr_endtime, new.pr_starttime))/60;
    delimiter ;
    
********************************************************************************
### Bookmarks
 
[Create Trigger](http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html)  
 

********************************************************************************
_BY: Farid Ahmadian_  
_TAG: mysql, trigger_  
_DATE: 2013-06-08 10:36:58_