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