The use of a trigger : Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Trigger

SQL>
SQL> set feedback on
SQL> set pages 9999 serveroutput on size 1000000
SQL>
SQL> CREATE TABLE authors (
  2    id         NUMBER PRIMARY KEY,
  3    first_name VARCHAR2(50),
  4    last_name  VARCHAR2(50)
  5  );

Table created.

SQL>
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> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (4, 'Michael', 'Abbey');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (5, 'Michael', 'Corey');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (6, 'Scott', 'Urman');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (7, 'Ron', 'Hardman');

1 row created.

SQL>
SQL> INSERT INTO authors (id, first_name, last_name)
  2    VALUES (8, 'Mike', 'McLaughlin');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL>
SQL>
SQL> SELECT id, first_name, last_name
  2  FROM authors
  3  WHERE last_name = 'HARDMAN';

no rows selected

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

Trigger created.

SQL>
SQL> UPDATE authors
  2  SET first_name = 'Ronald'
  3  WHERE first_name = 'Ron';
First Name Ron has change to Ronald

1 row updated.

SQL>
SQL>
SQL>
SQL> drop table authors;

Table dropped.