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 ;