Triggers


A trigger is a procedure that is run automatically by the database ”or in technical terms, fired ” when a specified SQL DML INSERT, UPDATE, or DELETE statement is run against a specified database table. Triggers are useful for doing things like advanced auditing of changes made to column values in a table.

When a Trigger Runs

A trigger can fire before or after the SQL statement runs. Also, since a DML statement can affect more than one row at the same time, the procedure code for the trigger may be run once for every row affected (such a trigger is known as a row-level trigger), or just once for all the rows (known as a statement-level trigger). For example, if you had an UPDATE statement that modified ten rows and you had also created a row trigger that would fire for this UPDATE statement, then that trigger would run ten times ”once for each row. If, however, your trigger was a statement-level trigger, the trigger would only fire once for the whole UPDATE statement.

Note  

A row-level trigger has access to the old and new column values when the trigger fires as a result of an UPDATE statement on that column.

The firing of a trigger may also be limited using a trigger condition, for example, when a column value is less than a specified value.

Set Up for the Example Trigger

Triggers are useful for doing advanced auditing of changes made to column values. In the next section, you ll see a trigger that records when a product s price is lowered by more than 25 percent. When this event occurs, the trigger will add a row to the product_price_audit table, which is created by the following CREATE TABLE statement in the store_schema.sql script:

 CREATE TABLE product_price_audit (   product_id INTEGER     CONSTRAINT price_audit_fk_products     REFERENCES products(product_id),   old_price NUMBER(5, 2),   new_price NUMBER(5, 2) ); 

As you can see, the product_id column of the product_price_audit table is a foreign key to the product_id column of the products table. The old_price column will be used to store the old price of a product prior to the change, and the new_price column will be used to store the new price after the change.

Creating a Trigger

You create a trigger using the CREATE TRIGGER statement. The simplified syntax for the CREATE TRIGGER statement is as follows :

 CREATE [OR REPLACE] TRIGGER  trigger_name  {BEFORE  AFTER  INSTEAD OF}  trigger_event  ON  table_name  [FOR EACH ROW [WHEN  trigger_condition  ]] BEGIN  trigger_body  END  trigger_name;  

where

  • OR REPLACE specifies the trigger is to replace an existing trigger if present. You can use this option when you want to modify the definition of a trigger.

  • trigger_name specifies the trigger name .

  • BEFORE specifies the trigger fires before the triggering event is performed. AFTER specifies the trigger fires after the triggering event is performed. INSTEAD OF specifies the trigger fires instead of performing the triggering event.

  • trigger_event specifies the event that causes the trigger to fire.

  • table_name specifies the table that the trigger references.

  • FOR EACH ROW specifies the trigger is a row-level trigger, which means the code contained within trigger_body is run for each row when the trigger fires. If you omit FOR EACH ROW , the trigger is a statement-level trigger, which means the code within trigger_body is run once when the trigger fires regardless of the number of rows affected.

  • trigger_condition specifies a Boolean condition that limits when a trigger actually runs its code.

  • trigger_body contains the SQL and PL/SQL statements that perform the trigger s task.

The example trigger you ll see in this section will fire before an update of the price column from the products table, and therefore I ll name the trigger before_product_price_update. Also, because I want to use the price column values before and after any UPDATE statement modifies the price column s value, I must use a row-level trigger. Finally, since I only want to audit a price change when the new price is lowered by more than 25 percent of the old value, I ll need to specify a trigger condition. The following statement creates the before_product_price_update trigger:

 CREATE OR REPLACE TRIGGER before_product_price_update BEFORE UPDATE OF price ON products FOR EACH ROW WHEN (new.price < old.price * 0.75) BEGIN   dbms_output.put_line('product_id = '  :old.product_id);   dbms_output.put_line('Old price = '  :old.price);   dbms_output.put_line('New price = '  :new.price);   dbms_output.put_line('The price reduction is more than 25%'); -- insert row into the product_price_audit table INSERT INTO product_price_audit (   product_id, old_price, new_price ) VALUES (   :old.product_id, :old.price, :new.price ); END before_product_price_update; / 

There are five things you should notice about this statement:

  • The BEFORE UPDATE OF clause specifies that the trigger is to fire before the update of the price column.

  • The FOR EACH ROW clause identifies this as a row-level trigger, which means the trigger code contained within the BEGIN and END keywords is to be run once for each row.

  • The trigger condition is (new.price < old.price * 0.75), which means the trigger code will only be run when the new price is less than 75 percent of the old price ”that is, when the price is reduced by more than 25 percent.

  • The new and old column values are accessed using the :old and :new aliases in the trigger.

  • The trigger code displays the product ID, the old and new prices, and a message stating that the price reduction is more than 25 percent. The code then adds a row to the product_price_audit table containing the product ID and the old and new prices.

Firing a Trigger

To fire the before_product_price_update trigger, you must reduce a product s price by more than 25 percent. Before you see that, the following example retrieves the product_id and price columns from the products table:

  SELECT product_id, price   FROM products   ORDER BY product_id;  ID      PRICE ---------- ----------          1      29.93          2         30          3      32.49          4      13.95          5      49.99          6      14.95          7      13.49          8      12.99          9      10.99         10      15.99         11      14.99         12      13.49 

To see the output from the trigger, you need to run the SET SERVEROUTPUT ON command:

  SET SERVEROUTPUT ON  

Go ahead and perform the following UPDATE statement to reduce the price of products #5 and #10 by 30 percent (this is achieved by multiplying the price column by .7). This UPDATE will cause the before_product_price_update trigger to fire:

  UPDATE products   SET price = price * .7   WHERE product_id IN (5, 10);  product_id = 10 Old price = 15.99 New price = 11.19 The price reduction is more than 25% product_id = 5 Old price = 49.99 New price = 34.99 The price reduction is more than 25% 2 rows updated. 

As you can see, the trigger fired for products #10 and #5. You can see that the trigger did indeed add the two required rows containing the product IDs, along with the old and new prices, to the product_price_audit table using the following query:

  SELECT *   FROM product_price_audit;  PRODUCT_ID  OLD_PRICE  NEW_PRICE ---------- ---------- ----------         10      15.99      11.19          5      49.99      34.99 

Getting Information on Triggers

Note  

You can get information on your triggers from the user_triggers view. Table 11-3 describes some of the columns in user_triggers.

Note  

You can get information on all the triggers you have access to using all_triggers.

Table 11-3: Some Columns in user_triggers

Column

Type

Description

TRIGGER_NAME

VARCHAR2(30)

Name of the trigger.

TRIGGER_TYPE

VARCHAR2(16)

Type of the trigger.

TRIGGERING_EVENT

VARCHAR2(227)

Event that causes the trigger to fire.

TABLE_OWNER

VARCHAR2(30)

User who owns the table that the trigger references.

BASE_OBJECT_TYPE

VARCHAR2(16)

Type of the object referenced by the trigger.

TABLE_NAME

VARCHAR2(30)

Name of the table referenced by the trigger.

COLUMN_NAME

VARCHAR2(4000)

Name of the column referenced by the trigger.

REFERENCING_NAMES

VARCHAR2(128)

Name of the old and new aliases.

WHEN_CLAUSE

VARCHAR2(4000)

Trigger condition that limits when the trigger runs its code.

STATUS

VARCHAR2(8)

Whether the trigger is enabled or disabled. Set to ENABLED or DISABLED .

DESCRIPTION

VARCHAR2(4000)

Description of trigger.

ACTION_TYPE

VARCHAR2(11)

Action type of the trigger. Set to CALL or PL/SQL .

TRIGGER_BODY

LONG

Code contained in the trigger body. The LONG type allows storage of large amounts of text. You'll learn about the LONG type in Chapter 14.

The following example retrieves the details of the before_product_price_update trigger from user_triggers :

  SELECT *   FROM user_triggers   WHERE trigger_name = 'BEFORE_PRODUCT_PRICE_UPDATE';  TRIGGER_NAME                   TRIGGER_TYPE ------------------------------ ---------------- TRIGGERING_EVENT ----------------------------------------------------------- TABLE_OWNER                    BASE_OBJECT_TYPE TABLE_NAME ------------------------------ ---------------- ----------- COLUMN_NAME ----------------------------------------------------------- REFERENCING_NAMES ----------------------------------------------------------- WHEN_CLAUSE ----------------------------------------------------------- STATUS -------- DESCRIPTION ----------------------------------------------------------- ACTION_TYPE ----------- TRIGGER_BODY ----------------------------------------------------------- BEFORE_PRODUCT_PRICE_UPDATE     BEFORE EACH ROW UPDATE STORE                           TABLE            PRODUCTS REFERENCING NEW AS NEW OLD AS OLD new.price < old.price * 0.75 ENABLED before_product_price_update BEFORE UPDATE OF   price ON   products FOR EACH ROW PL/SQL BEGIN   dbms_output.put_line('product_id = '  :old.product_id);   dbms_output 

Disabling and Enabling Trigger

You can stop a trigger from firing by disabling it using the ALTER TRIGGER statement. For example, the following statement disables the before_product_price_update trigger:

 ALTER TRIGGER before_product_price_update DISABLE; 

The following example enables the before_product_price_update trigger:

 ALTER TRIGGER before_product_price_update ENABLE; 

Dropping a Trigger

You drop a trigger using DROP TRIGGER . For example, the following statement drops the before_product_price_update trigger:

 DROP TRIGGER before_product_price_update; 



Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net