触发器(对表或视图执行DML操作时触发),INSTEAD OF触发器(只定义在视图上,替代实际的操作语句),系统触发器(对数据库系统进行操作时触发,如DDL语句、启动或关闭数据库等)
CREATE TRIGGER my_trigger --定义一个触发器my—trigger
BEFORE INSERT or UPDATE of TID,TN
amE on T
eachERS
FOR each row
WHEN(new.TNAME='David') --这一部分是触发条件
DECLARE --下面这一部分是触发体
teacher_id TEACHERS.TID%TY
PE;
INSERT_EXIST_TEACHER EXCEPTION;
BE
gin SELECT TID INTO teacher_id
From TEACHERS
WHERE TNAME=new.TNAME;
R
aiSE INSERT_EXIST_TEACHER;
EXCEPTION --异常处理也可用在这里
WHEN INSERT_EXIST_TEACHER THEN
INSERT INTO ERROR(TID,ERR)
VALUES(teacher_id,'the teacher already exists!');
END my tri
QQer;
CREATE TRIGGER my_trigger1
AFTER INSERT or UPDATE or DELETE on TEACHERS
FOR each row;
DECLARE
info CHAR(10);
BEGIN
IF inserting THEN --如果进行插入操作
info:='INSERT';
ELSIF updating THEN --如果进行修改操作
info:='Update';
ELSE--如果进行删除操作
info:='Delete';
END IF;
INSERT INTO
SQL_INFO VALUES(info); --记录这次操作信息
END my_trigger1;