触发示例5
=====================
create or replace trigger mytrig
before UPDATE OF SAL on emp for each row
when(new.deptno=30)
begin
if :new.sal<:OLD.SAL then
raise_application_error(-20003,'Sal should be > THE OLDER SAL');
end if;
end;
触发示例6
=====================
create or replace trigger mytrig
before insert on emp
for each row
begin
select myseq.nextval | max(empno)+1 into :new.empno from emp;
end;
触发sapmle示例#7
========================
负责备份的表:
-------------------------------------------------
create table back
(
empno number(4),
ename varchar2(10),
sal number(7,2),
deptno number(2),
uname varchar2(10),
cdate date,
operation varchar2(15)
);
触发码
============
create or replace trigger mytrig
before insert or update or delete on emp
for each row
begin
if inserting then
insert into back values(:new.empno,:new.ename,:new.sal,:new.deptno,user,sysdate,'INSERTION');
elsif updating then
insert into back values(:old.empno,:old.ename,:old.sal,:old.deptno,user,sysdate,'UPDATION');
else
insert into back values(:old.empno,:old.ename,:old.sal,:old.deptno,user,sysdate,'DELETION');
end if;
end;
注意:-在用户不了解的情况下,监视另一个表中该表上的用户事务。
触发示例代码#8
=====================
在系统/ sys用户中编写此触发器。
-------------------------------------------------- ----
create or replace trigger mytrig1
after logon on scott.schema
declare
a char(3);
begin
select to_char(sysdate,'dy') into a from dual;
if a in('sun','sat') then
Raise_application_error(-20004,'Cannot do LOGIN today');
end if;
end;
注意:-此架构级别触发器限制用户(在本示例中为SCOTT)在周末登录数据库。
触发样本ex#9
=================
CREATE OR REPLACE TRIGGER NODROP BEFORE DROP ON
DEBASIS.SCHEMA
BEGIN
IF ORA_DICT_OBJ_OWNER='DEBASIS' THEN
RAISE_APPLICATION_ERROR(-20005,'INVALID DROP OPTION');
END IF;
END;
注意:-触发器限制删除所有者为DEBASIS的任何对象
由于触发器无法验证自己。
上面的触发器不能限制其自身的下降。
还要检查
触发器-3From: https://bytes.com/topic/oracle/insights/753143-triggers-2-a