CREATE TRIGGER my_trigger --定义一个触发器my―trigger BEFORE INSERT or UPDATE of TID,TNAME on TEACHERS FOR each row WHEN(new.TNAME='David') --这一部分是触发条件 DECLARE --下面这一部分是触发体 teacher_id TEACHERS.TID%TYPE; INSERT_EXIST_TEACHER EXCEPTION; BEGIN SELECT TID INTO teacher_id FROM TEACHERS WHERE TNAME=new.TNAME; RAISE INSERT_EXIST_TEACHER; EXCEPTION --异常处理也可用在这里 WHEN INSERT_EXIST_TEACHER THEN INSERT INTO ERROR(TID,ERR) VALUES(teacher_id,'the teacher already exists!'); END my triqqer;
3.执行触发器 自动执行
复制代码 代码如下:
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;