Page 316
Listing 11.31. continued
where Stock_Symbol = :new.Stock_Symbol and Timestamp >= SYSDATE-3; :new.Running_Avg_3_Days := Running_Avg; end; /
Notice that the value of Running_Avg_3_Days is set by assigning the value to :new.Running_Avg_3_Days. Remember: If the triggering event is an INSERT, the column values that are actually stored in the table are referenced with :new.
Listing 11.32 displays the contents of the Block_Trade_Log table. Notice the two rows for stock symbol QQQQQ: one at $102.125 and the other at $103.5. When another row for stock symbol QQQQQ is inserted into Block_Trade_Log, the trigger fires and computes the three-day running average for that security—102.8125—and assigns it to the column Running_Avg_3_Days.
Listing 11.32. Firing the trigger with an INSERT statement.
SQL> select * from block_trade_log; STOCK_ PRICE BLOCKS_TRADED B RUNNING_AVG_3_DAYS TIMESTAMP ------ --------- ------------- - ------------------ --------- QQQQQ 102.125 100 B 19-JUN-95 QQQQQ 103.5 100 S 19-JUN-95 VVVVV 55.75 3000 S 19-JUN-95 VVVVV 55.5 1000 B 20-JUN-95 SQL> insert into block_trade_log 2 (Stock_Symbol, Price, Blocks_Traded, Bought_Sold, Timestamp) 3 values 4 (`&stock',&price,&numblocks,'&BS','&date') 5 ; Enter value for stock: QQQQQ Enter value for price: 104.25 Enter value for numblocks: 300 Enter value for bs: B Enter value for date: 20-JUN-95 old 4: (`&stock',&price,&numblocks,'&BS','&date') new 4: (`QQQQQ',104.25,300,'B','20-JUN-95') 1 row created. SQL> select * from block_trade_log;
Page 317
STOCK_ PRICE BLOCKS_TRADED B RUNNING_AVG_3_DAYS TIMESTAMP ------ --------- ------------- - ------------------ --------- QQQQQ 102.125 100 B 19-JUN-95 QQQQQ 103.5 100 S 19-JUN-95 VVVVV 55.75 3000 S 19-JUN-95 VVVVV 55.5 1000 B 20-JUN-95 QQQQQ 104.25 300 B 102.8125 20-JUN-95
The interaction of triggers can be quite complex. For example, you can create a trigger that, when fired, causes another trigger to fire. Triggers that behave in this way are called cascading triggers. To illustrate the concept of cascading triggers, look at three simple tables in Listing 11.33—tab1, tab2, and tab3. Initially, each table has a single row.
Listing 11.33. Firing the trigger with an INSERT statement.
create table tab1
(col1 number);
create table tab2
(col2 number);
create table tab3
(col3 number);
SQL> select * from tab1;
COL1
---------
7
SQL> select * from tab2;
COL2
---------
10
SQL> select * from tab3;
COL3
---------
13
For table tab1, create a row-level BEFORE UPDATE trigger that will insert the old value of the col1 column from tab1 into tab2, as shown in Listing 11.34. For table tab2, create a row-level BEFORE INSERT trigger that updates table tab3 and sets the value of col3 to the new value of col2. Finally, for table tab3, create a statement-level AFTER UPDATE trigger that inserts a row into tab3 with the value of col3 equal to 27.
Page 318
Listing 11.34. Creating a trigger that will cause another trigger to fire.
SQL> create or replace trigger tab1_Update_Before before 2 update on tab1 3 for each row 4 4 declare 5 5 begin 6 6 insert into tab2 7 (col2) 8 values 9 (:old.col1); 10 10 end; 11 / Trigger created. SQL> create or replace trigger tab2_Insert_Before before 2 insert on tab2 3 for each row 4 4 declare 5 5 begin 6 6 update tab3 7 set 8 col3 = :new.col2; 9 9 end; 10 / Trigger created. SQL> create or replace trigger tab3_Update_After after 2 update on tab3 3 3 declare 4 4 begin 5 5 insert into tab3 6 (col3) 7 values 8 (27); 9 9 end; 10 / Trigger created.
Page 319
| NOTE |
A table is mutating when its contents are being changed by an INSERT, UPDATE, or DELETE statement that hasn't yet committed. A row-level trigger cannot read or modify the contents of a mutating table because a mutating table is in a state of flux. The only exception to this rule is that a BEFORE INSERT row-level trigger for a table with a foreign key may modify columns in the table containing the primary key. For more information about mutating tables, please refer to Chapter 8 of the Oracle7 Server Application Developer's Guide, available online. If you have loaded the Oracle documentation on your Windows 95 PC, you'll find this document in C:\Orawin95\Doc\A32536_1.PDF; it is a PDF (Adobe Portable Document Format) file that can be read using the Adobe Acrobat Reader. |
Now, what will happen when a row in tab1 is updated? As you can see in Listing 11.35, the following changes have taken place:
Listing 11.35. Testing the cascading triggers.
SQL> update tab1
2 set col1 = 8;
1 row updated.
SQL> select * from tab1;
COL1
--------
8
SQL> select * from tab2;
COL2
--------
10
7
SQL> select * from tab3;
COL3
--------
7
27
Page 320
| TIP |
By default, the number of cascaded triggers that can fire is limited to 32. However, keep this in mind—your ability to understand the ramifications of an INSERT, UPDATE, or DELETE statement is inversely proportional to the number of cascading triggers associated with that SQL statement. In other words, keep it straightforward. |
You cannot execute a COMMIT or ROLLBACK statement in a database trigger.
NEW TERM
Rollback is the process of rescinding or undoing all database changes made by a user since the last COMMIT was issued or since the beginning of the database session.
Also, a trigger may not call a stored procedure, function, or package subprogram that performs a COMMIT or ROLLBACK. Oracle maintains this restriction for a good reason. If a trigger encounters an error, all database changes that have been propagated by the trigger should be rolled back. But if the trigger committed some portion of those database changes, Oracle would not be able to roll back the entire transaction.
You can call a stored procedure or function, whether standalone or part of a package, from the PL/SQL body of a database trigger. As an example, here is a version of the trigger Block_Trade_Log_BI, originally shown in Listing 11.31, that calls the stored function Get_3_Day_Running_Avg. The trigger is based on the Block_Trade_Log table previously discussed in this lesson. Listing 11.36 contains the stored function that the trigger will reference.
Listing 11.36. Creating the stored function to be called by a trigger.
SQL> create or replace function Get_3_Day_Running_Avg 2 (Stock_Symb in varchar2) 3 return number is 4 4 Running_Avg number; 5 5 begin 6