Farid Ahmadian / DevOps

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

---

BY: Farid Ahmadian
TAG: mysql, trigger
DATE: 2013-06-08 10:36:58

---

Farid Ahmadian / DevOps [ TXT ]

With many thanks and best wishes for dear Pejman Moghadam, someone who taught me alot in linux and life :)