触发器---9

NO1.触发器：

;
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句


FOR EACH ROW表示任何一条记录满足触发事件
use view

例子：
select * from  information_schema.VIEWS;
CREATE TABLE trigger_time(
exec_time TIME
);

CREATE TRIGGER dept_trig1  BEFORE INSERT ON  index1 FOR EACH ROW
INSERT INTO trigger_time VALUES(NOW())


NO2.创建多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END

//不能使用;
DELIMITER &&结束




CREATE TRIGGER dept_trig1  AFTER DELETE ON  index1 FOR EACH ROW
BEGIN
INSERT INTO trigger_time VALUES('12:09:01');
INSERT INTO trigger_time VALUES('12:09:02')
END


NO3.查看触发器
SHOW TRIGGERS;
SELECT * FROM information_schema.`TRIGGERS`;

NO4.表中触发器执行的顺序
BEFORE触发器(INSERT\UPDATE\FELETE)、AFTER触发器
例子：

 CREATE TABLE trigger_test(
  id INT PRIMARY KEY  AUTO_INCREAMENT,
  info VARCHAR(20)
    
);

CREATE TRIGGER  before_insert BEFORE INSERT
ON department FOR EACH ROW
INSERT INTO trigger_test VALUES(NULL,"before_insert ");



CREATE TRIGGER  after_insert AFTER INSERT
ON department FOR EACH ROW
INSERT INTO trigger_test VALUES(NULL,"after_insert ");

N04.删除触发器
DROP TRIGGER  before_insert ;




--------------------------;
----------------------------;

实例：

CREATE TRIGGER  before_insert BEFORE INSERT
ON department FOR EACH ROW
INSERT INTO trigger_test VALUES(NULL,"before_insert",now());


CREATE TRIGGER  after_update AFTER UPDATE
ON department FOR EACH ROW
INSERT INTO trigger_test VALUES(NULL,"after_update ",now());



CREATE TRIGGER  after_delete AFTER DELETE
ON department FOR EACH ROW
INSERT INTO trigger_test VALUES(NULL,"after_delete ",now());

//
//可以插入另外一张表中
select * from information_schema.triggers where TRIGGER_NAME='after_update'