Another Look at Triggers


In Chapter 4, "Working with Databases and Database Objects," we saw that a trigger is used to define a set of actions that are to be executed whenever an insert, update, or delete operation is performed against a table or updatable view. Like constraints, triggers are often used to enforce data integrity and business rules. Unlike constraints, triggers can also be used to update other tables, automatically generate or transform values for inserted or updated rows, and invoke functions to perform tasks such as issuing errors or alerts.

Before a trigger can be created, the following components must be identified:

  • Subject table/view: The table or view that the trigger is to interact with.

  • Trigger event: An SQL operation that causes the trigger to be activated whenever it is performed against the subject table/view. This operation can be an insert operation, an update operation, or a delete operation.

  • Trigger activation time: Indicates whether the trigger should be activated before, after, or instead of the trigger event. A BEFORE trigger will be activated before the trigger event occurs; therefore, it will be able to see new data values before they are inserted into the subject table. An AFTER trigger will be activated after the trigger event occurs; therefore, it can see only data values that have already been inserted into the subject table. An INSTEAD OF trigger will replace the trigger event made against the subject view. (A BEFORE trigger might be used to trap and process unwanted values, while an AFTER trigger could be used to copy data values entered to other tables or views.)

  • Set of affected rows: The rows of the subject table/view that are being inserted, updated, or deleted.

  • Trigger granularity: Specifies whether the actions the trigger will perform are to be performed once for the entire insert, update, or delete operation or once for every row affected by the insert, update, or delete operation.

  • Triggered action: An optional search condition and a set of SQL statements that are to be executed whenever the trigger is activated. (If a search condition is specified, the SQL statements will only be executed if the search condition evaluates to TRUE.) If the trigger is a BEFORE trigger, the triggered action can include statements that retrieve data, set transition variables, or signal SQL states. If the trigger is an AFTER trigger, the triggered action can include statements that retrieve data, insert records, update records, delete records, or signal SQL states.

Triggered actions can refer to the values in the set of affected rows using what are known as transition variables. Transition variables use the names of the columns in the subject table, qualified by a specified name that indicates whether the reference is to the original value (before the insert, update, or delete operation is performed) or the new value (after the insert, update, or delete operation is performed). Another means of referring to values in the set of affected rows is through the use of transition tables. Transition tables also use the names of the columns in the subject table, but they allow the complete set of affected rows to be treated as a table. Transition tables can only be used in after triggers.

Once the appropriate trigger components have been identified, a trigger can be created by executing the CREATE TRIGGER SQL statement. The basic syntax for this statement is:

 CREATE TRIGGER [TriggerName] [<NO CASCADE> BEFORE | AFTER | INSTEAD OF] [INSERT | DELETE | UPDATE <OF [ColumnName], ... >] ON [TableName | ViewName] <REFERENCING [Reference]> [FOR EACH ROW | FOR EACH STATEMENT] <WHEN ([SearchCondition])> [TriggeredAction] 

where:

TriggerName

Identifies the name to be assigned to the trigger to be created.

ColumnName

Identifies one or more columns in the subject table or view of the trigger whose values must be updated before the trigger's triggered action (TriggeredAction) will be executed.

TableName

Identifies, by name, the subject table of the BEFORE or AFTER trigger to be created.

ViewName

Identifies, by name, the subject view of the INSTEAD OF trigger to be created.

Reference

Identifies one or more transition variables and/or transition tables that are to be used by the trigger's triggered action (TriggeredAction). The syntax used to create transition variables and/or transition tables that are to be used by the trigger's triggered action is:

 <OLD <AS> [CorrelationName]> <NEW <AS> [CorrelationName]> <OLD TABLE <AS> [Identifier]> <NEW TABLE <AS> [Identifier]> 

 

where:

 

CorrelationName

Identifies a name to be used to identify a specific row in the subject table of the trigger, either before it was modified by the trigger's triggered action (OLD <AS>) or after it has been modified by the trigger's triggered action (NEW <AS>).

 

Identifier

Identifies a name that is to be used to identify a temporary table that contains a set of rows found in the subject table of the trigger, either before they were modified by the trigger's triggered action (OLD TABLE <AS>) or after they have been modified by the trigger's triggered action (NEW TABLE <AS>).

 

Each column affected by an activation event (insert, update, or delete operation) can be made available to the trigger's triggered action by qualifying the column's name with the appropriate correlation name or table identifier.

SearchCondition

Specifies a search condition that, when evaluated, will return either TRUE, FALSE, or Unknown. This condition is used to determine whether the trigger's triggered action (TriggeredAction) is to be performed.

TriggeredAction

Identifies the action to be performed when the trigger is activated. The triggered action must consist of one or more SQL statements; when multiple statements are specified, the first statement must be preceded by the keywords BEGIN ATOMIC, the last statement must be followed by the keyword END, and every statement between these keywords must be terminated with a semicolon (;).

Thus, if you wanted to create a trigger for a table named EMPLOYEES that will increase the SALARY of the row being updated by 10%, you could do so by executing a CREATE TRIGGER statement that looks something like this:

 CREATE TRIGGER pay_raise NO CASCADE BEFORE UPDATE ON employees FOR EACH ROW SET new.salary = salary * 1.1 

Or, if you wanted to allow users to add records to a table named ACTIVITY_HISTORY, but prevent them from updating or deleting records once they have been stored in the table, you could do so by executing two CREATE TRIGGER statements that look something like this:

 CREATE TRIGGER block_updates NO CASCADE BEFORE UPDATE ON activity_history FOR EACH ROW SIGNAL SQLSTATE '75001'   SET MESSAGE_TEXT = 'Updates not allowed!' CREATE TRIGGER block_deletes NO CASCADE BEFORE DELETE ON activity_history FOR EACH ROW SIGNAL SQLSTATE '75002'   SET MESSAGE_TEXT = 'Deletes not allowed!' 

Notice that in this case, two triggers had to be created: one that handles UPDATE events and another that handles DELETE events for the ACTIVITY_HISTORY table. If necessary, several different triggers can be created for a single table. For example, consider the following scenario: Suppose you have a table named SALES, which has the following characteristics:

Open table as spreadsheet

Column Name

Data Type

INVOICE

INTEGER

SALE_DATE

DATE

SALE_AMT

DECIMAL(6,2)

SHIP_DATE

DATE

BILL_DATE

DATE

Business rules dictate that any time a record is inserted into this table, the current date is to be recorded as the sale date, a shipping date is to be scheduled three days out from the date of sale (i.e., the current date), and billing is to take place thirty days from the date of sale. To adhere to these business rules, you could create three different AFTER triggers by executing the following set of CREATE TRIGGER statements:

 CREATE TRIGGER trigger_a AFTER INSERT ON sales REFERENCING NEW AS new FOR EACH ROW UPDATE sales SET sale_date = CURRENT DATE   WHERE invoice = n.invoice CREATE TRIGGER trigger_b AFTER INSERT ON sales REFERENCING NEW AS new FOR EACH ROW UPDATE sales SET ship_date = CURRENT DATE + 3 DAYS   WHERE invoice = n.invoice CREATE TRIGGER trigger_c AFTER INSERT ON sales REFERENCING NEW AS new FOR EACH ROW UPDATE sales SET bill_date = CURRENT DATE + 30 DAYS   WHERE invoice = n.invoice 

Since triggers are executed in the order in which they have been created, each time a new record is inserted into the SALES table, trigger TRIGGER_A will fire, then trigger TRIGGER_B will fire, and finally, trigger TRIGGER_C will fire. If, for some reason, one trigger fails, the others will not be affected.




DB2 9 Fundamentals Certification Study Guide
DB2 9 Fundamentals: Certification Study Guide
ISBN: 1583470727
EAN: 2147483647
Year: 2007
Pages: 93

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