Force all department names to uppercase in a trigger : Business Logic Trigger « Trigger « Oracle PL / SQL

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

Force all department names to uppercase in a trigger

 
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> --

 

Related examples in the same category