Create tigger on wrapper table : Create Trigger « Trigger « Oracle PL / SQL






Create tigger on wrapper table

   
SQL>
SQL>
SQL> set echo one
SP2-0265: echo must be set ON or OFF
SQL> set serveroutput on
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));

Table created.

SQL>
SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7521, 'WARD',  'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER',  7839, TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

1 row created.

SQL>
SQL>
SQL> create table upper_ename( x$ename, x$rid,primary key (x$ename,x$rid))
  2    organization index
  3    as
  4    select upper(ename), rowid from emp;


SQL>
SQL> create or replace trigger upper_ename
  2    after insert or update or delete on emp
  3    for each row
  4    begin
  5        if (updating and (:old.ename||'x' <> :new.ename||'x')) then
  6            delete from upper_ename
  7             where x$ename = upper(:old.ename)
  8               and x$rid = :old.rowid;
  9
 10            insert into upper_ename
 11            (x$ename,x$rid) values
 12            ( upper(:new.ename), :new.rowid );
 13        elsif (inserting) then
 14            insert into upper_ename
 15            (x$ename,x$rid) values
 16            ( upper(:new.ename), :new.rowid );
 17        elsif (deleting) then
 18            delete from upper_ename where x$ename = upper(:old.ename) and x$rid = :old.rowid;
 19        end if;
 20    end;
 21  /

Trigger created.

SQL>
SQL> update emp set ename = initcap(ename);

14 rows updated.

SQL>
SQL> select * from upper_ename;
X$ENAME    X$RID
---------- ------------------
ADAMS      AAAD3HAABAAAIF6AAK
ADAMS      AAAEJwAABAAAIF6AAK
ALLEN      AAAD3HAABAAAIF6AAB
ALLEN      AAAEJwAABAAAIF6AAB

X$ENAME    X$RID
---------- ------------------
BLAKE      AAAD3HAABAAAIF6AAF
BLAKE      AAAEJwAABAAAIF6AAF
CLARK      AAAD3HAABAAAIF6AAG
CLARK      AAAEJwAABAAAIF6AAG
FORD       AAAD3HAABAAAIF6AAM
FORD       AAAEJwAABAAAIF6AAM
JAMES      AAAD3HAABAAAIF6AAL
JAMES      AAAEJwAABAAAIF6AAL
JONES      AAAD3HAABAAAIF6AAD
JONES      AAAEJwAABAAAIF6AAD
KING       AAAD3HAABAAAIF6AAI
KING       AAAEJwAABAAAIF6AAI
MARTIN     AAAD3HAABAAAIF6AAE
MARTIN     AAAEJwAABAAAIF6AAE
MILLER     AAAD3HAABAAAIF6AAN
MILLER     AAAEJwAABAAAIF6AAN
SCOTT      AAAD3HAABAAAIF6AAH
SCOTT      AAAEJwAABAAAIF6AAH
SMITH      AAAD3HAABAAAIF6AAA
SMITH      AAAEJwAABAAAIF6AAA
TURNER     AAAD3HAABAAAIF6AAJ
TURNER     AAAEJwAABAAAIF6AAJ
WARD       AAAD3HAABAAAIF6AAC
WARD       AAAEJwAABAAAIF6AAC

28 rows selected.

SQL> drop table emp;

Table dropped.

SQL>
SQL> --

   
    
  








Related examples in the same category

1.create or replace trigger
2.Oracle's syntax for creating a trigger based on two tables
3.Trigger on each row
4.Use Sequence in a trigger
5.Empty trigger(before insert or update or delete)
6.This trigger sends messages over a pipe to record inserts into myStudent.
7.Cascade inserts into myStudent into session and lecturer.
8.Trigger is autonomous and hence the changes will be logged even if the original transaction rolls back.
9.Trigger Which Modifies a Mutating Table
10.Creating a Trigger with cursor inside
11.Autonumbering Trigger
12.Use RAISE_APPLICATION_ERROR in a trigger
13.Show errors for a trigger
14.Submit job from a trigger
15.Use sysdate and user function in a trigger