 |
|
Oracle Tips by Burleson |
Triggers
Here is another example that maintains a history
of changes to a table. Updates and deletes are copied to a history
table using a trigger
.
This trigger takes advantage of the triggers ability to determine
which event was executed. Using this capability, the trigger can
execute different sections of code based on the even that executed.
The three test
conditions are INSERTING, UPDATING and DELETING. The test will be
TRUE for the event that executed the trigger.
if (updating)
then…
To see this capability in action, first create the
sales_history table to hold the old rows from the sales
table. This table will have the same columns as the sales
table plus a column for the change date and a column for the change
method.
SQL> CREATE
TABLE SALES_HISTORY
2 ( CHG_DATE DATE
3 , CHG_TYPE VARCHAR2(6)
4 , STORE_KEY VARCHAR2(4)
5 , BOOK_KEY VARCHAR2(6)
6 , ORDER_NUMBER VARCHAR2(20)
7 , ORDER_DATE DATE
8 , QUANTITY NUMBER(5))
9 /
Table
created.
Now create a trigger that will
capture the table changes and place the old data into the
sales_history table.
SQL> create
or replace trigger sales_history_tr
2 before update or delete on sales
3 for each row
4 begin
5 if (updating) then
6 insert into sales_history
7 ( CHG_DATE
8 , CHG_TYPE
9 , STORE_KEY
10 , BOOK_KEY
11 , ORDER_NUMBER
12 , ORDER_DATE
13 , QUANTITY)
14 values
15 ( sysdate
16 , 'UPDATE'
17 , :old.STORE_KEY
18 , :old.BOOK_KEY
19 , :old.ORDER_NUMBER
20 , :old.ORDER_DATE
21 , :old.QUANTITY);
22 else
23 insert into sales_history
24 ( CHG_DATE
25 , CHG_TYPE
26 , STORE_KEY
27 , BOOK_KEY
28 , ORDER_NUMBER
29 , ORDER_DATE
30 , QUANTITY)
31 values
32 ( sysdate
33 , 'DELETE'
34 , :old.STORE_KEY
35 , :old.BOOK_KEY
36 , :old.ORDER_NUMBER
37 , :old.ORDER_DATE
38 , :old.QUANTITY);
39 end if;
40 end; /
The above example uses the :OLD specification to
capture the values in the row before it is changed and save them to
the sales_history table,
along with the change date/time and the change type (update or
delete). Notice the condition test at line 5 to determine if the
event was an update or a delete.
Sometimes the :OLD and :NEW reference is
confusing. You can redefine these names using the referencing
clause. The example below replaces the standard notation with early
and late
SQL> create
or replace trigger sales_history2_tr
2 before update or delete on sales
3 referencing old as early new as late
4 for each row
5 begin
6 insert into sales_history
7 ( CHG_DATE,
8 CHG_TYPE,
9 STORE_KEY,
10 BOOK_KEY,
11 ORDER_NUMBER,
12 ORDER_DATE,
13 QUANTITY)
14 values
15 ( sysdate,
16 'UPDATE',
17 :early.STORE_KEY,
18 :early.BOOK_KEY,
19 :early.ORDER_NUMBER,
20 :early.ORDER_DATE,
21 :early.QUANTITY);
22 end;
23 /
Trigger
created.
Often the default values are defined in the
referencing clause for clarity.
create or
replace trigger sales_history2_tr
before update or delete on sales
referencing old as old new as new
for each row
begin
…
A trigger
does not need to perform an action
on the object that fired the event. Below is a trigger that fires
after an insert on a table and executes a procedure in a package.
SQL> create
or replace trigger do_something_tr
2 after insert on book_author
3 begin
4 ref_cursor_pak.sales_row_count;
5 end;
6 /
Trigger
created.
This trigger
executes the procedure
sales_row_count from the
ref_cursor_pak example after
each insert into the book_author
tables.
SQL> insert
into book_author values
2 ('A101','B110',.15);
Number of rows: 0
First Order Number: O101
First Order Number: O102
Number of rows: 2
Cursor Closed
1 row
created.
In the example, it appears that the trigger fired
before the row was created. That is not the case. The results appear
that way because of the manner that SQL*Plus retrieved the results
from the buffer. Because the trigger executes after the insert event,
if the insert fails, the trigger will not fire.
SQL> insert
into book_author values
2 ('A101','B110');
insert into book_author values
*
ERROR at line 1:
ORA-00947: not enough values
Notice that the trigger did not fire
because the row insertion failed.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
