随笔 - 196&
文章 - 0&评论 - 68&trackbacks - 0
触发器的创建与使用&编写触发器执行代码注意事项：&1）触发器代码的大小不能超过32K。如果确实需要使用大量代码建立触发器，应该首先建立存储过程，然后在触发器中使用call语句调用存储过程。2）触发器只能包含SELECT,INSERT,UPDATE,DELETE语句，而不能包含DDL语句（CREATE,ALTER,DROP）和事务控制性语句（COMMIT,ROLLBACK和SAVEPOINT）。 语句触发器： 1、建立before语句触发器CREATE OR REPLACE TRIGGER tr_sec_emp& BEFORE INSERT OR UPDATE OR DELETE ON empBEGIN& IF to_char(SYSDATE, 'DY', 'nls_date_language=AMERICAN') IN ('SAT', 'SUN') THEN&&& raise_applicaton_error(-20001, '不能在休息日改变雇员信息');& END IF;END;当有多个触发事件时，可以将其区分：CREATE OR REPLACE TRIGGER tr_sec_emp& BEFORE INSERT OR UPDATE OR DELETE ON empBEGIN& IF to_char(SYSDATE, 'DY', 'nls_date_language=AMERICAN') IN ('SAT', 'SUN') THEN&&& CASE&&&&& WHEN INSERTING THEN& --当触发事件是insert时&&&&&&& raise_applicaton_error(-20001, '不能在休息日增加雇员');&&&&& WHEN UPDATING THEN&& --当触发事件是update时&&&&&&& raise_applicaton_error(-20001, '不能在休息日修改雇员信息');&&&&& WHEN DELETING THEN&& --当触发事件是delete时&&&&&&& raise_applicaton_error(-20001, '不能在休息日解雇雇员');&&& END CASE; END IF;END;&2、建立after语句触发器--创建表audit_tableCREATE TABLE audit_table(name VARCHAR2(20),ins INT,upd INT,del INT,starttime DATE,endtime DATE);--创建AFTER语句触发器CREATE OR REPLACE TRIGGER tr_audit_empAFTER INSERT OR UPDATE OR DELETE ON EMPDECLARE& v_temp INT;BEGIN& SELECT count(*) INTO v_temp FROM audit_table&&& WHERE name='EMP';& IF v_temp=0 THEN&&& INSERT INTO audit_table VALUES&&& ('EMP',0,0,0,SYSDATE,NULL);& END IF;& CASE&&& WHEN INSERTING THEN&&&&& UPDATE audit_table SET ins=ins+1,endtime=SYSDATE&&&&&&& WHERE name='EMP';&&& WHEN UPDATING THEN&&&&& UPDATE audit_table SET upd=upd+1,endtime=SYSDATE&&&&&&& WHERE name='EMP';&&& WHEN DELETING THEN&&&&& UPDATE audit_table SET del=del+1,endtime=SYSDATE&&&&&&& WHERE name='EMP';&&& END CASE;& END;--往emp表加一条数据 INSERT INTO EMP VALUES (8,'张三','manager',7839,to_date('','YYYY-mm-dd'),3450,null,10);&--更新一条记录（由于执行两次，所以upd值为2）UPDATE EMP SET ename='李四' WHERE empno=8;--删除一条记录DELETE FROM EMP WHERE empno=8;行触发器：--创建before行触发器CREATE OR REPLACE TRIGGER tr_emp_salBEFORE UPDATE OF sal ON EMPFOR EACH ROWBEGIN& IF :new.sal&:old.sal THEN&&& raise_application_error(-2000,'工资只涨不降');& END IF;END;&&&& &&&&& &UPDATE EMP SET sal =1000 WHERE empno=7; --员工编号7的原来工资为2450，而更新为1000，触发器不允许--创建after行触发器CREATE TABLE audit_emp_change(name VARCHAR2(10),oldsal NUMBER(6,2),newsal NUMBER(6,2),time DATE);--创建该触发器用于记录工资发生变化的日期CREATE OR REPLACE TRIGGER tr_sal_change& AFTER UPDATE OF sal ON emp& FOR EACH ROWDECLARE& v_temp INT;BEGIN& SELECT count(*) INTO v_temp FROM audit_emp_change WHERE name = :old.& IF v_tmep = 0 THEN&&& INSERT INTO audit_emp_change&&& VALUES&&&&& (:old.ename, :old.sal, :new.sal, SYSDATE);& ELSE&&& UPDATE audit_emp_change&&&&&& SET oldsal = :old.sal, newsal = :new.sal, time = SYSDATE&&&& WHERE name = :old.& END IF;END;&--限制行触发器CREATE OR REPLACE TRIGGER tr_sal_change& AFTER UPDATE OF sal ON EMP& FOR EACH ROW& WHEN (old.job = 'SALESMAN')DECLARE& v_temp INT;BEGIN& SELECT count(*)&&& INTO v_temp&&& FROM audit_emp_change&& WHERE name = :old.& IF v_temp = 0 THEN&&& INSERT INTO audit_emp_change&&& VALUES&&&&& (:old.ename, :old.sal, :new.sal, SYSDATE);& ELSE&&& UPDATE audit_emp_change&&&&&& SET oldsal = :old.sal, newsal = :new.sal, time = SYSDATE&&&& WHERE name = :old.& END IF;END;&DML语句触发器注意事项：&触发器代码不能触发器所对应的基表中读取数据。如要基于EMP表建立触发器，那么该触发器的执行代码不能包含对EMP表的查询操作。尽管在建立触发器时不会出现任何错误，但在执行相应触发操作时会显示错误信息。假设希望雇员工资不能超过当前的最高工资，并使用触发器实现该规则：CREATE OR REPLACE TRIGGER tr_emp_sal& BEFORE UPDATE OF sal ON emp& FOR EACH ROWDECLARE& maxsal NUMBER(6, 2);BEGIN& SELECT max(sal) INTO maxsal FROM& IF :new.sal & maxsal THEN&&& raise_application_error(-20001, '超出工资上限');& END IF;END;&&UPDATE emp SET sal=6000 WHERE empno=7788;执行上述update操作时提示错误如下图所示：&&说明：为满足数据库数据特定规则，可以使用约束、触发器、子程序实现。因为约束性能最好，实现最简单，所以首选约束；如果约束不能实现特定规则，那么应该选择触发器；如果触发器仍然不能满足实现特定规则，那么应该选择子程序（过程和函数）。1）如为了实现雇员工资不能低于800元，可以选用check约束，示例如下：alter table emp add constraint ck_sal check （sal&=800）; 2）如假定雇员工资不能低于其原工资，但也不能高于原工资的20%，使用约束无法实现，但可以通过触发器实现。示例如下：create or replace trigger tr_check_salbefore update of sal on empfor each rowwhen （new.sal&old.sal or new.sal&1.2*old.sal）begin&raise_application_error(-20931,'工资只升不降，并且升幅不能超过20%'); INSTEAD OF触发器&&& 可以在表或视图上指定INSTEAD OF触发器。执行这种触发器就能够替代原始的触发动作。INSTEAD OF触发器扩展了视图更新的类型。对于每一种触发动作(INSERT、UPDATE或 DELETE)，每一个表或视图只能有一个INSTEAD OF触发器。　　 不能在带有WITH CHECK OPTION定义的视图中创建INSTEAD OF触发器。&&& INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。基于多个基表的视图必须使用。INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。INSTEAD OF 触发器的另一个优点是使您得以编写这样的逻辑代码：在允许批处理的其他部分成功的同时拒绝批处理中的某些部分。当视图中包含下面的结构之一时，就是不可更新的视图：●& 集合运算符（union、union all、intersect、minus）●& 分组函数（min、max、sum、avg、count等）●& CASE或DECODE语句●& CONNECT BY、GROUP BY、HAVING或START WITH子句●& DISTINCT运算符●& 连接(当包含连接键时会引发异常)&&& 建立INSTEAD OF 触发器注意事项：●INSTEAD OF选项只适用于视图；●当基于视图建立触发器时，不能指定before和after选项；●在建立视图时没有指定with check option选择；●当建立INSTEAD OF触发器时，必须指定for each row选项创建一个基于连接的视图create or replace view dept_emp as& select a.deptno,a.deptname,b.empno,b.ename from dept a,emp b where a.deptno=b.可以直接查询该视图，但无法执行insert操作：insert into dept_emp values(50,'admin','1223','mary')因此，创建一个instead of 触发器create or replace trigger tr_instead_of_dept_empinstead of insert on dept_empfor each rowdeclare&v_begin& select count(*) into v_temp from dept&&& where deptno=:new.& if v_temp=0 then&&& insert into dept(deptno,deptname)&&&&& values(:new.deptno,:new.deptname);&& select count(*) into v_temp from emp&&& where empno=:new.& if v_temp=0 then&&& insert into emp (empno,ename,deptno)&&&&& values(:new.empno,:new.ename,:new.deptno);&创建完后再执行插入操作就可以顺利插入数据了。&&系统触发器& 用于创建触发器的常用的事件属性函数如下：Ora_client_ip_address 返回客户端的ip地址Ora_database_name 返回当前数据库名Ora_des_encrypted_password 返回des加密后的用户口令Ora_dict_obj_name 返回ddl操作所对应的数据库对象名Ora_dict_obj_name_list(name_list out ora_name_list_t) 返回在事件中被修改的对象名列表Ora_dict_obj_owner 返回ddl操作所对应的对象的所有者名Ora_dict_obj_owner_list(owner_list out ora_name_list_t) 返回在事件中被修改的对象的所有者列表Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型Ora_grantee(user_list out ora_name_list_t) 返回授权事件的授权者Ora_instance_num 返回例程号Ora_is_alter_column(column_name in varchar2) 检测特定列是否被修改Ora_is_creating_nested_table 检测是否正在建立嵌套表Ora_is_drop_column(column_name in varchar2) 检测特定列是否被删除Ora_is_servererror(error_number) 检测是否返回了特定oracle错误Ora_login_user 返回登录用户名Ora_sysevent 返回触发器的系统事件名。& --创建例程启动和关闭的触发器，用于记录例程启动和关闭的事件和时间&首先创建时间表event_table表&create table event_table(event varchar2(30),time date); &例程启动触发器只能使用after关键字，而例程关闭触发器只能使用before关键字create or replace trigger tr_startupafter startup on databasebegin& insert into event_table values(ora_sysevent,sysdate);&create or replace trigger tr_shutdownbefore shutdown on databasebegin& insert into event_table values(ora_sysevent,sysdate); & select * from event_table && --建立登录和退出触发器& 用于记载用户登录和退出时间，可以分别建立登录和退出触发器。& 首先建立表log_tablecreate table log_table(username varchar2(20),logon_time date,logoff date,address varchar2(20)) &登录触发器只能使用after关键字，而退出触发器只能使用before关键字create or replace trigger tr_logonafter logon on databasebegin& insert into log_table(username,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address); & create or replace trigger tr_logoffbeforbegin& insert into log_table(username,logoff_time,address) values(ora_login_user,sysdate,ora_client_ip_address);&& select * from log_table&
阅读(...) 评论()> oracle触发器问题创建一个触发器：定义一个before行级触发器，为员工表emp更新工资时定义
oracle触发器问题创建一个触发器：定义一个before行级触发器，为员工表emp更新工资时定义
lll123 & &
发布时间： & &
浏览：3 & &
回复：2 & &
悬赏：0.0希赛币
oracle触发器问题创建一个触发器：定义一个before行级触发器，为员工表emp更新工资时定义一个完整性规则job为“MANAGER”的工资不得低于2500元，如果低于2500员，自动改为2500元。create or replace trigger tri_sal_empbefore update on empfor each row& when(old.job='MANAGER')declareasal
emp.sal%begin&
& select emp.sal into asal from emp & where
ename= :old. & if asal&2500 then&
asal :=2500; & &这个代码写的有问题& 求指教，求教育
create or replace trigger tri_sal_empbefore update on empfor each row& when(old.job='MANAGER' and new.sal&2500)begin& :new.sal:=2500;end tri_sal_
lllangzi29 & &
00:39:28 & &
& & （0）（0）引用
代码参考：　　SQL code　　CREATE OR REPLACE TRIGGER tri_sal_emp
BEFORE UPDATE ON emp
FOR EACH ROW
WHEN ( job = 'MANAGER')
asal emp.sal%TYPE;
IF :NEW.sal & 2500 THEN
:New.sal := 2500;
END;lllabcd1234 & &
00:39:28 & &
& & （0）（0）引用
本问题标题：
本问题地址：
温馨提示：本问答中心的任何言论仅代表发言者个人的观点，与希赛网立场无关。请对您的言论负责，遵守中华人民共和国有关法律、法规。如果您的言论违反希赛网问答中心的规则，将会被删除。
暂无合适的专家
&&&&&&&&&&&&&&&
希赛网 版权所有 & &&}