数据库触发器

最新推荐文章于 2022-08-07 16:22:00 发布

--LIANG--

最新推荐文章于 2022-08-07 16:22:00 发布

阅读量1.3k

点赞数 2

分类专栏： oracle 文章标签：数据库 oracle

本文链接：https://blog.csdn.net/zl570932980/article/details/78033853

版权

oracle 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

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

create [or replace] trigger触发器名
触发时间
触发事件
on 表名
[for each row]
begin
pl/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  password
1   3   小华  145
2   4   小花  18423
3   5   小涛  46452
4   6   小飞  1745
5   1   小米  123
6   2   小明  452

select * from delete_tab查询结果为空。

创建deletename 触发器：

create or replace trigger deletename --触发器名字
before  --触发的时间
delete --触发的事件
on tab_1 --表名
for each row
begin
    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   password
1   3    小华     145
2   4    小花     18423
3   5    小涛     46452
4   1    小米     123
5   2    小明     452

再查询select * from delete_tab结果：

    Id   name  password
1   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 row
begin
    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      log
1   1       更新之前：小米--更新之后：小米  更新之前：123--更新之后：d1z6fd13
2   3       更新之前：小华--更新之后：小华  更新之前：145--更新之后：d1z6fd13
3   6       更新之前：小飞--更新之后：小飞  更新之前：1745--更新之后：d1z6fd13

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

    Id      name        password
1   6       小飞          d1z6fd13
2   4       小花          18423
3   5       小涛          46452
4   1       小米          d1z6fd13
5   3       小华          d1z6fd13
6   2       小明          452

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

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

create or replace trigger auth_secure --触发器名字
before  --触发的时间
insert or update or delete --触发的事件
on tab_1 --表名
for each row
begin
    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_seq
increment by 1 --每次增加1
start with 1 --从1开始计数
nomaxvalue --不设置最大值
nocycle --一直累加不循环
nocache --设置不缓存

创建一个触发器：

create or replace trigger userifo_trigger
before 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        password
1       1       admin           admin
2       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_log
after insert or delete or update --触发事件是after操作之后   触发事件insert delete update
on userinfo
declare
v_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       admin
2       6       测试           123

执行select * from userinfo_log 查询：

1   ZYSMS   insert  2017-09-19 13:26:07
2   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_date
1       ZYSMS       insert      2017-09-19 13:26:07
2       ZYSMS       insert      2017-09-19 13:26:07
3       ZYSMS       delet       2017-09-19 13:31:45

再次执行：

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

查询：select * from userinfo

    Id  username    password
1   5   admin       admin123

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

            l_user      l_type      l_date
1           ZYSMS       insert      2017-09-19 13:26:07
2           ZYSMS       insert      2017-09-19 13:26:07
3           ZYSMS       delet       2017-09-19 13:31:45
4           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','1482586@163.com');
INSERT INTO tab2 VALUES(02,'18865947289','1458969@126.com');

–创建视图连接两张表

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_VIEW
BEGIN
 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','48998956@126.com');

–查询
执行：SELECT * FROM tab_view;

    Id  name    age       tel                   email
1   1   小明      22      18865930264     1482586@163.com
2   2   小米      20      18865947289     1458969@126.com
3   3   小华      23      18884921869     48998956@126.com

执行：SELECT * FROM tab1;

    Id  name    age
1   1   小明      22
2   2   小米      20
3   3   小华      23

执行：SELECT * FROM tab2;

    Id  tel             email
1   1   18865930264     1482586@163.com
2   2   18865947289     1458969@126.com
3   3   18884921869     48998956@126.com