Mysql-学习笔记（==》触发器 十一）

-------触发器--------

 

USE db;
SELECT FROM sss;

CREATE TABLE sssbak LIKE sss;
SHOW CREATE TABLE sss;
SHOW CREATE TABLE sssbak;
CREATE TABLE `sssbak` (
`sid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`sname` VARCHAR(10) NOT NULL,
`sscore` TINYINT(3) UNSIGNED DEFAULT NULL,
`saddress` VARCHAR(30) DEFAULT NULL,
`stel` BIGINT(20) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8

CREATE TABLE `sss` (
`sid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`sname` VARCHAR(10) NOT NULL,
`sscore` TINYINT(3) UNSIGNED DEFAULT NULL,
`saddress` VARCHAR(30) DEFAULT NULL,
`stel` BIGINT(20) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=MYISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

sss 学生信息表
sssbak 学生补考表

TRUNCATE sss;
TRUNCATE sssbak;-- 清除表的内容
SELECT FROM sss;
SELECT FROM sssbak;
-- 建立触发器 建立的对象为 new
DELIMITER
CREATE TRIGGER tt1 AFTER INSERT ON sss FOR EACH ROW
BEGIN
DECLARE ss TINYINT;
SET ss=NEW.sscore;
IF ss60 THEN
INSERT INTO sssbak VALUES(NEW.sid,NEW.sname,NEW.sscore,NEW.saddress,NEW.stel);
END IF;
END
DELIMITER;

删除表sss中的元素 在sssbak中相应的元素也删除 删除的对象为 OLD
DELIMITER
CREATE TRIGGER tt2 AFTER DELETE ON sss FOR EACH ROW
BEGIN
DELETE FROM sssbak WHERE sid=OLD.sid;
END
DELIMITER;

修改 OLD 修改之前 NEW 修改之后
DELIMITER
CREATE TRIGGER tt3 AFTER UPDATE ON sss FOR EACH ROW
BEGIN
DECLARE newss TINYINT;
DECLARE oldss TINYINT;
SET newss=NEW.sscore;
SET oldss=old.sscore;
IF newss=60 THEN
DELETE FROM sssbak WHERE sid =NEW.sid;
ELSE -- sss新的分数小于60的时候
REPLACE INTO sssbak VALUES(NEW.sid,NEW.sname,NEW.sscore,NEW.saddress,NEW.stel);
END IF;
END
DELIMITER;

 

-- 使用触发器，实质性相关的操作时，自动触发的
INSERT INTO sss VALUES(NULL,'张三',30,'北京',18290438501);
INSERT INTO sss VALUES(NULL,'李四',90,'郑州',18290438501);

 

-- 删除触发器
DROP TRIGGER tt1;

-- 查看db数据库中所有的触发器
SHOW TRIGGERS FROM `db`;