470,874 Members | 1,509 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

TRIGGERS - 2

debasisdas
8,127 Expert 4TB
trigger sample example #5
=====================

Expand|Select|Wrap|Line Numbers
  1. create or replace trigger mytrig
  2. before UPDATE OF SAL on emp for each row
  3. when(new.deptno=30)
  4. begin
  5. if :new.sal<:OLD.SAL then
  6. raise_application_error(-20003,'Sal should be > THE OLDER SAL');
  7. end if;
  8. end;
trigger sample example #6
=====================
Expand|Select|Wrap|Line Numbers
  1. create or replace trigger mytrig
  2. before insert on emp
  3. for each row
  4. begin
  5. select myseq.nextval | max(empno)+1 into :new.empno from emp;
  6. end;
trigger sapmle example #7
========================
The table responsible for backup:
-------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. create table back 
  2. (
  3. empno number(4),
  4. ename varchar2(10), 
  5. sal number(7,2), 
  6. deptno number(2),
  7. uname varchar2(10), 
  8. cdate date,
  9. operation varchar2(15)
  10. );
  11.  
trigger code
============
Expand|Select|Wrap|Line Numbers
  1. create or replace trigger mytrig
  2. before insert or update or delete on emp 
  3. for each row
  4. begin
  5. if inserting then
  6. insert into back values(:new.empno,:new.ename,:new.sal,:new.deptno,user,sysdate,'INSERTION');
  7. elsif updating then
  8. insert into back values(:old.empno,:old.ename,:old.sal,:old.deptno,user,sysdate,'UPDATION');
  9. else
  10. insert into back values(:old.empno,:old.ename,:old.sal,:old.deptno,user,sysdate,'DELETION');
  11. end if;
  12. 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.
------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. create or replace trigger mytrig1
  2. after logon on scott.schema
  3. declare
  4. a char(3);
  5. begin
  6. select to_char(sysdate,'dy') into a from dual;
  7. if a in('sun','sat') then
  8. Raise_application_error(-20004,'Cannot do LOGIN today');
  9. end if;
  10. 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
=================
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE TRIGGER NODROP BEFORE DROP ON 
  2. DEBASIS.SCHEMA
  3. BEGIN
  4. IF ORA_DICT_OBJ_OWNER='DEBASIS' THEN
  5. RAISE_APPLICATION_ERROR(-20005,'INVALID DROP OPTION');
  6. END IF;
  7. 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
Jun 13 '07 #1
0 2891

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by raulgz | last post: by
1 post views Thread by jason_s_ford | last post: by
4 posts views Thread by Mark Flippin | last post: by
1 post views Thread by tim.pascoe | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
4 posts views Thread by --CELKO-- | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.