TRIGGERS - 2

[debasisdas]

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