oracle创建触发器-1

触发器的定义
触发器是一个操作命令集合，触发器被创建后，当指定表发生指定操作（insert, update, delete）时Oracle会自动执行触发器中定义的语句

--触发器基本语法

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE|AFTER}

{DELETE|INSERT|UPDATE} [OF column [,column]…]}

[OR{DELETE|INSERT|UPDATE [OF column [,column]…]}]…

ON table_name

[FOR EACH ROW][WHEN condition]

BEGIN

    …pl/sql block...

END [trigger_name]

其中：

trigger_name:触发器名：触发器对象的名称。

{BEFORE|AFTER}：触发时间。指明触发器何时执行，该值可取：

        BEFORE：表示在数据库动作之前触发器执行;

        AFTER：表示在数据库动作之后触发器执行。

{ INSERT|UPDATE|DELETE}：触发事件。指明哪些数据库动作会触发此触发器：

        INSERT：数据库插入会触发此触发器;

        UPDATE：数据库修改会触发此触发器;

        DELETE：数据库删除会触发此触发器。

TABLE：表名。数据库触发器所在的表。

[FOR EACH ROW]：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

--触发器示例（触发器的创建不要和其他语句一起执行）

--首先我们要创建表bookcard,这个表的主键是no_id
create table bookcard
(
  card_id     varchar2(32) not null,
  customer_id varchar2(32) not null,
  no_id       varchar2(32) primary key not null
) ;
--创建触发器tri_bookcard
create or replace trigger tri_bookcard 
  before insert on bookcard
for each row
begin
  if length(:new.customer_id)>10 then --这里的new是因为要改变新插入的数据
    :new.customer_id:=substr(:new.customer_id,-9,9)||'z';
    end if;
end tri_bookcard;

--将触发器置失效（创建后的触发器默认是生效的）
alter trigger tri_bookcard disable;
insert into bookcard (card_id, customer_id, no_id) values ('01836022483', '01836022483', 'bk210100522');
commit ;
--将触发器置生效
alter trigger tri_bookcard enable;
insert into bookcard (card_id, customer_id, no_id) values ('02292750966', '02292750966', 'bk210201276');
insert into bookcard (card_id, customer_id, no_id) values ('02953318714', '02953318714', 'bk210201278');
commit ;
--查询结果，发现触发器生效时，过长的customer_id会被截取
select t.card_id ,t.customer_id , length(t.customer_id) as "len" ,t.no_id from bookcard t ;

--创建触发器 tri_t_count，作用是当表bookcard插入数据时，count加1,时间修改为当前时间

create table t_count
(
table_name varchar2(64),
pcount number(8),
lasttime date default to_date('1990-12-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
) ;

--创建约束，t_count表的table_name唯一
alter table t_count add constraint table_name_unique unique (table_name);

--创建触发器,当bookcard表有插入操作时如果t_count表没有bookcard的数据时，则添加数据，如果有则count+1且把时间置为当前时间

create or replace trigger tri_t_count
  after insert on bookcard
  for each row
    declare
    sno number ;
begin
      select count(1) into sno from t_count where table_name = 'bookcard'  ;
      if sno = 0 then
        insert into t_count (table_name,pcount) values  ('bookcard',1);
       else 
  update t_count
  set pcount = pcount + 1,lasttime = sysdate ; 
  end if ;
end;

insert into bookcard (card_id, customer_id, no_id) values ('02953318725', '02953318725', 'bk210201280');
commit ;
select t.card_id ,t.customer_id , length(t.customer_id) as "len" ,t.no_id from bookcard t ;
select * from t_count ;
--创建触发器audit_trigger，记录表bookcard的增删更新操作并记录被操作数据的no_id和时间
--创建表audit_table
create table audit_table
(
operation_type  varchar2(64),
operation_time date ,
pdata varchar2(64)
) ;
--创建触发器
create or replace trigger audit_trigger
after insert or update or delete on bookcard
for each row
begin
    if inserting then
        insert into audit_table (operation_type, operation_time,  pdata)
        values ('insert', sysdate,:new.no_id);
    elsif updating then
        insert into audit_table (operation_type, operation_time,  pdata)
        values ('update', sysdate,:new.no_id);
    elsif deleting then
        insert into audit_table (operation_type, operation_time,  pdata)
        values ('delete', sysdate,:old.no_id);
    end if;
end;
--查询触发结果
delete from bookcard where no_id = 'bk210201280' ;
insert into bookcard (card_id, customer_id, no_id) values ('02953318725', '02953318725', 'bk210201280');
commit ;

select * from bookcard ;
select * from t_count ;
select * from audit_table ;
--查看某个表所有的触发器
select * from all_triggers where lower(table_name)='bookcard';
--查看触发器具体内容
select * from all_source where lower(type)='trigger' and lower(name) in (select lower(t1.trigger_name) from all_triggers t1 where lower(table_name)='bookcard');
--删除触发器
/*
drop trigger trigger_name  ; 
*/ --trigger_name 为触发器名称
--循环删除触发器
declare
   tri_name varchar2(100);  --声明游标即触发器名
 begin
    for cur in (select trigger_name from all_triggers where lower(table_name)='bookcard') loop     
          --将执行过程中获取的表名赋值给table_name
          tri_name := cur.trigger_name; 
          --拼接执行的sql语句
          execute immediate 'drop trigger ' || tri_name; 
end loop;     
commit;
end; 
--这样就删除了所有的触发器
select * from all_triggers where lower(table_name)='bookcard';

--循环删除表
 begin
    for tabname in (select table_name from user_tables where lower(table_name) in ('bookcard','t_count','audit_table')) loop     
          --拼接执行的sql语句
          execute immediate 'drop table  ' || tabname.table_name; 
end loop;     
commit;
end ; 

select * from user_tables ;