By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,622 Members | 2,072 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

TRIGGERS - 2

debasisdas
Expert 5K+
P: 8,127
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
Share this Article
Share on Google+