Define trigger to force all department names to uppercase : Business Logic Trigger « Trigger « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Trigger
  3. Business Logic Trigger

Define trigger to force all department names to uppercase


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>








Related examples in the same category

1.A trigger restricting updates
2.Trigger to check inserting value
3.Force all department names to uppercase in a trigger
4.Trigger to check the employee count per department
5.A Trigger to check the available room