8,127
=====================
Expand|Select|Wrap|Line Numbers
- 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;
=====================
Expand|Select|Wrap|Line Numbers
- 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;
========================
The table responsible for backup:
-------------------------------------------------
Expand|Select|Wrap|Line Numbers
- create table back
- (
- empno number(4),
- ename varchar2(10),
- sal number(7,2),
- deptno number(2),
- uname varchar2(10),
- cdate date,
- operation varchar2(15)
- );
============
Expand|Select|Wrap|Line Numbers
- 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;
trigger sample code #8
=====================
Write this trigger in system/sys user.
------------------------------------------------------
Expand|Select|Wrap|Line Numbers
- 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;
trigger sample ex #9
=================
Expand|Select|Wrap|Line Numbers
- 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;
Since triggers can't validate themselves.
The above trigger can't restrict droping of itself.
Also check TRIGGERS - 3
