trigger sample example #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;
trigger sample example #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;
trigger sapmle example #7
========================
The table responsible for backup:
-------------------------------------------------
-
create table back
-
(
-
empno number(4),
-
ename varchar2(10),
-
sal number(7,2),
-
deptno number(2),
-
uname varchar2(10),
-
cdate date,
-
operation varchar2(15)
-
);
-
trigger code
============
-
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;
Note:--Monitoring the user transactions on the table in another table with out the knowledge of the user.
trigger sample code #8
=====================
Write this trigger in system/sys user.
------------------------------------------------------
- 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;
Note :--This schema level trigger restricts the user(in this example--SCOTT) from logging in into the database on weekend.
trigger sample 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;
Note :--the trigger restricts droping of any object whose owner is DEBASIS
Since triggers can't validate themselves.
The above trigger can't restrict droping of itself.
Also check
TRIGGERS - 3 
