Mega Code Archive
Force all department names to uppercase in a trigger
<![CDATA[
SQL>
SQL> create table department
2 ( dept_id number(2),
3 dept_name varchar2(14),
4 no_of_emps varchar2(13)
5 )
6 /
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER department_insert_update
2 BEFORE INSERT OR UPDATE ON department
3 FOR EACH ROW
4 DECLARE
5 dup_flag INTEGER;
6 BEGIN
7
8 :NEW.dept_name := UPPER(:NEW.dept_name);
9 END;
10 /
Trigger created.
SQL>
SQL>
SQL> INSERT INTO department VALUES (10, 'a', 'NEW YORK');
1 row created.
SQL> INSERT INTO department VALUES (20, 'b', 'DALLAS');
1 row created.
SQL> INSERT INTO department VALUES (30, 'SALES', 'CHICAGO');
1 row created.
SQL> INSERT INTO department VALUES (40, 'OPERATIONS', 'BOSTON');
1 row created.
SQL>
SQL> select * from department;
DEPT_ID DEPT_NAME NO_OF_EMPS
---------- -------------- -------------
10 A NEW YORK
20 B DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected.
SQL>
SQL> drop table department;
Table dropped.
SQL>
SQL>
SQL> --
]]>