1.什么是Oracle触发器?
我的理解:oracle触发器是由oracle表数据发生改变(新增、修改、删除)时联动触发的表数据、结构变化的函数或过程的定义功能,如同建表语句,是由变更操作产生联动操作的定义语句。
标准定义:触发器是在事件发生时隐式地自动运行的PL/SQL程序块,不能接收参数,不能被调用。补充:不能接收参数,但可以使用内置的对象:new和:old获取产生变化的数据。
2.什么时候用到Oracle触发器?
我的理解:工作中遇到的触发器使用场景,有如下几种:
1.关联表相同字段同步更新,如A表和B表均有字段M,当A表的字段M更新后,可定义触发器同步更新B表的字段M;
2.表主键取值,oracle没有自增序列,定义主键相关序列后,可用触发器在每条数据新增前取序列值作为主键插入到表中。
标准定义:没有很标准的定义,除了上述两种场景,还有一种替代触发器,替代触发器用于解决当组成视图的表是两个及两个以上时,无法更新的问题,示例如下:
Create Or Replace Trigger tr_v_e_d
Instead Of Insert On v_emp_dept
For Each Row
Begin
Insert Into dept(deptno,dname) Values(:New.deptno,:New.dname);
Insert Into emp(empno,ename,job,deptno) Values(:New.empno,:New.ename,:New.job,:New.deptno);
End;
3.怎么使用触发器?
触发器的定义语句一般分为名称、触发时机、变量声明和PL/SQL程序块组成,以下是行触发器的示例,另外还有表触发器及系统触发器未进行列举,定义形式大体一致:
CREATE OR REPLACE TRIGGER "TR_CLT_CHGNSCLIENT"
AFTER INSERT OR UPDATE ON clt_clt_info
FOR EACH ROW
declare
rowcount integer;
tableType varchar2(10);
BEGIN
select object_type into tableType from user_objects where object_name = 'NSCLIENT';
if tableType = 'TABLE' then
if :new.stauts = 2 then
begin
select count(*) into rowcount from nsclient where cltno =:new.cltno;
if rowcount>0 then
begin
UPDATE NsClient SET CltName = :new.CHINESENAME,
address = :new.BUSSADDR,
postcode = :new.POSTCODE,
telphone = :new.PHONE,
fax = :new.FAX,
nstype = :new.TYPE,
jurperson = :new.LEGALPER,
treeno = :new.TREENO,
firmcode = :new.ORGCODE
WHERE CltNo = :new.cltno;
end;
else
begin
INSERT INTO NsClient (
cltno,cltname,address,postcode,telphone,fax,jurperson,treeno,firmcode,nsType,brno)
VALUES (:new.cltno,:new.CHINESENAME,:new.BUSSADDR,:new.POSTCODE,
:new.PHONE,:new.FAX,:new.LEGALPER,:new.TREENO,:new.ORGCODE,:new.TYPE,:new.BRNO
);
END;
end if;
end;
end if;
end if;
END;
4.使用触发器遇到的问题
问题描述:在A表新增了UPDATE_TIME字段,计划用触发器新增或更新该字段,减少代码改动,在触发器中使用update a set update_time=sysdate时提示ORA-04091: 表发生了变化, 触发器/函数不能读它;
问题原因:A表更新之后事务未提交,再次执行update操作就会导致ORA-04091错误;
解决方案:
方案A:使用PRAGMA AUTONOMOUS_TRANSACTION在定义触发器时显示声明事务,可用于表数据的新增或是不同表数据的操作,不适用于更新操作;
方案B:定义触发器PL/SQL程序块在更新语句前执行,使得更新数据同一事务提交,这样就能避免ORACLE错误,示例如下:
CREATE OR REPLACE TRIGGER "TR_CPM_CUR_BALANCE_UPDATETIME"
BEFORE INSERT OR UPDATE ON cpm_cur_balance
FOR EACH ROW
declare
rowcount integer;
BEGIN
:new.update_time := sysdate;
END;
/
5.知识扩展
ORACLE没有DLL语句,只有DML、DDL、DCL语句,定义分别如下:
DML(Data Manipulation Language,数据操作语言):用于检索或者修改数据。
DML包括: SELECT、INSERT、UPDATE、DELETE。
DDL(Data Definition Language,数据定义语言): 用于定义数据的结构,比如 创建、修改或者删除数据库对象。
DDL包括:DDL语句可以用于创建用户和重建数据库对象。下面是DDL命令:CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE INDEX、DROP INDEX
DCL(Data Control Language,数据控制语言):用于定义数据库用户的权限。
DCL包括:ALTER PASSWORD 、GRANT 、REVOKE 、CREATE SYNONYM。