mysql 创建触发器(trigger)

语法:

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt

例:

#table1: test1
CREATE TABLE test1(a1 INT);
 
#table2: test2
CREATE TABLE test2(a2 INT);
 
#table2: test3
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
 
#table3: test4
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);


#创建触发器

DELIMITER | 
CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END
|
DELIMITER ;

#当test1插入数据就会触发,
#当test1插入一点新数据,test2也会同样插入一条,
#test3如果和插入的数据一样但删除那条数据, test4的a4和数据一样,则b4会自加1
#这个例子是mysql手册上的,很简单也包括最常用的例子