Mega Code Archive
Define trigger to force all department names to uppercase
<![CDATA[
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
1 row created.
SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
1 row created.
SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
1 row created.
SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> -- Example of a trigger.
SQL> CREATE OR REPLACE TRIGGER department_insert_update
2 BEFORE INSERT OR UPDATE ON dept
3 FOR EACH ROW
4 DECLARE
5 dup_flag INTEGER;
6 BEGIN
7 --Force all department names to uppercase.
8 :NEW.dname := UPPER(:NEW.dname);
9 END;
10 /
Trigger created.
SQL>
SQL> -- Testing the department_insert_update trigger.
SQL> INSERT INTO dept (deptno, dname) VALUES (10,'payroll');
1 row created.
SQL>
SQL> INSERT INTO dept (deptno, dname) VALUES (11,'Sewage');
1 row created.
SQL>
SQL> UPDATE dept SET dname = 'Payroll' WHERE deptno = 10;
2 rows updated.
SQL>
SQL> SELECT deptno, dname FROM dept WHERE deptno BETWEEN 10 AND 11;
DEPTNO DNAME
---------- --------------
10 PAYROLL
10 PAYROLL
11 SEWAGE
SQL>
SQL>
SQL>
SQL>
SQL> drop table dept;
Table dropped.
SQL>
SQL>
SQL>
SQL>
SQL>
]]>