一、创建触发器的基本语法：
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 触发器语句
触发事件包括：INSERT、UPDATE、DELETE
二、正确案例演示
如果想在sql文件中直接创建触发器，下面为一个完整的sql文件创建（含触发器的创建）示例：
-
use mysql
-
drop database if exists myTest;
-
create database myTest default charset=gb2312;
-
use myTest;
-
create table Stud
-
(
-
sno int(8),
-
sname varchar(64),
-
ssex varchar(64),
-
sage int(4) ,
-
sdep varchar(128)
-
);
-
create table Scsc
-
(
-
sno int(8),
-
cno int(4),
-
grade int(4)
-
);
-
insert into Stud(sno,sname,ssex,sage,sdep) values(2006126001,'lwj','female',19,'com scen');
-
insert into Stud(sno,sname,ssex,sage,sdep) values(2006126002,'fj','male',20,'com scen');
-
insert into Scsc(sno,cno,grade) values(2006126001,1001,70);
-
insert into Scsc(sno,cno,grade) values(2006126002,1002,85);
-
-- 注意下面mysql的触发器的用法:
-
-- 当其放在sql语句中与数据库/表一起创建的时候
-
-- 必须重定义结束符为$$(代替;),否则创建出错。
-
-- 且创建触发器的过程要放在quit的前面，这样
-
-- 不至于造成常规的sql语句无法执行问题。
-
delimiter $$
-
drop trigger if exists delTrigger$$
-
create trigger delTrigger after delete on Stud for each row
-
begin
-
declare oldsno char(10);
-
set oldsno=old.sno;
-
delete from Scsc where sno=oldsno;
-
end$$
-
drop trigger if exists updateTgr$$
-
create trigger updateTgr after update on Stud for each row
-
begin
-
declare newsno char(10);
-
declare oldsno char(10);
-
set newsno = NEW.sno;
-
set oldsno = OLD.sno;
-
if newsno<>oldsno then
-
update Scsc set sno=newsno where sno=oldsno;
-
end if;
-
end$$
- quit
图1 创建成功
图2 更新触发测试
三、易错问题
而下面的写法虽然语法上没有问题，但是却无法直接写到sql文件中的！原因不明！
-
------- Error usage
-
drop trigger if exists delTrigger
-
create trigger delTrigger after delete on Stud for each row
-
begin
-
declare oldsno char(10);
-
set oldsno=old.sno;
-
delete from Scsc where sno=oldsno;
-
end
-
drop trigger if exists updateTgr
-
create trigger updateTgr after update on Stud for each row
-
begin
-
declare newsno char(10);
-
declare oldsno char(10);
-
set newsno = NEW.sno;
-
set oldsno = OLD.sno;
-
if newsno<>oldsno then
-
update Scsc set sno=newsno where sno=oldsno;
-
end if;
-
end
- ------- Error usage
图3
参考网址：