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. 


Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net