7.4. Processing Row Captured Data
Row triggers can store :OLD and :NEW column values in a global temporary table. The scope of a global temporary table is just that transaction. By copying :OLD and :NEW values, the processing of the business rule can be deferred to the statement level trigger. Sometimes this is necessary because the business rule is complex and requires queries from tables, including the table being updated.
The following illustrates the general technique. First a global temporary table is needed. This table will be used to store data in the row level trigger.
CREATE global temporary TABLE professors_g (prof_name VARCHAR2(10), specialty VARCHAR2(20), hire_date DATE, salary NUMBER(7,2), tenure VARCHAR2(3), department VARCHAR2(10)) ON COMMIT DELETE ROWS;
The next step is to code procedures in the constraints package for this table. These procedures will be added to the PROFESSORS_CONS package. Showing just the additions for the package specification:
CREATE OR REPLACE PACKAGE professors_cons IS
PROCEDURE load_temp_table
(v_prof_name professors.prof_name%TYPE,
v_specialty professors.specialty%TYPE,
v_hire_date professors.hire_date%TYPE,
v_salary professors.salary%TYPE,
v_tenure professors.tenure%TYPE,
v_department professors.department%TYPE);
PROCEDURE dump_temp_table;
END professors_cons;
The package body is:
CREATE OR REPLACE PACKAGE BODY professors_cons IS
PROCEDURE load_temp_table
(v_prof_name professors.prof_name%TYPE,
v_specialty professors.specialty%TYPE,
v_hire_date professors.hire_date%TYPE,
v_salary professors.salary%TYPE,
v_tenure professors.tenure%TYPE,
v_department professors.department%TYPE)
IS
BEGIN
INSERT INTO professors_g VALUES
(v_prof_name, v_specialty, v_hire_date,
v_salary, v_tenure, v_department);
END load_temp_table;
PROCEDURE dump_temp_table IS
BEGIN
FOR rec in (SELECT * FROM professors_g) LOOP
dbms_output.put_line(
rec.prof_name||' '||rec.specialty||' '||
rec.hire_date||' '||rec.salary||' '||
rec.tenure||' '||rec.department);
END LOOP;
END dump_temp_table;
END professors_cons;
The following is a AFTER DELETE ROW trigger. When this trigger fires it only inserts row data in the temporary table through the PROFESSORS_CONS package.
CREATE OR REPLACE TRIGGER professors_adr
AFTER DELETE ON professors
FOR EACH ROW
BEGIN
professors_cons.load_temp_table
(:old.prof_name, :old.specialty, :old.hire_date,
:old.salary, :old.tenure, :old.department);
END;
The next trigger is an AFTER DELETE STATEMENT trigger that uses the constraints package to print the rows deleted. Although this demonstration merely prints the data, in some circumstances this can be useful to a statement level trigger. Statement level triggers have no knowledge of the rows affected by the SQL statement. They have no knowledge of :OLD and :NEW values.
The AFTER DELETE statement trigger is:
CREATE OR REPLACE TRIGGER professors_ads
AFTER DELETE ON professors
BEGIN
professors_cons.dump_temp_table;
END;
The DELETE SQL statement is followed by the output from the statement trigger.
SQL> DELETE FROM professors; Blake Mathematics 08-aug-2003 02:06:27 10000 YES MATH Milton Am Hist 09-aug-2003 02:06:27 10000 YES HIST Wilson English 06-aug-2003 02:06:27 10000 YES ENGL Jones Euro Hist 12-jul-2003 02:06:28 10000 YES HIST Crump Ancient Hist 12-jul-2003 02:06:28 10000 YES HIST 5 rows deleted.