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> 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> drop table authors;
Table dropped.
SQL>