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

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`;