Oracle各类触发器的使用实例

最近在读Oracle触发器，试着编写了几个触发器，还算实用，放在自己的数据库中。现将其贴出来，并简单讲讲触发器的定义和使用。语言浅显，绝对适合新手。

触发器，简单理解，就是当用户进行某项操作时，会触发的一段程序体，类似于炉石中的奥秘。我们可以利用这个特点来做一些很有趣的事。

一、DML触发器

这类触发器是指在进行insert、update或delete操作时触发的程序体。如果你想在用户对数据进行操作时，记录或限制其操作，就可以用DML触发器。

举例来说，我想统计我的网站用户的注册、注销或者更新个人信息等情况，我们就可以写如下一个触发器，每当有用户进行上述操作时，触发器会自动执行，并在log日志表中存储这些信息。

上代码：

create or replace trigger user_log 
before delete or insert or update
on users
for each row
declare
id number;
begin
select count(*)+1 into id from ts_log_users t;
if inserting then insert into ts_log_users t values (id,sysdate,(select count(*)+1 from ts_log_users t where t.class='create'),'create',:new.username);
elsif deleting then insert into ts_log_users t values (id,sysdate,(select count(*)-1 from ts_log_users where class='create'),'delete',:new.username);
else insert into ts_log_users t values (id,sysdate,(select t.count from ts_log_users t where create_date=(select max(t.create_date) from ts_log_users t)),'update',:new.username);
end if;
end;

第一行定义触发器名字为user_log，create or replace意思是建立或如果存在一样名字的触发器则覆盖。

第二行关键字可以选择before和after，即操作前和操作后。比如我想查看用户登录的ip情况，这时我们需要在用户成功登录后触发触发器，所以需写after。而这里要对用户的增删改敏感，所以选择before。后面是操作定义，即什么样的操作会触发触发器。

第三行on后面接表名，即当用户对这个表进行操作时，触发触发器。另外，操作为update时，可写成before uodate of 列名 on 表名，来使限制更加具体化。

第四行将触发器定义为行级触发器，即每当一行数据发生变化时，就触发一次触发器。比如我一下更新了三个用户的信息，那么log日志表中就会生成三条数据。如果不写这句话，则触发器只会在上述操作时，触发一次，形成一条数据。

第五行声明变量，用于存储和操作一些可用数据。

从begin到end为程序主体，读起来也很容易理解，主要有两个操作，一是将log日志的数据量+1作为数据id赋值给之前定义的id变量。赋值用into完成。

第二个操作是一个if操作，分别完成了对增删改不同情况的处理。处理主要为想log日志表插入数据，如果是新建用户的话，则向log日志表插入数据id（上面得到的）、系统时间、目前用户数量（即count下log日志中是create操作的数据）、操作类别（此处为create）以及操作的用户名（:new.的意思就是新修改的数据，对比:old.使用，这两个字段在更新操作时会经常用到。例如将更新前的数据插入到另一表中，则用:old.表示，新插入的数据则用:new.操作）。后两个是对delete和update的操作，类似上面不在赘述。

比如我现在向user表插入一条数据，在网站端即新注册了一个用户liuge：

insert into users values('liuge','password')

这时便会触发上面的触发器，在log日志表形成一条记录：

如果我更新一下liuge用户的信息呢，这时就会再出现一个操作记录：

用户没有增加，因此count不变。如果我删除这条记录即注销这个用户，这时log日志插入这样一条数据：

由于用户被删除，因此:new.找不到用户名，也就没有插入进数据。

你可以利用上面的log日志表统计当前用户数、每天新注册人数以及流失用户数等信息，很实用。

二、替代触发器

这类触发器仅仅应用在视图上。我举个例子就明白这个触发器的用处了。比如说，我提供给初级管理员老张一个视图，只显示用户的基本注册信息，如用户名、性别、民族等，而隐藏了用户密码、身高、体重这些私密信息（这些信息存在表中，老张看不到，因为我没给他看表的权限）。但有一天有个走后门的找老张想把自己的信息加到我数据库中，于是老张就对视图进行插入操作。可是问题来了，当插入一条关系户数据到表中时，我们需要其用户密码、身高体重等（设置了不为空），而老张看不到因此插不进去，因此报错无法操作。所以这时我们就可以写一个替代触发器，即如果老张对视图操作时，后面的表会自动插入一些随机密码等，以完成老张的视图操作。触发器定义格式类似于上面，这里不再赘述（因为我没用视图，所以没写）。

三、系统触发器

这类触发器是在进行系统操作如startup、drop、alter、logon等时才会触发的。

下面我写一个，因为我的数据库给了好多人使用，老张、老王等，而他们经常在别的时间和地点登录数据库，我想看看这些登录情况，因此我写一个登录的触发器：

create or replace trigger oracle_user_log 
after logon
on database
declare
id number;
begin
select count(*)+1 into id from ts_o_log_users t;
insert into ts_o_log_users values (id,sys.login_user,sysdate,ora_client_ip_address);
end;

这样就可以查看用户登录数据库的信息了：

四、其它

我们还可利用触发器中的条件来限制某些操作，例如限制某个表的增删改，或者限制在晚上什么时间不允许插入数据（表现在网站即晚上不允许注册新用户）等等。

这是一个防止删除users表的触发器（未测试）：

create or replace trigger nodrop_users
before drop on schema
begin
if upper(sys.dictionary_obj_name)='USERS' then raise_application_error(-20005,'不能删除此表！');
end if;
end;

通过修改if条件来完成各种限制操作，可以实现很多功能。至于那些sys.和ora.方法名，可以上网自行搜索，找到合适自己的。