Call a trigger in procedure : Create Procedure « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Create Procedure

CREATE TABLE authors (        
  2    id         NUMBER PRIMARY KEY,
  3    first_name VARCHAR2(50),
  4    last_name  VARCHAR2(50)
  5  );

Table created.

SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (1, 'Marlene', 'Theriault');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (2, 'Rachel', 'Carmichael');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (3, 'James', 'Viscusi');

1 row created.

SQL>
SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> PROMPT

SQL> PROMPT ** Create an BEFORE UPDATE trigger on the AUTHORS table
** Create an BEFORE UPDATE trigger on the AUTHORS table
SQL> PROMPT

SQL>
SQL> CREATE OR REPLACE TRIGGER author_trig
  2     BEFORE UPDATE OF first_name
  3     ON authors
  4     FOR EACH ROW
  5  BEGIN
  6     DBMS_OUTPUT.PUT_LINE('First Name '
  7                          ||:OLD.first_name
  8                          ||' has change to '
  9                          ||:NEW.first_name);
 10  END;
 11  /

Trigger created.

SQL>
SQL> PROMPT

SQL> PROMPT ** Create a procedure that will cause the author_trig to fire
** Create a procedure that will cause the author_trig to fire
SQL> PROMPT

SQL>
SQL> CREATE OR REPLACE PROCEDURE author_first_name_upd (
  2     i_author_id IN AUTHORS.ID%TYPE,
  3     i_first_name IN AUTHORS.FIRST_NAME%TYPE)
  4  IS
  5  BEGIN
  6
  7     UPDATE authors a
  8     SET a.first_name = UPPER(i_first_name)
  9     WHERE a.id = i_author_id;
 10
 11  EXCEPTION
 12     WHEN OTHERS
 13     THEN
 14        DBMS_OUTPUT.PUT_LINE(sqlerrm);
 15  END;
 16  /

Procedure created.

SQL>
SQL> SET ESCAPE OFF
SQL>
SQL>
SQL> drop table authors;

Table dropped.

SQL>
SQL>