In a traditional information system, the business rules of the organization served by the system are implemented in application software. For instance, one business rule might be that if the inventory on hand for a part falls below the stocking level for that part, an order for the required quantity is entered into the system. You would commonly enforce this rule by writing a routine in COBOL or some other programming language that is invoked at an appropriate point in the application. However, this method presents several problems:
Oracle, along with other modern RDBMSs, provides a mechanism--;the database trigger--;that eases the task of implementing an organization's business rules. A database trigger is a block of PL/SQL statements that are executed when a SQL statement--;a DELETE, UPDATE, or INSERT statement--;is applied to a table. You can use a database trigger to perform the following tasks:
This chapter examines the details of creating triggers for various purposes.
You'll want to use a text editor to write your triggers. The Oracle statement CREATE TRIGGER creates (or replaces) a trigger that is fired when a specified event occurs on a table. The syntax for the CREATE TRIGGER statement is
CREATE [OR REPLACE] TRIGGER trigger-name {BEFORE | AFTER}
triggering-event ON table-name
[FOR EACH ROW]
[WHEN (condition)]
PL/SQL-block
where the variables are defined as follows: trigger-name is the name of the trigger to create and is subject to Oracle object-naming restrictions.
triggering-event is either INSERT, UPDATE, or DELETE corresponding to the three DML statements.
table-name is the name of the table with which the trigger is associated.
FOR EACH ROW is an optional clause that, when used, causes the trigger to fire for each affected row.
condition is an optional Oracle Boolean condition that, when TRUE, enables the trigger to fire.
PL/SQL-block is the PL/SQL block that is executed when the trigger fires--;referred to as the trigger body.
The following sections discuss the use of these CREATE TRIGGER statement elements.
A database trigger fits in one of the following two classifications:
A statement-level trigger fires only once for the triggering event and does not have access to the column values of each row that the trigger affects. A row-level trigger fires for each row that the trigger affects and can access the original and new column values processed by the SQL statement.
You generally use a statement-level trigger to process information about the SQL statement that caused the trigger to fire--;for instance, who executed it and when. You typically use a row-level trigger when you need to know the column values of a row to implement a business rule.
Within the trigger body, a row-level trigger can reference the column values of the row that existed when the trigger was fired. These values depend on which SQL statement caused the trigger to fire.
When you create a trigger, you specify which event will cause the trigger to fire. The three possible events are
In addition, you can combine these triggering events so that a trigger fires whenever a DELETE or INSERT or UPDATE statement is executed, as shown in the following code:
SQL> create or replace trigger Block_Trade_After_All After 2 insert or update or delete on Tab1 3 for each row 4 4 declare 5 5 begin 6 6 insert into Tab11 7 (col11) 8 values 9 (11); 10 10 end; 11 / Trigger created.
A BEFORE row-level trigger is fired before the triggering event is executed. Therefore, you can use a BEFORE row-level trigger to modify a row's column values. An AFTER row-level trigger fires after the triggering event has occurred. You can't modify column values with an AFTER trigger.
Based on all of the permutations you can use in the CREATE TRIGGER statement, a single table can have up to 12 types of triggers.
TipIf you're considering the use of an entity-relationship modeling tool for database design--;which you should--;you'll find that most of them will automatically generate database triggers based on the primary and foreign key relationships you define. Some tools, such as ERwin from Logic Works, will either create the triggers directly via an Oracle connection or store the triggers in a script file. If you choose the latter method, you can modify the trigger creation script by adding application-specific business rules to any triggers that have been generated.
Just because you can create all 12 types of triggers for a table doesn't mean that you must! In fact, you should be judicious in creating triggers for your tables.
NoteOracle 7.1 supports multiple triggers of the same type on the same table. In contrast, Oracle 7.0 installations that used snapshots couldn't create AFTER ROW triggers for the master table because the snapshot logs used AFTER ROW triggers on the same table. The restriction was removed for Oracle 7.1. However, unless you're planning to use an AFTER ROW trigger for a table referenced by a snapshot, you should avoid defining multiple triggers of the same type for a given table--;the potential for design error and confusion is too great.
The next part of this chapter explores some uses for database triggers.
As the DBA for a credit card company, you are responsible for implementing credit policy via database triggers. Company research has shown that the probability of credit card fraud is greater than 80 percent when more than $1,000 in credit charges have accumulated on a single account within three days. The director of operations wants to record any account that meets this criteria in a separate table where it can be investigated in detail.
To accomplish this task, you create a trigger on the Credit_Charge_Log table that fires before a row is inserted. The trigger looks at the total amount of charges for the specified card number for the past three days, and if the total exceeds $1,000, it performs an INSERT in the Credit_Charge_Attempt_Log table where the record will be investigated by credit agents. Here is the CREATE TRIGGER statement for this trigger: ***Production: Please make sure the hyphens that mark the comment lines in the code below do not become em dashes. Thanks.***
SQL> create or replace trigger Credit_Charge_Log_Ins_Before before
2 insert on Credit_Charge_Log
3 for each row
4
4 declare
5
5 total_for_past_3_days number;
6
6 begin
7
7 -- Check the credit charges for the past 3 days.
8 -- If they total more than $1000.00, log this entry
9 -- in the Credit_Charge_Attempt_Log for further handling.
10
10 select sum(amount)
11 into total_for_past_3_days
12 from Credit_Charge_Log
13 where
14 Card_Number = :new.Card_Number and
15 Transaction_Date >= sysdate-3;
16
16 if total_for_past_3_days > 1000.00 then
17
17 insert into Credit_Charge_Attempt_Log
18 (Card_Number, Amount, Vendor_ID, Transaction_Date)
19 values
20 (:new.Card_Number, :new.Amount,
:new.Vendor_ID, :new.Transaction_Date);
21
21 end if;
22
22 end;
23 /
Trigger created.
To set up the trigger so that it will fire, I've initialized the contents of the Credit_Charge_Log table with several rows.
SQL> select * from credit_charge_log; CARD_NUMBER AMOUNT VENDOR_I TRANSACTI ---------------- --------- -------- --------- 8343124443239383 128.33 12345678 19-JUN-95 9453128834232243 83.12 98765432 18-JUN-95 4644732212887321 431.1 18181818 19-JUN-95 0944583312453477 211.94 09090909 18-JUN-95 0944583312453477 413.81 08080808 18-JUN-95 0944583312453477 455.31 91919191 19-JUN-95 0944583312453477 225 12341234 20-JUN-95 0944583312453477 512.22 12341234 20-JUN-95 8 rows selected.
Before a row is inserted into the table for card number 0944583312453477, the trigger is fired. It queries the table to see if the charges for that card number for the past three days exceed $1,000.
SQL> insert into Credit_Charge_Log
2 (Card_Number, Amount, Vendor_ID, Transaction_Date)
3 values
4 ('0944583312453477', 128.28, '43214321', '20-JUN-95');
1 row created.
As you see, more than $1,000 in charges have been made on card number 0944583312453477 in the past three days, so the trigger inserts a row into Credit_Charge_Attempt_Log.
SQL> select * from Credit_Charge_Attempt_Log; CARD_NUMBER AMOUNT VENDOR_I TRANSACTI ---------------- --------- -------- --------- 0944583312453477 128.28 43214321 20-JUN-95
Here's an example of how you can use a database trigger to enforce a security policy. Acme Corporation's database is designed so that a row must be inserted into the Shipment table for an actual shipment to be made. The Manual_Check column indicates whether a shipping clerk should verify by phone the accuracy of the shipping request. To reduce the likelihood of fraud, corporate policy is that a shipping clerk should check any shipping request that has been entered after normal working hours--;5:00 p.m.
As the DBA, you are responsible for implementing this policy. You create a trigger, Shipment_Ins_Before, that will fire before the execution of an INSERT statement on the Shipment table. The trigger body consists of a single PL/SQL statement--;the assignment of Y to the column Manual_Check. In addition, you decide to use a WHEN clause so that the trigger fires only after 5:00 p.m.(or 17:00 using a 24-hour clock).
SQL> create or replace trigger Shipment_Ins_Before before 2 insert on Shipment 3 for each row 4 when (to_number(to_char(sysdate,'HH24')) > 17) 5 5 declare 6 6 begin 7 7 :new.Manual_Check := 'Y'; 8 8 end; 9 / Trigger created.
Now that the trigger has been created, you can test it. As you can see in the following code, the current time is later than 7:00 p.m.
SQL> select to_char(sysdate,'HH24') from dual; TO_CHAR(SYSDATE,'HH24') ------------------------------------------------------------------- 19
When a row is inserted into the Shipment table, the Manual_Check column is set to Y as intended.
SQL> insert into Shipment
2 (Shipment_ID, Product_Code, Quantity, Customer_ID)
3 values
4 ('SHIP1001', 'PROD123', 100, 'CUST999');
1 row created.
SQL> select * from Shipment;
SHIPMENT_ID PRODUCT_CODE QUANTITY CUSTOMER_ID M ENTERED_BY
------------ ------------ --------- ------------ - ---------------------
SHIP1001 PROD123 100 CUST999 Y
Another use for a trigger is to set a column to a particular value before a SQL statement takes effect. The following scenario demonstrates this process. Suppose a table named Block_Trade_Log is used to record block trading on the NASDAQ. The table contains the following: the stock symbol, the trading price, the number of blocks that were traded, when the trade occurred, whether the blocks were bought or sold, and the three-day running average for the stock. When a row is inserted into the table, a trigger is used to set the value for Running_Avg_3_Days. Here is the statement that creates the trigger:
create or replace trigger Block_Trade_Log_Ins_Before before insert on Block_Trade_Log for each row declare Running_Avg number; begin select avg(price) into Running_Avg from Block_Trade_Log 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 will actually be stored in the table are referenced with :new.
Here's a look at the current contents of Block_Trade_Log. Notice the two rows for stock symbol QQQQQ: one at $102.125 and the other at $103.5.
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
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, as shown in the following code:
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;
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
NoteThe following example illustrates how you can see the effect of a trigger before you commit the transaction that causes the trigger to fire. The trigger Repair_Header_Delete_After is invoked when Repair ID 506 is deleted from the Repair_Header table. As you can see in the following code, a row is inserted into the Repair_Header_Log table before any COMMIT is issued:
SQL> delete from Repair_Header
2 where
3 Repair_ID = 506;
1 row deleted.
SQL> select * from Repair_Header_Log;
REPAIR_ID CUSTOMER_ID DATE_DELE USER_DELETING
--------- ----------- --------- ------------------------------
506 1010 20-JUN-95 FRAYED_WIRES
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, I've create three simple tables.
create table tab1 (col1 number); create table tab2 (col2 number); create table tab3
(col3 number);
To initialize the three tables, you can insert a single row in each table.
SQL> select * from tab1;
COL1
---------
7
SQL> select * from tab2;
COL2
---------
10
SQL> select * from tab3;
COL3
---------
13
For table tab1, you can create a row-level BEFORE UPDATE trigger that will insert the old value of the col1 column from tab1 into tab2.
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.
For table tab2, you can create a row-level BEFORE INSERT trigger that updates table tab3 and sets the value of col3 to the new value of col2.
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.
NoteA table is mutating when its contents are being changed by a INSERT, UPDATE, or DELETE statement that has not 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.
Finally, for table tab3, you can create a statement-level AFTER UPDATE trigger that inserts a row into tab3 with the value of col3 equal to 27.
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.
Now, the moment of truth--; what happens when a row in tab1 is updated?
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
As you can see in the preceding code, the following changes have occurred:
TipBy default, the number of cascaded triggers that can fire is limited to 32. That number is controlled by the Oracle initialization parameter max_open_cursors, which can be changed. 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. 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. Here is an example of the error that Oracle returns if a trigger contains a COMMIT:
SQL> create or replace trigger Shipment_Upd_After before 2 Update on Shipment 3 for each row 4 4 declare 5 5 begin 6 6 :new.Manual_Check := 'N'; 7 7 commit; 8 8 end; 9 / Trigger created. SQL> update Shipment 2 set Quantity = 100; update Shipment * ERROR at line 1: ORA-04092: cannot COMMIT in a trigger ORA-06512: at line 2 ORA-04088: error during execution of trigger 'FRAYED_WIRES.SHIPMENT_UPD_AFTER'
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, I've rewritten the trigger Block_Trade_Log_Ins_Before so that it calls the stored function Get_3_Day_Running_Avg. The trigger is based on the Block_Trade_Log table previously discussed in this chapter. The trigger references this stored function:
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 6 select avg(price) 7 into Running_Avg 8 from Block_Trade_Log 9 where 10 Stock_Symbol = Stock_Symb and 11 Timestamp >= SYSDATE-3; 12 12 return Running_Avg; 13 13 end; 14 / Function created.
Here is the modified version of Block_Trade_Log_Ins_Before that calls the stored function Get_3_Day_Running_Avg:
SQL> create or replace trigger Block_Trade_Log_Ins_Before before 2 insert on Block_Trade_Log 3 for each row 4 4 declare 5 5 Running_Avg number; 6 6 begin 7 7 :new.Running_Avg_3_Days := Get_3_Day_Running_Avg (:new.Stock_Symbol); 8 8 end; 9 /
Trigger created.
Another use for a trigger is to disallow a SQL statement or transaction by raising an exception. In this example, I create a row-level AFTER INSERT trigger and declare the exception Too_Many_Blocks. If the value of Blocks_Traded in the inserted row is greater than 1,000,000, the exception is raised. In the exception section, an exception handler for the user-defined exception passes back an Oracle error and message.
SQL> create or replace trigger Block_Trade_Log_Ins_After after
2 insert on Block_Trade_Log
3 for each row
4
4 declare
5
5 Too_Many_Blocks exception;
6
6 begin
7
7 if :new.Blocks_Traded > 1000000 then
8 raise Too_Many_Blocks;
9 end if;
10
10
10 exception
11
11 when Too_Many_Blocks then
12 raise_application_error (-20001,
'You cannot trade more than one million blocks');
13
13 end;
14 /
Trigger created.
For example, if you insert a row into Block_Trade_Log in which Blocks_Traded is equal to 1,000, the row is inserted successfully.
SQL> insert into Block_Trade_Log
2 (Stock_Symbol, Price, Blocks_Traded, Timestamp)
3 values
4 ('AXAXA', 104.25, 1000, SYSDATE);
1 row created.
However, if you try to insert a row into Block_Trade_Log in which Blocks_Traded is equal to 1,000,001, the exception is raised and the Oracle error returned to the caller--;in this case, SQL*Plus.
SQL> insert into Block_Trade_Log
2 (Stock_Symbol, Price, Blocks_Traded, Timestamp)
3 values
4 ('AXAXA', 103.25, 1000001, SYSDATE);
insert into Block_Trade_Log
*
ERROR at line 1:
ORA-20001: You cannot trade more than one million blocks
ORA-06512: at line 9
ORA-04088: error during execution of trigger
'FRAYED_WIRES.BLOCK_TRADE_LOG_INS_AFTER'
If you've decided that you absolutely don't want a particular trigger, you can drop it with the statement
DROP TRIGGER trigger-name;
where trigger-name is the name of the trigger to be dropped.
For example, to drop the DELETE AFTER trigger on the Repair_Header table, issue the following statement via SQL*Plus.
SQL> drop trigger Repair_Header_Delete_After; Trigger dropped.
Sometimes, dropping a trigger is too drastic. Instead, you might want to deactivate a trigger temporarily. You can disable a trigger until enabling it again makes sense. To do disable a trigger temporarily, use the ALTER TRIGGER statement
ALTER TRIGGER trigger-name DISABLE;where trigger-name is the trigger to disable.
The following example disables the trigger Repair_Header_Delete_After.
SQL> alter trigger Repair_Header_Delete_After disable;Trigger altered.To enable a disabled trigger, use the statement
ALTER TRIGGER trigger-name ENABLE;where trigger-name is the trigger to enable.
For instance, you can enable Repair_Header_Delete_After by issuing the following command:
SQL> alter trigger Repair_Header_Delete_After enable; Trigger altered.
Oracle provides a data dictionary view named DBA_TRIGGERS that you can query to obtain information about any existing database triggers. DBA_TRIGGERS consists of the following columns:
SQL> desc dba_triggers Name Null? Type ------------------------------- -------- ---- OWNER NOT NULL VARCHAR2(30) TRIGGER_NAME NOT NULL VARCHAR2(30) TRIGGER_TYPE VARCHAR2(16) TRIGGERING_EVENT VARCHAR2(26) TABLE_OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) REFERENCING_NAMES VARCHAR2(87) WHEN_CLAUSE VARCHAR2(2000) STATUS VARCHAR2(8) DESCRIPTION VARCHAR2(2000) TRIGGER_BODY LONG
For example, if you wanted to see all the triggers that were created by Oracle user FRAYED_WIRES, you could use the following query:
SQL> select trigger_name, trigger_type, triggering_event, status 2 from dba_triggers 3 where 4 owner = 'FRAYED_WIRES' 5 order by trigger_name, triggering_event, trigger_type; TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT STATUS ------------------------------ ---------------- ------------------- -------- BLOCK_TRADE_LOG_INS_BEFORE BEFORE EACH ROW INSERT ENABLED REPAIR_HEADER_DELETE_AFTER AFTER EACH ROW DELETE ENABLED SHIPMENT_INS_BEFORE BEFORE EACH ROW INSERT ENABLED SHIPMENT_UPD_AFTER BEFORE EACH ROW UPDATE ENABLED TAB1_UPDATE_BEFORE BEFORE EACH ROW UPDATE ENABLED TAB2_INSERT_BEFORE BEFORE EACH ROW INSERT ENABLED TAB3_UPDATE_BEFORE AFTER STATEMENT UPDATE ENABLED
You can also query DBA_TRIGGERS to see the actual trigger body that executes when the trigger fires.
SQL> select trigger_body 2 from dba_triggers 3 where 4 trigger_name = 'BLOCK_TRADE_LOG_INS_BEFORE'; TRIGGER_BODY ------------------------------------------------------------------------- declare Running_Avg number := 0; begin select avg(price) into Running_Avg from Block_Trade_Log where Stock_Symbol = :new.Stock_Symbol and Timestamp >= SYSDATE-3; :new.Running_Avg_3_Days := Running_Avg; end;
The main ideas in this chapter include the following: