mysql-03 触发器的学习和应用

1）基本语法

DELIMITER $$

CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `mypos2`.`kk` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `mypos2`.`<Table Name>`
FOR EACH ROW BEGIN

END$$

DELIMITER ;

2）常用触发器

a.Insert/Update触发器

CREATE
TRIGGER `sy_cj` BEFORE INSERT ON `syjlb`
FOR EACH ROW SET new.yhje=new.ysje-new.ssje;

FOR EACH ROW SET new.mcsx=PysxCx(new.hpmc);

USE `mypos2`$$

DROP TRIGGER /*!50032 IF EXISTS */ `update_hpkcb_hyxxb_delete_symxb_before_delete_syjlb`$$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `update_hpkcb_hyxxb_delete_symxb_before_delete_syjlb` BEFORE DELETE ON `syjlb`
FOR EACH ROW BEGIN
IF old.gkid>10000 THEN
UPDATE hyxxb SET knye=knye+old.ssje,kyjf=kyjf-FLOOR(old.ssje)
WHERE hyxxb.hyid=old.gkid;
END IF;
UPDATE hpkcb b1,(SELECT hpid,SUM(xssl) AS xssl FROM symxb WHERE symxb.syid=old.syid GROUP BY Hpid) b2 SET b1.kcsl=b1.kcsl+b2.xssl WHERE b1.hpid=b2.hpid;
DELETE FROM symxb WHERE symxb.syid=old.syid;
END;
$$

DELIMITER ;

b.delete触发器

FOR EACH ROW UPDATE class SET classSize=classSize-1 WHERE classId=old.classId;

FOR EACH ROW UPDATE chapter SET questionCount=questionCount-1 WHERE chapterId=old.chapterId;