导读:就爱阅读网友为大家分享了多篇关于“oracle触发器”资料,内容精辟独到,非常感谢网友的分享,希望从中能找到对您有所帮助的内容。
相关资料一 : Oracle触发器
-创建触发器(行级触发器)create or replace trigger tri_update_emp_bakafter updateon emp_bakfor each row --每更新一行 就触发一次begin--Oracle里面 对触发器 也提供了特殊的对象 :NEW :OLD 来访问 更新前后的数据dbms_output.put_line(更新后 || :NEW.sal);dbms_output.put_line(更新前 || :OLD.sal);if updating thenend if;if insertingend;--创建触发器(行级触发器)(前置)create or replace trigger tri3_update_emp_bakbefore updateon emp_bakfor each row --每更新一行 就触发一次begin--oracle 里面 对触发器 也提供了特殊的对象 :NEW :OLD 来访问 更新前后的数据dbms_output.put_line(更新后 || :NEW.sal);dbms_output.put_line(更新前 || :OLD.sal);end;select * from emp_bakupdate emp_bak set sal = 1000 where empno in (7788)--创建触发器(表级触发器)--//表级别触发器里面 不允许使用 :NEW :OLD 变量create or replace trigger tri2_update_emp_bakafter updateon emp_bakbegin-- dbms_output.put_line(更新后 || :NEW.sal);--dbms_output.put_line(更新前 || :OLD.sal);end;--创建触发器(行级触发器)create or replace trigger tri4_update_emp_bakafter update of salon emp_bakfor each row --每更新一行 就触发一次begin--oracle 里面 对触发器 也提供了特殊的对象 :NEW :OLD 来访问 更新前后的数据dbms_output.put_line(更新后 || :NEW.sal);dbms_output.put_line(更新前 || :OLD.sal);end;create table userinfo(userid number(4) PRimary key,username varchar2(20))create table addrinfo(addrid number(4) primary key,addname varchar2(20),userid number(4) references userinfo(userid))insert into userinfo values (1,李四)insert into addrinfo values (1,湖北武汉,1)select * from addrinfodelete from userinfo where userid =1--级联删除create or replace trigger tri_userinfo_deletebefore deleteon userinfofor each rowbegindelete from addrinfo where userid = :OLD.userid;-- insert intoend;create table dept_bak as select * from deptcreate or replace view myviewasselect a.empno,a.ename,a.sal,b.dnamefrom emp a inner join dept bon a.deptno = b.deptnoselect * from myviewupdate myview set sal = 1000,dname=aaaaa where empno = 7499--替代触发器 用在视图上面 只能是行级的--替代触发器 特点是 真正的操作已经 变成了一个动作而已 功能由触发器来完成create or replace trigger tri_myviewinstead of updateon myviewbegindbms_output.put_line(删除操作执行了。。。);update emp set sal = :NEW.sal where empno = :OLD.empno;update dept set dname = :NEW.dname where dname = :OLD.dname;--delete from emp where empno = :OLD.empno;end;select * from emp;select * from dept;update emp_bak set ename =aaaa,sal = 10000 where empno in (7788)--系统包-- 产生随机数**** **** **** ****--dbms_random.value 0-1 之间的随机小数--dbms_random.random 随机整数select dbms_random.value from dual;create or replace procedure proc_cardno(mycardno out varchar2)astempcard varchar2(50);cardno varchar2(19);begintempcard:= dbms_random.value;cardno:=substr(tempcard,2,4)|| ||substr(tempcard,6,4)|| ||substr(tempcard,10,4)|| ||substr(tempcard,14,4);mycardno:=cardno;-- dbms_output.put_line(tempcard);-- dbms_output.put_line(cardno);end;--使用UTL_FILE包读写文件create directory MY_DIR as d:emp;create directory MY_DIR2 as d:emp2;declare--定义文件对象myfile utl_file.file_type;--定义变量 用来存储每读出一行的数据linestr varchar2(200);begin--打开文件myfile:= utl_file.fopen(MY_DIR,oracle.log,r);--进行读取(循环)looputl_file.get_line(myfile,linestr);dbms_output.put_line(linestr);end loop;exception
扩展:oracle触发器实例 / 触发器 / oracle触发器语法
Oracle触发器中select into报错no_data_found异常处理
红色部分为对查询不到数据异常的处理
create or replace trigger TIG_MONITOR_ALARM
after insert on t_monitor_real_minute
for each row www.2cto.com
declare
-- 标准值
standvalue number;
--报警实况表id
liveid number;
begin
--
--触发器功能:监测实况数据表,对比监测数据是否超标,超标数据则记录入超标报警表中
--
standvalue:=-1;
liveid:=-1;
select nvl(t.bzz,-1) into standvalue from t_monitor_factor t where t.jcdbm=:new.STATION_ID and t.jcxmbm=:new.INFECTANT_ID;
--如果录入检测项目数据大于标准值,则入库报警信息表中
if standvalue>-1 then
if :new.M_VALUE>standvalue then
--将数据录入报警历史数据中
insert into t_alarm_history(id,jcdbm,jcxmbm,mvalue,mtime)
values www.2cto.com (SEQ_ALARM_HISTORY.NEXTVAL,:new.STATION_ID,:new.INFECTANT_ID,:new.M_VALUE,:new.M_TIME);
--异常判断,如果查询不到数据
begin
select r.id into liveid from t_alarm_real r where r.jcdbm=:new.STATION_ID and r.jcxmbm=:new.INFECTANT_ID;
--查询不到数据
EXCEPTION
WHEN no_data_found THEN
--不存在则录入新的报警实况
insert into t_alarm_history(id,jcdbm,jcxmbm,mvalue,mtime)
values(SEQ_ALARM_REAL.NEXTVAL,:new.STATION_ID,:new.INFECTANT_ID,:new.M_VALUE,:new.M_TIME);
end;
--报警实况中是否已存在该监测点的该因子报警信息
if liveid>-1 then
update t_alarm_real r1 set r1.mvalue=:new.M_VALUE,r1.mtime=:new.M_TIME,r1.status=0 where r1.id=liveid;
else
--不存在则录入新的报警实况
insert into t_alarm_history(id,jcdbm,jcxmbm,mvalue,mtime)
values www.2cto.com (SEQ_ALARM_REAL.NEXTVAL,:new.STATION_ID,:new.INFECTANT_ID,:new.M_VALUE,:new.M_TIME);
end if;
end if;
end if;
EXCEPTION
WHEN no_data_found THEN
null;
end TIG_MONITOR_ALARM;
扩展:触发器 select into / oracle 触发器 select / oracle select into
触发器是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,下文中就为大家详细讲解Oracle触发器。
创建触发器(行级触发器)
create or replace trigger tri_update_emp_bak
after update
on emp_bak
for each row 每更新一行 就触发一次
begin
Oracle 里面 对触发器 也提供了特殊的对象 :NEW :OLD 来访问 更新前后的数据
dbms_output.put_line('更新后' || :NEW.sal);
dbms_output.put_line('更新前' || :OLD.sal);
if updating then
end if;
if inserting
end;
创建触发器(行级触发器)(前置)
create or replace trigger tri3_update_emp_bak
before update
on emp_bak
for each row 每更新一行 就触发一次
begin
oracle 里面 对触发器 也提供了特殊的对象 :NEW :OLD 来访问 更新前后的数据
dbms_output.put_line('更新后' || :NEW.sal);
dbms_output.put_line('更新前' || :OLD.sal);
end;
select * from emp_bak
update emp_bak set sal = 1000 where empno in (7788)
创建触发器(表级触发器)
//表级别触发器里面 不允许使用 :NEW :OLD 变量
create or replace trigger tri2_update_emp_bak
after update
on emp_bak
begin
dbms_output.put_line('更新后' || :NEW.sal);
dbms_output.put_line('更新前' || :OLD.sal);
end;
创建触发器(行级触发器)
create or replace trigger tri4_update_emp_bak
after update of sal
on emp_bak
for each row 每更新一行 就触发一次
begin
oracle 里面 对触发器 也提供了特殊的对象 :NEW :OLD 来访问 更新前后的数据
dbms_output.put_line('更新后' || :NEW.sal);
dbms_output.put_line('更新前' || :OLD.sal);
end;
create table userinfo
(
userid number(4) PRimary key,
username varchar2(20)
)
create table addrinfo
(
addrid number(4) primary key,
addname varchar2(20),
userid number(4) references userinfo(userid)
)
insert into userinfo values (1,'李四')
insert into addrinfo values (1,'湖北武汉',1)
select * from addrinfo
delete from userinfo where userid =1
级联删除
create or replace trigger tri_userinfo_delete
before delete
on userinfo
for each row
begin
delete from addrinfo where userid = :OLD.userid;
insert into
end;
create table dept_bak as select * from dept
create or replace view myview
as
select a.empno,a.ename,a.sal,b.dname
from emp a inner join dept b
update myview set sal = 1000,dname='aaaaa' where empno = 7499
替代触发器 用在视图上面 只能是行级的
替代触发器 特点是 真正的操作已经 变成了一个动作而已 功能由触发器来完成
create or replace trigger tri_myview
instead of update
on myview
begin
dbms_output.put_line('删除操作执行了。。。');
update emp set sal = :NEW.sal where empno = :OLD.empno;
update dept set dname = :NEW.dname where dname = :OLD.dname;
delete from emp where empno = :OLD.empno;
end;
select * from emp;
select * from dept;
update emp_bak set ename ='aaaa',sal = 10000 where empno in (7788)
系统包
产生随机数
**** **** **** ****
dbms_random.value 0-1 之间的随机小数
dbms_random.random 随机整数
select dbms_random.value from dual;
create or replace procedure proc_cardno(mycardno out varchar2)
as
tempcard varchar2(50);
cardno varchar2(19);
begin
tempcard:= dbms_random.value;
cardno:=substr(tempcard,2,4)||' '||substr(tempcard,6,4)||' '||substr(tempcard,10,4)||' '||substr(tempcard,14,4);
mycardno:=cardno;
dbms_output.put_line(tempcard);
dbms_output.put_line(cardno);
end;
使用UTL_FILE包读写文件
create directory MY_DIR as 'd:\temp';
create directory MY_DIR2 as 'd:\temp2';
declare
定义文件对象
myfile utl_file.file_type;
定义变量 用来存储每读出一行的数据
linestr varchar2(200);
begin
打开文件
myfile:= utl_file.fopen('MY_DIR','oracle.log','r');
进行读取(循环)
loop
utl_file.get_line(myfile,linestr);
dbms_output.put_line(linestr);
end loop;
exception
when others then
utl_file.fclose(myfile);
dbms_output.put_line('读取完毕!');
end;
declare
定义文件对象
myfile utl_file.file_type;
定义变量 用来存储每读出一行的数据
linestr varchar2(200);
begin
打开文件
myfile:= utl_file.fopen('MY_DIR','oracle.log','a');
追加数据
utl_file.get_line(myfile,linestr);
utl_file.put_line(myfile,'你好,这是新的数据',true);
utl_file.fclose(myfile);
exception
when others then
utl_file.fclose(myfile);
dbms_output.put_line('读取完毕!');
end;
拷贝
declare
定义文件对象
sourcefile utl_file.file_type;
targetfile utl_file.file_type;
定义变量 用来存储每读出一行的数据
linestr varchar2(200);
begin
打开文件
sourcefile:= utl_file.fopen('MY_DIR','oracle.log','r');
targetfile:= utl_file.fopen('MY_DIR2','copy.txt','a');
进行读取(循环) 并且写入到新的文件里面
loop
utl_file.get_line(sourcefile,linestr);
utl_file.put_line(targetfile,linestr,true);
dbms_output.put_line(linestr);
end loop;
exception
when others then
utl_file.fclose(sourcefile);
utl_file.fclose(targetfile);
dbms_output.put_line('拷贝完毕!');
end;
关于Oracle触发器的问题就为大家讲解到这里,还有很多Oracle触发器的知识这里没有涉及到,以后我还会继续为大家讲解更多的关于Oracle触发器的知识点,希望对大家能够有所帮助。
关于oracle的触发器的讲解
由DML事件引发的触发器,编写DML触发器时的两点要素是:
1.确定触发的表,即在其上定义触发器的表。
2.确定触发的事件,DML触发器的触发事件有INSERT、UPDATE和DELETE三种;替代触发器,简称INSTEAD OF触发器,创建在视图上,用来替换对视图进行的删除、插入和修改操作; 数据定义语言(DDL)触发器,简称DDL触发器,定义在模式上,触发事件是数据对象的创建和修改;数据库事件触发器,定义在整个数据库或模式上,触发事件是数据库事件.
www.2cto.com
功能:
1、 允许/限制对表的修改
2、 自动生成派生列,比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑
ORACLE产生数据库触发器的语法为:
CREATE [OR REPLACE] TRIGGER 触发器名
{BEFORE|AFTER|INSTEAD OF} 触发事件1 [OR 触发事件2...]
ON 表名
WHEN 触发条件
[FOR EACH ROW]
DECLARE
声明部分
BEGIN
主体部分
www.2cto.com
END;
###StudyGet_Info_Pagination_SIGN###
其中:
触发器名:触发器对象的名称。由于触发器是数据库自动执行的,因此该名称只是一个名称,没有实质的用途。一个触发器可由多个不同的数据操纵语言操作触发。在触发器中,可用INSERTING、DELETING、UPDATING谓词来区别不同的数据操纵语言操作。这些谓词可以在IF分支条件语句中作为判断条件来使用。
触发时间:指明触发器何时执行,该值可取, 触发的时间有BEFORE和AFTER两种,分别表示触发动作发生在DML语句执行之前和语句执行之后。确定触发级别,有语句级触发器和行级触发器两种。语句级触发器表示SQL语句只触发一次触发器,行级触发器表示SQL语句影响的每一行都要触发一次。
Before:表示在数据库动作之前触发器执行;在SQL语句的执行过程中,如果存在行级BEFORE触发器,则SQL语句在对每一行操作之前,都要先执行一次行级BEFORE触发器,然后才对行进行操作。如果存在行级AFTER触发器,则SQL语句在对每一行操作之后,都要再执行一次行级AFTER触发器。
after:表示在数据库动作之后出发器执行。如果存在语句级AFTER触发器,则在SQL语句执行完毕后,要最后执行一次语句级AFTER触发器。
触发事件:指明哪些数据库动作会触发此触发器,指INSERT、DELETE或UPDATE事件,事件可以并行出现,中间用OR连接; www.2cto.com
insert:数据库插入会触发此触发器;
update:数据库修改会触发此触发器;
delete:数据库删除会触发此触发器。
表 名:数据库触发器所在的表。
for each row:表示触发器为行级触发器,省略则为语句级触发器,对表的每一行触发器执行一次。
触发器的创建者或具有DROP ANY TIRGGER系统权限的人才能删除触发器。删除触发器的语法如下:
DROP TIRGGER 触发器名
可以通过命令设置触发器的可用状态,使其暂时关闭或重新打开,即当触发器暂时不用时,可以将其置成无效状态,在使用时重新打开。该命令语法如下:
www.2cto.com
ALTER TRIGGER 触发器名 {DISABLE|ENABLE}
其中,DISABLE表示使触发器失效,ENABLE表示使触发器生效。
同存储过程类似,触发器可以用SHOW ERRORS 检查编译错误。
如果有多个触发器被定义成为相同时间、相同事件触发,且最后定义的触发器是有效的,则最后定义的触发器被触发,其他触发器不执行。触发器体内禁止使用COMMIT、ROLLBACK、SAVEPOINT语句,也禁止直接或间接地调用含有上述语句的存储过程。定义一个触发器时要考虑上述多种情况,并根据具体的需要来决定触发器的种类。
触发器的作用
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外,触发器还有其它许多不同的功能:
(1) 强化约束(Enforce restriction)
触发器能够实现比CHECK 语句更为复杂的约束。
www.2cto.com
(2) 跟踪变化Auditing changes
触发器可以侦测数据库内的操作,从而不允许数据库中未经许可的指定更新和变化。
(3) 级联运行(Cascaded operation)。
触发器可以侦测数据库内的操作,并自动地级联影响整个数据库的各项内容。例如,某个表上的触发器中包含有对另外一个表的数据操作(如删除,更新,插入)而该操作又导致该表上触发器被触发。
(4) 存储过程的调用(Stored procedure invocation)。
为了响应数据库更新触,发器可以调用一个或多个存储过程,甚至可以通过外部过程的调用而在DBMS( 数据库管理系统)本身之外进行操作。
www.2cto.com
由此可见,触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题。例如,触发器能够找出某一表在数据修改前后状态发生的差异,并根据这种差异执行一定的处理。此外一个表的同一类型(INSERT、 UPDATE、 DELETE)的多个触发器能够对同一种数据操作采取多种不同的处理。
总体而言,触发器性能通常比较低。
当运行触发器时,系统处理的大部分时间花费在参照其它表的这一处理上,因为这些表既不在内存中也不在数据库设备上,而删除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。
---------------------------示例说明----------------------
开始
create trigger biufer_employees_department_id
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
when (new_value.department_id<>80 )
begin
:new_value.commission_pct :=0;
end;
/
www.2cto.com
触发器的组成部分:
1、 触发器名称
2、 触发语句
3、 触发器限制
4、 触发操作
1、 触发器名称
create trigger biufer_employees_department_id
命名习惯:
biufer(before insert update for each row)
employees 表名
department_id 列名
2、 触发语句
比如:
表或视图上的DML语句
DDL语句
数据库关闭或启动,startup shutdown 等等
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
说明:
1、 无论是否规定了department_id ,对employees表进行insert的时候
2、 对employees表的department_id列进行update的时候
www.2cto.com
3、 触发器限制
when (new_value.department_id<>80 )
限制不是必须的。此例表示如果列department_id不等于80的时候,触发器就会执行。
其中的new_value是代表跟新之后的值。
4、 触发操作
是触发器的主体
begin
:new_value.commission_pct :=0;
end;
主体很简单,就是将更新后的commission_pct列置为0
触发:
insert into employees(employee_id,
last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )
values( 12345,’Chen’,’Donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25);
select commission_pct from employees where employee_id=12345;
触发器不会通知用户,便改变了用户的输入值。
触发器类型:
1、 语句触发器
2、 行触发器
3、 INSTEAD OF 触发器
4、 系统条件触发器
5、 用户事件触发器
www.2cto.com
1、 语句触发器
是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、
DELETE或者组合上进行关联。但是无论使用什么样的组合,各个语句触发器都只会针对指定语句激活一次
。比如,无论update多少行,也只会调用一次update语句触发器。
例子:
需要对在表上进行DML操作的用户进行安全检查,看是否具有合适的特权。
Create table foo(a number);
Create trigger biud_foo
Before insert or update or delete
On foo
Begin
If user not in (‘DONNY’) then
Raise_application_error(-20001, ‘You don’t have access to modify this table.’);
End if;
End;
/ www.2cto.com
即使SYS,SYSTEM用户也不能修改foo表
[试验]
对修改表的时间、人物进行日志记录。
1、 建立试验表
create table employees_copy as select *from hr.employees
2、 建立日志表
create table employees_log(
who varchar2(30),
when date);
3、 在employees_copy表上建立语句触发器,在触发器中填充employees_log 表。
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Begin
Insert into employees_log(
Who,when)
Values( user, sysdate);
End;
/ www.2cto.com
4、 测试
update employees_copy set salary= salary*1.1;
select *from employess_log;
5、 确定是哪个语句起作用?
即是INSERT/UPDATE/DELETE中的哪一个触发了触发器?
可以在触发器中使用INSERTING / UPDATING / DELETING 条件谓词,作判断:
begin
if inserting then
-----
elsif updating then
-----
elsif deleting then
------
end if;
end;
if updating(‘COL1’) or updating(‘COL2’) then
------
end if;
www.2cto.com
[试验]
1、 修改日志表
alter table employees_log
add (action varchar2(20));
2、 修改触发器,以便记录语句类型。
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Declare
L_action employees_log.action%type;
Begin
if inserting then
l_action:=’Insert’;
elsif updating then
l_action:=’Update’;
elsif deleting then
l_action:=’Delete’;
else
raise_application_error(-20001,’You should never ever get this error.’);
Insert into employees_log(
Who,action,when)
Values( user, l_action,sysdate);
End;
/
www.2cto.com
3、 测试
insert into employees_copy( employee_id, last_name, email, hire_date, job_id)
values(12345,’Chen’,’Donny@hotmail’,sysdate,12);
select *from employees_log
update employees_copy set salary=50000 where employee_id = 12345;
2、 行触发器
是指为受到影响的各个行激活的触发器,定义与语句触发器类似,有以下两个例外:
1、 定义语句中包含FOR EACH ROW子句
2、 在BEFORE……FOR EACH ROW触发器中,用户可以引用受到影响的行值。
比如:
定义:
create trigger biufer_employees_department_id
before insert or update
of department_id
on employees_copy
referencing old as old_value
new as new_value
for each row
when (new_value.department_id<>80 )
begin
:new_value.commission_pct :=0;
end;
/
www.2cto.com
Referencing 子句:
执行DML语句之前的值的默认名称是ld ,之后的值是 :new
insert 操作只有:new
delete 操作只有ld
update 操作两者都有
referencing子句只是将new 和old重命名为new_value和old_value,目的是避免混淆。比如操作一个名为
new的表时。
作用不很大。
[试验]:为主健生成自增序列号
drop table foo;
create table foo(id number, data varchar2(20));
create sequence foo_seq;
create or replace trigger bifer_foo_id_pk
before insert on foo
for each row
begin
select foo_seq.nextval into :new.id from dual;
end; www.2cto.com
/
insert into foo(data) values(‘donny’);
insert into foo values(5,’Chen’);
select * from foo;
3、 INSTEAD OF 触发器更新视图
Create or replace view company_phone_book as
Select first_name||’, ’||last_name name, email, phone_number,
employee_id emp_id
From hr.employees;
尝试更新email和name
update hr.company_phone_book
set name=’Chen1, Donny1’
where emp_id=100
www.2cto.com
create or replace trigger update_name_company_phone_book
INSTEAD OF
Update on hr.company_phone_book
Begin
Update hr.employees
Set employee_id=:new.emp_id,
First_name=substr(:new.name, instr(:new.name,’,’)+2),
last_name= substr(:new.name,1,instr(:new.name,’,’)-1),
phone_number=:new.phone_number,
email=:new.email
where employee_id=:old.emp_id;
end;
4、 系统事件触发器
系统事件:数据库启动、关闭,服务器错误
create trigger ad_startup
after startup
on database
begin
-- do some stuff
end;
/
5、 用户事件触发器
用户事件:用户登陆、注销,CREATE / ALTER / DROP / ANALYZE / AUDIT / GRANT / REVOKE /
RENAME / TRUNCATE / LOGOFF
例子:记录删除对象
1. 日志表
create table droped_objects(
object_name varchar2(30),
object_type varchar2(30),
dropped_on date);
www.2cto.com
2.触发器
create or replace trigger log_drop_trigger
before drop on donny.schema
begin
insert into droped_objects values(
ora_dict_obj_name, -- 与触发器相关的函数
ora_dict_obj_type,
sysdate);
end;
/
3. 测试
create table drop_me(a number);
create view drop_me_view as select *from drop_me;
drop view drop_me_view;
drop table drop_me;
www.2cto.com
select *from droped_objects
禁用和启用触发器
alter trigger <trigger_name> disable;
alter trigger <trigger_name> enable;
事务处理:
在触发器中,不能使用commit / rollback
因为ddl语句具有隐式的commit,所以也不允许使用
视图:dba_triggers
以上关于“[关于数据库,oracle触发器][oracle触发器]Oracle触发器”的信息由网友上传分享,希望对您有所帮助 ,感谢您对就爱阅读网的支持!
来源: http://www.92to.com/bangong/2017/06-06/22857211.html