Oracle DDL类型触发器

Oracle DDL类型触发器主要是对于Oracle数据库的DDL操作触发的触发器,主要包括create、drop、alter等DDL事件,经常利用DDL类型触发器记录DDL操作记录或者限定对某个对象进行DDL操作。也可以根据对应DDL操作做对应的操作。


Oracle DDL类型触发器的语法结构

DDL类型触发器的编写语法如下:

create [ or replace] trigger tr_name(触发器名)
before|after
ddl_event|database_event
on SCHEMA(数据库对象)|DATABASE(数据库)
[follows tr_name1(其它触发器名)]
[when 条件] 
declare
--声明部分
begin
--触发器内容部分
end;

语法解析:

1、or replace :存在同名的触发器就覆盖保存。

2、trigger:创建触发器的关键词。

3、before|after表示是选择的触发器是在进行DDL操作之前触发还是之后触发。

4、ddl_event:表示的DDL事件,有create(创建)、alter(修改)、drop(删除)等常用DDL操作。

5、SCHEMA|DATABASE:表示触发器是作用在数据库对象上还是数据库上。

6、follows :表示触发器的顺序是跟在哪个之后。

7、when 表示触发器触发的附带条件,比如时间。


下面通过一个案例来解析Oracle DDL类型触发器的写法:

案例1、利用Oracle DDL类型触发器给学生信息表(stuinfo)做一个禁止删除、修改表结构的触发器,代码如下:

create or replace trigger tr_stuinfo_ddl
  before alter or drop  on schema
begin
  --禁止对学生信息表进行删除和修改操作
  if dictionary_obj_name = 'STUINFO' THEN
    --修改表结构
    if sysevent = 'ALTER' then
      --抛出错误
      raise_application_error(-20001,
                              '禁止学生信息表stuinfo进行alter操作!');
    end if;
  
    --删除表结构
    if sysevent = 'DROP' then
      --抛出错误
      raise_application_error(-20001,
                              '禁止学生信息表stuinfo进行drop操作!');
    end if;
  END IF;
end;

执行完案例1触发器,我们通过修改学生信息表(stuinfo)测试一下触发器的效果,代码如下:

--修改表结构
alter table STUINFO modify stuaddress VARCHAR2(200);
--删除表结构
drop table stuinfo;

结果如下:

Oracle DDL类型触发器


案例2、利用DDL类型触发器的创建一个数据库级别的触发器,记录用户登录数据库的记录信息。再次我们需要设计一个登录记录表,来保存用户登录信息,代码如下:

-- Create table
create table LOGIN_LOG
(
  logid     VARCHAR2(20),
  loginuser VARCHAR2(100),
  logindate DATE
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table LOGIN_LOG
  is '登录日志表';
-- Add comments to the columns 
comment on column LOGIN_LOG.logid
  is '日志id';
comment on column LOGIN_LOG.loginuser
  is '登录用户名';
comment on column LOGIN_LOG.logindate
  is '登入时间';

建立DDL类型触发器(数据库级):

create or replace trigger tr_stuinfo_ddl_login
  after  logon--数据库系统事件
  on database
begin
  --插入登录日志表
insert into login_log
  (LOGID, LOGINUSER, LOGINDATE)
values
  (pk_oplog_id.nextval,sys.login_user, sysdate);
end;

建立好案例2触发器,我们通过登录数据库,然后查看下登录日志表,查看一下效果,结果如下:

Oracle DDL类型触发器