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.
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.
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.
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.
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
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.
|
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
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;
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;