数据库触发器

触发器(trigger)：是一种特殊的存储过程，可以用来对表实施复杂的完整性约束，保持数据的一致性。当触发器所保护的数据发生改变时，触发器会自动被激活，并执行触发器中所定义的相关操作，从而保证对数据的不完整性约束或不正确的修改。
一、触发器简介
触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。因此触发器不需要人为的去调用，也不能调用。然后，触发器的触发条件其实在你定义的时候就已经设定好了。这里面需要说明一下，触发器可以分为语句级触发器和行级触发器。详细的介绍可以参考网上的资料，简单的说就是语句级的触发器可以在某些语句执行前或执行后被触发。而行级触发器则是在定义的了触发的表中的行数据改变时就会被触发一次。
具体举例：
1、 在一个表中定义的语句级的触发器，当这个表被删除时，程序就会自动执行触发器里面定义的操作过程。这个就是删除表的操作就是触发器执行的条件了。
2、 在一个表中定义了行级的触发器，那当这个表中一行数据发生变化的时候，比如删除了一行记录，那触发器也会被自动执行了。
二、触发器语法
触发器的语法：

create [or replace] trigger触发器名触发时间触发事件on 表名[for each row]beginpl/sql语句End;

其中：
or replace：加与不加的区别：如果数据库中之前存在同名的触发器，加上or replace，在 编译时会替换之前存在的触发器，不加的话编译会导致编译报错。
触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称， 没有实质的用途
触发时间：指明触发器何时执行，该值可取
before：表示在数据库动作之前触发器执行
after：表示在数据库动作之后触发器执行
触发事件：指明哪些数据库动作会触发此触发器
insert：数据库插入会触发此触发器
update：数据库修改会触发此触发器
delete：数据库删除会触发此触发器
表 名：数据库触发器所在的表
for each row：对表的要执行操作的每一行（条）数据触发器都要执行一次。如果没有加for each row，则只对整个表的执行操作的数据的其中一行（条）执行一次。

:new— 触发器执行过程中触发表作操作的当前行的新纪录
:old— 触发器执行过程中触发表作操作的当前行的旧纪录
:nextval：来获下一个取序列值，然后再进行其他操作

我们在使用:new或者:old的操作时需要注意以下问题

1、 必须是行级触发器，因为:new或者:old是当前触发表操作的当前行的新数据或者旧数据，所以必须在行级触发器中才能使用。否则编译时会出现错误。

2、 当触发器被不同事件触发时，需要注意：
insert触发操作 只有 :new
delete触发操作 只有 :old
update触发操作 都有。
如果违反上述操作中随意使用:new或者:old不会在编译时报错误但是会发现使用这两个关键字无效。

3、 使用触发器时如果要修改:new的值需要注意：
只有行级前触发器才能修改:new的值，而行级后触发器不可以。
因为行级前触发器是在本行DML操作之前，所以才能修改:new的值。从逻辑上说，也只有在这时修改才有意义。如果行级后触发器要试图修改:new的值会报变异错误。从逻辑上说后触发器的执行已经在本行DML操作，以后了，再修改:new的值已经不会对数据产生影响了。

4、 当在触发条件中使用:new或者:old的时候注意，只能直接写 old或者new，不能带符号”:”否则会出错。
5.一个表最多创建12个触发器

举例：
首先是建表，造数据

create table tab_1(       id varchar(11) primary key,       name varchar(10),       password varchar(10))create table insert_tab(       id varchar(10) null primary key,       name varchar(10) null ,       password varchar(10));create table delete_tab(       id varchar(10) null primary key,       name varchar(10) null ,       password varchar(10));create table log_tab(       id varchar(10) null primary key,       log varchar(100))insert into TAB_1 (ID, NAME, PASSWORD)values ('1', '小米', '123');insert into TAB_1 (ID, NAME, PASSWORD)values ('2', '小明', '452');insert into TAB_1 (ID, NAME, PASSWORD)values ('3', '小华', '145');insert into TAB_1 (ID, NAME, PASSWORD)values ('4', '小花', '18423');insert into TAB_1 (ID, NAME, PASSWORD)values ('5', '小涛', '46452');insert into TAB_1 (ID, NAME, PASSWORD)values ('6', '小飞', '1745');

select * from tab_1查询结果：

    Id  name  password1   3   小华  1452   4   小花  184233   5   小涛  464524   6   小飞  17455   1   小米  1236   2   小明  452

select * from delete_tab查询结果为空。

创建deletename 触发器：

create or replace trigger deletename --触发器名字before  --触发的时间delete --触发的事件on tab_1 --表名for each rowbegin    insert into delete_tab (id,name,password)values(:old.id,:old.name,:old.password);end;

执行:delete from tab_1 t where t.id=’6’ (注意提交事务)

再次查询select * from tab_1查询结果：

    Id   name   password1   3    小华     1452   4    小花     184233   5    小涛     464524   1    小米     1235   2    小明     452

再查询select * from delete_tab结果：

    Id   name  password1   6    小飞     1745

创建insertname 触发器：

create or replace trigger insertname --触发器名字before  --触发的时间insert --触发的事件on tab_1 --表名for each row begin    insert into insert_tab (id,name,password)values(:new.id,:new.name,:new.password);end;

select * from insert_tab查询结果为空。

执行：

insert into TAB_1 (ID, NAME, PASSWORD)values ('6', '小飞', '1745');

再次查询select * from insert_tab查询结果：

        Id      name    password    1   6       小飞      1745

创建updatename 触发器：

create or replace trigger updatename --触发器名字before  --触发的时间update --触发的事件on tab_1 --表名for each rowbegin    insert into log_tab (id,log)    values    (:old.id,'更新之前：'||:old.name||'--更新之后：'||:new.name||'更新之前：'||:old.password||'--更新之后：'||:new.password);end;

先执行：

update tab_1 t set t.password ='d1z6fd13' where t.id in('1','3','6');

执行：select * from log_tab 查询结果：

    Id      log1   1       更新之前：小米--更新之后：小米  更新之前：123--更新之后：d1z6fd132   3       更新之前：小华--更新之后：小华  更新之前：145--更新之后：d1z6fd133   6       更新之前：小飞--更新之后：小飞  更新之前：1745--更新之后：d1z6fd13

再次执行：select * from tab_1查询结果：

    Id      name        password1   6       小飞          d1z6fd132   4       小花          184233   5       小涛          464524   1       小米          d1z6fd135   3       小华          d1z6fd136   2       小明          452

例如触发器能实现如下功能

（1)、下面的触发器在操作表tab_1之前触发：

create or replace trigger auth_secure --触发器名字before  --触发的时间insert or update or delete --触发的事件on tab_1 --表名for each rowbegin    IF(to_char(sysdate,'DY')='星期一') THEN       RAISE_APPLICATION_ERROR(-20600,'不能在周一修改表tab_1');    END IF;end;

执行delete from tab_1 t where t.id in (‘2’,’5’);
报：

（2)、使用触发器实现序号自增
创建一个测试表：

create table userinfo(  id number(11) primary key,  username varchar(50),  password varchar(50));

创建一个序列：

create sequence userifo_seqincrement by 1 --每次增加1start with 1 --从1开始计数nomaxvalue --不设置最大值nocycle --一直累加不循环nocache --设置不缓存

创建一个触发器：

create or replace trigger userifo_triggerbefore insert --触发事件on userinfo  --触发的表名for each row --对于操作的每一行都进行触发begin    select userifo_seq.nextval into :new.id from dual;    --:NEW表示新插入的那条记录END;end;

向表插入数据:

insert into userinfo(username,password)values('admin','admin');insert into userinfo(username,password)values('测试','123');commit;

执行select * from userinfo查询表结果：

        Id      username        password1       1       admin           admin2       2       测试              123

（3)、当用户对userinfo表执行DML语句时，将相关信息记录到日志表
–创建记录测试表

create table userinfo_log(       l_user varchar(20),       l_type varchar(20),       l_date varchar(60))

然后先清空表userinfo再创建触发器：
–创建触发器

create or replace trigger uf_logafter insert or delete or update --触发事件是after操作之后   触发事件insert delete updateon userinfodeclarev_type userinfo_log.l_type%type;begin       if inserting then          --insert触发          v_type:='insert';          dbms_output.put_line('记录已成功插入，并记录到日志');          elsif deleting then            --delete触发            v_type:='delet';            dbms_output.put_line('记录成功删除,并记录到日子');            elsif updating then           --update触发           v_type:='updat';           dbms_output.put_line('记录成功更新,并记录到日子');       end if;         insert into userinfo_log         values         (user,v_type,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));         --USER表示当前用户名end;

–下面我们来分别执行DML语句
第一步先测试填加两条数据：

insert into userinfo(username,password)values('admin','admin');insert into userinfo(username,password)values('测试','123');

执行select * from userinfo查询结果：

1       5       admin       admin2       6       测试           123

执行select * from userinfo_log 查询：

1   ZYSMS   insert  2017-09-19 13:26:072   ZYSMS   insert  2017-09-19 13:26:07

第二步删除一条数据：

delete from userinfo t where t.id=6

–然后查看效果
select * from userinfo_log
运行结果如下：

        l_user      l_type      l_date1       ZYSMS       insert      2017-09-19 13:26:072       ZYSMS       insert      2017-09-19 13:26:073       ZYSMS       delet       2017-09-19 13:31:45

再次执行：

update  userinfo t set t.password='admin123' where t.username='admin';

查询：select * from userinfo

    Id  username    password1   5   admin       admin123

执行：select * from userinfo_log查询结果：

            l_user      l_type      l_date1           ZYSMS       insert      2017-09-19 13:26:072           ZYSMS       insert      2017-09-19 13:26:073           ZYSMS       delet       2017-09-19 13:31:454           ZYSMS       updat       2017-09-19 13:32:40

（4)、创建触发器，利用视图插入数据

–创建表

CREATE TABLE tab1 (       id NUMBER(4) PRIMARY KEY,       name VARCHAR2(20),       age NUMBER(2)       );CREATE TABLE tab2 (       id NUMBER(4),       tel VARCHAR2(15),       email VARCHAR2(30)       );

–插入数据

INSERT INTO tab1 VALUES(01,'小明',22);INSERT INTO tab1 VALUES(02,'小米',20);INSERT INTO tab2 VALUES(01,'18865930264','[e-mail]');INSERT INTO tab2 VALUES(02,'18865947289','[e-mail]');

–创建视图连接两张表

CREATE OR REPLACE VIEW tab_view AS SELECT tab1.id,name,age,tel,email FROM tab1,tab2 WHERE tab1.id = tab2.id;

–创建触发器

CREATE OR REPLACE TRIGGER TAB_TRIGGER INSTEAD OF INSERT ON TAB_VIEWBEGIN INSERT INTO TAB1 (ID, NAME,AGE) VALUES (:NEW.ID,:NEW.NAME,:NEW.AGE); INSERT INTO TAB2 (ID,TEL,EMAIL) VALUES (:NEW.ID,:NEW.TEL,:NEW.EMAIL);END;/

–现在就可以利用视图插入数据

INSERT INTO tab_view VALUES(03,'小华',23,'18884921869','[e-mail]');

–查询
执行：SELECT * FROM tab_view;

    Id  name    age       tel                   email1   1   小明      22      18865930264     [e-mail]2   2   小米      20      18865947289     [e-mail]3   3   小华      23      18884921869     [e-mail]

执行：SELECT * FROM tab1;

    Id  name    age1   1   小明      222   2   小米      203   3   小华      23

执行：SELECT * FROM tab2;

    Id  tel             email1   1   18865930264     [e-mail]2   2   18865947289     [e-mail]3   3   18884921869     [e-mail]

这里触发器是使用Oracle数据库做的测试!