015-018

Previous

Table of Contents

Next

Furthermore, triggers can be written to fire either after or before a specified event occurs. A before insert row-level trigger fires once before each row is inserted into the associated table; if an INSERT statement creates six new rows in the table, the trigger will fire six times (once before each individual row is created).

All told, there are 12 types of database triggers, which are listed in Table 1.2.

Table 1.2 The twelve types of database triggers.

Trigger Type	Functionality
before delete statement level	Fires once before each DELETE statement affects the trigger's associated table, no matter how many rows are deleted from the table.
before delete row level	Fires once for each row affected by a DELETE statement, before each row is deleted.
after delete row level	Fires once for each row affected by a DELETE statement, after each row is deleted.
after delete statement level	Fires once after each DELETE statement that affects the trigger's associated table, no matter how many rows are deleted from the table.
before insert statement level	Fires once before each INSERT statement affects the trigger's associated table, no matter how many rows are inserted into the table.
before insert row level	Fires once for each row inserted into the table, before each row is inserted.
after insert row level	Fires once for each row inserted into the table, after each row is inserted.
after insert statement level	Fires once after each INSERT statement affects the trigger's associated table, no matter how many rows are inserted into the table.
before update statement level	Fires once before each UPDATE statement affects the trigger's associated table, no matter how many rows in the table are updated.
before update row level	Fires once for each row updated in the table, before each row is updated.
after update row level	Fires once for each row updated in the table, after each row is updated.
after update statement level	Fires once after each UPDATE statement that affects the trigger's associated table, no matter how many rows in the table are updated.

Triggers do not have to be triggered by a single type of DML statement on the table. It s quite common to combine the functions of any two or all three triggering DML statements inside the trigger. The most common example of this is a trigger that fires when a new row is created using the INSERT statement or when one or more rows is modified using the UPDATE statement.

Listing 1.14 displays a typical database trigger. In this example, the trigger attempts to validate a social security number when a new student is added. If the social security number cannot be validated , the trigger raises an exception and prevents the creation of a new record in the STUDENTS table. If the social security number is valid, the procedure guarantees that the student record has a financial aid ID and that the grade point average values for the new student don t contain any data.

Listing 1.14 A typical database trigger.

 CREATE OR REPLACE TRIGGER STUDENTS_BRI before INSERT on STUDENTS FOR each row IS    bValidSSN           boolean := TRUE;    xInvalid_SSN_Given  EXCEPTION; BEGIN    bValidSSN := Validate_SSN (:new.ssn);    IF (!bValidSSN) THEN       RAISE xInvalid_SSN_Given;    END IF;    :new.financing_num   := Finance_seq.NEXTVAL;    :new.overall_gpa     := NULL;    :new.most_recent_gpa := NULL; END; 

UPDATE triggers can be made to fire even more specifically through the use of a WHEN clause in the trigger definition, as shown in Listing 1.15.

Listing 1.15 An UPDATE trigger using a WHEN clause.

 CREATE OR REPLACE TRIGGER STUDENTS_BRU before UPDATE on STUDENTS FOR each row WHEN (overall_gpa > 4.0) IS    xInvalid_GPA     EXCEPTION; BEGIN    RAISE xInvalid_GPA; END; 

In Listing 1.15, the trigger uses a WHEN condition to prevent any update of a student s grade point average from exceeding 4.0. The trigger fires only when the new value of the overall_gpa column exceeds 4.0.

Previous

Table of Contents

Next