UNIX: Row-level triggers are adapted to statement-level triggers-workaround

Workaround for UNIX training: we might as well make a workaround. The row-level trigger is adapted to a statement-level trigger. Look at the following processing.

1. Create a package:emp_pkg.

CREATE Orreplace PACKAGE emp_pkg as

/* ----------------------------------

--author:kevin.yuan

--create_time:2008-07-01

---------------------------------- */

TYPE Crarray is TABLE of Emp%rowtype Indexby Binary_integer;

Oldrows Crarray; --accept the old valuesof emp

Oldempty Crarray; --initialize values

End emp_pkg;

2. Create a statement-level trigger that is used to trigger the trigger when the initialization data is emptied.

CREATE orreplace TRIGGER Emp_bd_st before DELETE on EMP

/*----------------------------------

Author:Kevin.yuan

Create_time:2008-07-01

---------------------------------- */

BEGIN

Emp_pkg.oldrows: = Emp_pkg.oldempty;

End Emp_bd_st;

3. Create a row-level trigger:emp_d, the core of the problem and the key right here, this trigger

Does not participate in the business logic, but only the data that is triggered is loaded into the Emp_pkg.oldrows record table.

CREATE orreplace TRIGGER emp_d after DELETE on EMP for each ROW

/* ----------------------------------

--author:kevin.yuan

--create_time:2008-07-01

---------------------------------- */

DECLARE

--CT is the position of the deleted records

CT INTEGER: = Emp_pkg.oldRows.COUNT + 1;

BEGIN

Emp_pkg.oldrows (CT). ename: =: old.ename;

Emp_pkg.oldrows (CT). Deptno: =: old.deptno;

End Emp_d;

4. Create a statement level Trigger:emp_d_st, the first 3 steps are for this step, this step

Truly participate in business logic processing.

CREATE orreplace TRIGGER Emp_d_st after DELETE on EMP

/* ----------------------------------

--author:kevin.yuan

--create_time:2008-07-01

---------------------------------- */

DECLARE

BEGIN

For I in 1. Emp_pkg.oldRows.COUNT LOOP

INSERT into Emp_log

(ename, dname, dates)

Select Emp_pkg.oldrows (i) ename, dname,sysdate

From Dept

WHERE deptno =emp_pkg.oldrows (i) deptno;

End LOOP;

End Emp_d_st;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/OS/unix/

Let's see if the flexible triggers meet our business requirements:

Sql> ALTER trigger EMP_DEL_TRG disable;

Trigger Altered

Remove EMP Data

sql> Delete from emp where rownum<3;

2 rows deleted

Sql> select * from Emp_log;

ename dname Dates

——————– ——————– ———–

SMITH 2008-7-1 19

ALLEN SALES 2008-7-1 19

sql> rollback;

Rollback Complete

Deletes dept data.

sql> Delete from dept;

4 rows deleted

Sql> select * from Emp_log;

ename dname Dates

——————– ——————– ———–

To this end, to achieve. Because more than the usual number of triggers, the system performance will have a certain impact, and, no doubt, will aggravate the burden of service maintenance later on, therefore, good database logic design and code writing ideas is very necessary, otherwise, can only go some other paths, but, The idea of a row-level trigger variable statement-level trigger is still necessary, and it is a solution that can be used as a reference when it is unavoidable in trigger to need access's own table.