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 when creating triggers for your tables.

The next part of this lesson explores some uses for database triggers.

Validating Column Values with a Trigger

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. Listing 11.26 illustrates how to create this trigger.

Listing 11.26. Creating a before insert trigger.

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, initialize the contents of the Credit_Charge_Log table with several rows, as shown in Listing 11.27.

Listing 11.27. Contents of the Credit_Charge_Log table before the next row is inserted.

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. If they do, a row is added to the Credit_Charge_Attempt_Log table, as you can see in Listing 11.28 (see line 1). Because more than $1,000 in charges have been made on card number 0944583312453477 in the past three days, the trigger inserts a row into Credit_Charge_Attempt_Log.

Listing 11.28. Trigger fires when row is inserted into Credit_Charge_Log table.

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.
SQL> select * from Credit_Charge_Attempt_Log;
CARD_NUMBER         AMOUNT VENDOR_I TRANSACTI
----------------- -------- -------- ---------
0944583312453477    128.28 43214321 20-JUN-95

Enforcing Security with a Trigger

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 Shipment table has a column, Manual_Check, that 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. As shown in Listing 11.29, 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 only fires after 5:00 P.M.(or 17:00 using a 24-hour clock).

Listing 11.29. Creating a trigger that must satisfy a condition before firing.

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 Listing 11.30, the current time is later than 5:00 p.m.—it is actually 7:00 p.m. When a row is inserted into the Shipment table, the Manual_Check column is set to Y as intended.

Listing 11.30. Trigger checks time and fires.

SQL> select to_char(sysdate,'HH24') from dual;
TO_CHAR(SYSDATE,'HH24')
------------------------------------------------------------------
19

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

Setting Column Values with a Trigger

Another use for a trigger is to set a column to a particular value before an 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. Listing 11.31 illustrates how the trigger is created.

Listing 11.31. Setting a column value in a trigger.

create or replace trigger Block_Trade_Log_BI before
insert on Block_Trade_Log
for each row

declare
Running_Avg number;

begin

select avg(price)
into Running_Avg
from Block_Trade_Log

                                                     continues