Oracle触发器(附带SQL语句完整过程及示例)

触发器：定义

    触发器(trigger)设置某个条件,如果SQL语句执行了该条件则触发某个或者某些固定的SQL

触发器:类型

     1.DML触发器 2.替代触发器 3.系统事件触发器

对数据表进行DML语句操作(insert、update、delete)时所触发的触发器,DML触发器有两种

    1.行级触发器 2.表级触发器；行级触发器会对数据库表中的受影响的每一行 触发一次触发器代码；语句级触发器则只触发一次，与语句所影响到的行数无关；触发器中使用before触发则在事件发生之前执行触发器代码；触发器中使用after触发器则在触发事件发生之后执行触发器代码；如果删除重建触发器中的某个表必须要检查触发器是否可用；触发器自动同步Demo请参考https://blog.51cto.com/zhanky/2355285

    1.DML行级触发器

----示例语法
----先了解这个语法它长什么样
create or replace trigger modify_stu
after update
on CSDN_STUDENT
for each row
begin
    dbms_output.put_line(:old.classid||:old.classname);
end;

----示例Demo(跟着示例执行一边就基本上就可以大概明白触发器怎么使用)
----新建SQL表
create table CSDN_STUDENT   ---创建CSDN_STUDENT表
(
  id        NUMBER(19), --id
  stu_no    VARCHAR2(20), --学号
  stu_name  VARCHAR2(32), --姓名
  stu_age   NUMBER,  --年龄
  stu_major VARCHAR2(32) --专业
)

create table CSDN_STU_LOG   ---创建CSDN_STU_LOG表，用于记录对CSDN_STUDENT表的操作日志
(
  log_id     NUMBER,  --日志id
  log_action VARCHAR2(100),  --操作名称
  log_date   DATE,  --操作时间
  log_message   VARCHAR2(32) --
)

----定义行级触发器
----modify_stu 触发器名字  
----insert update delete 条件
----for each row 表示行级触发器 
----:new 引用插入操作的某列值,如 :new.stu_name
----.:old 引用删除操作的某列值,如 :old.stu_name
----CSDN_STUDENT 关联的表
----update of stu_name 对应:new.stu_name和:old.stu_name人名
----如果不想显示人名或者其他信息将第二行和带:new和:old的改成字符'XXX'
------after insert or delete or update
------示例 insert into CSDN_STU_LOG values(1,'insert',sysdate,'XXX');

create or replace trigger modify_stu
after insert or delete or update of stu_name 
on CSDN_STUDENT
for each row
  begin 
    if inserting then
      insert into CSDN_STU_LOG values(1,'insert',sysdate,:new.stu_name);
    elsif deleting then
       insert into CSDN_STU_LOG values(2,'delete',sysdate,:old.stu_name);
    elsif updating then
      insert into CSDN_STU_LOG values(3,'update_old',sysdate,:old.stu_name);
      insert into CSDN_STU_LOG values(4,'update_new',sysdate,:new.stu_name);
     end if;
end;

 ----触发器捕捉到SQL操作进行对应的处理 将日志保存至 CSDN_STU_LOG表中

insert into CSDN_STUDENT values(1,'NO2','李四',21,'数学系');  --插入数据
update CSDN_STUDENT set stu_age=19 where stu_name='李四';    --修改年龄
delete CSDN_STUDENT where stu_name='李四';                   --删除数据

2.DML表级触发器

----表级触发器不加 for each row
create or replace trigger modify_stus
before insert or update or delete on CSDN_STUDENT
begin
   if deleting then
     raise_application_error(-20001,'该表不允许删除数据');
   elsif updating then
     raise_application_error(-20002,'该表不允许修改数据');
    elsif inserting then
     raise_application_error(-20003,'该表不允许插入数据');
    end if;
end;

再次进行添加、删除、更改操作会出现提示框禁止操作(在 IF 里面也可以SQL语句或者自定义的提示信息)

insert into CSDN_STUDENT values(1,'NO2','李四',21,'数学系');  --插入数据
update CSDN_STUDENT set stu_age=19 where stu_name='李四';    --修改年龄
delete CSDN_STUDENT where stu_name='李四';                   --删除数据

禁止删除表触发器特殊形况下使用可以提高安全性

----关键字drop 也可以替换成别的
CREATE OR REPLACE TRIGGER CSDN_ddl_deny_lee
BEFORE  drop OR truncate on database    
begin
    raise_application_error(num => -20000,msg => '主机:' || SYS_CONTEXT('USERENV', 'HOST')||chr(13) || 'IP:' ||SYS_CONTEXT('USERENV', 'IP_ADDRESS')||'试图删除' || ora_dict_obj_name() || '表');
end;