MySQL触发器

MySQL触发器简介

触发器百科

语法

DELIMITER ；
CREATE TRIGGER <databaseName>.<triggerName>
< [ BEFORE | AFTER ] >
< [ INSERT | UPDATE | DELETE ] >
ON <databaseName>.<triggerName>
FOR EACH ROW
BEGIN
  do something
END ；

CREATE TABLE product(
id INT(10) NOT NULL UNIQUE PRIMARY KEY,
name varchar(20) NOT null,
FUNCTION varchar(50),
company VARCHAR(50) NOT null,
address VARCHAR(50)
);

CREATE TABLE operate(
op_id INT(10) NOT NULL UNIQUE PRIMARY KEY auto_increment,
op_name varchar(20) NOT null,
op_tiem TIME NOT NULL
);

CREATE TRIGGER Tproduct_bf_insert before INSERT 
ON product FOR each row
INSERT operate(`op_name`,`op_time`) VALUES('insert',curtime());

CREATE TRIGGER Tproduct_af_update1 after update 
ON chufaqi_weiwanling.product FOR each row
INSERT chufaqi_weiwanling.operate(`op_name`,`op_time`) VALUES('update',curtime());


CREATE TRIGGER Tproduct_af_update after update 
ON product FOR each row
INSERT operate(`op_name`,`op_time`) VALUES('update',curtime());

CREATE TRIGGER Tproduct_af_delete after delete 
ON product FOR each row
INSERT INTO operate(`op_name`,`op_time`) VALUES('delete',curtime());

INSERT INTO product values(11,'abc','治疗感冒','北京abc制药厂','北京市昌平区');
UPDATE product SET address="北京市海淀区" WHERE id=1;

DELETE FROM `product` WHERE id=1;

SELECT * FROM operate;


DROP TRIGGER Tproduct_af_update;