TRIGGERS - 2

[debasisdas]

trigger sample example #5
=============== ======

[CODE=oracle]create or replace trigger mytrig
before UPDATE OF SAL on emp for each row
when(new.deptno =30)
begin
if :new.sal<:OLD.S AL then
raise_applicati on_error(-20003,'Sal should be > THE OLDER SAL');
end if;
end;[/CODE]

trigger sample example #6
=============== ======
[CODE=oracle]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;[/CODE]

trigger sapmle example #7
=============== =========
The table responsible for backup:
-------------------------------------------------
[CODE=oracle]
create table back
(
empno number(4),
ename varchar2(10),
sal number(7,2),
deptno number(2),
uname varchar2(10),
cdate date,
operation varchar2(15)
);
[/code]
trigger code
============
[code=oracle]
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.emp no,:new.ename,: new.sal,:new.de ptno,user,sysda te,'INSERTION') ;
elsif updating then
insert into back values(:old.emp no,:old.ename,: old.sal,:old.de ptno,user,sysda te,'UPDATION');
else
insert into back values(:old.emp no,:old.ename,: old.sal,:old.de ptno,user,sysda te,'DELETION');
end if;
end;[/CODE]

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.
------------------------------------------------------
[CODE=oracle]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_applicati on_error(-20004,'Cannot do LOGIN today');
end if;
end;[/CODE]

Note :--This schema level trigger restricts the user(in this example--SCOTT) from logging in into the database on weekend.

trigger sample ex #9
=============== ==
[CODE=oracle]
CREATE OR REPLACE TRIGGER NODROP BEFORE DROP ON
DEBASIS.SCHEMA
BEGIN
IF ORA_DICT_OBJ_OW NER='DEBASIS' THEN
RAISE_APPLICATI ON_ERROR(-20005,'INVALID DROP OPTION');
END IF;
END;[/CODE]

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