1.13. Database Triggers Database triggers are named program units that are executed in response to events that occur in the database. Five different types of events can have trigger code attached to them:
Data Manipulation Language (DML) statements DML triggers are available to fire whenever a record is inserted into, updated in, or deleted from a table. These triggers can be used to perform validation, set default values, audit changes, and even disallow certain DML operations.
Data Definition Language (DDL) statements DDL triggers fire whenever DDL is executedfor example, whenever a table is created. These triggers can perform auditing and prevent certain DDL statements from occurring.
Database events Database event triggers fire whenever the database starts up or is shut down, whenever a user logs on or off, and whenever an Oracle error occurs. For Oracle8i Database and above, these triggers provide a means of tracking activity in the database.
INSTEAD OF INSTEAD OF triggers are essentially alternatives to DML triggers . They fire when inserts, updates, and deletes are about to occur; your code specifies what to do in place of these DML operations. INSTEAD OF triggers control operations on views, not tables. They can be used to make non-updateable views updateable and to override the behavior of views that are updateable. These triggers are not covered further in this section, as they are a specialized topic that requires thorough coverage to be useful.
1.13.1. DML Triggers Data Manipulation Language (DML) triggers fire when records are inserted into, updated within, or deleted from a particular table. These are the most common type of triggers, especially for developers; the other trigger types are used primarily by DBAs. There are many options regarding DML triggers. They can fire after or before a DML statement or they can fire after or before each row is processed within a statement. They can fire for INSERT, UPDATE, or DELETE statements, or combinations of the three. 1.13.1.1. Transaction participation By default, DML triggers participate in the transaction from which they were fired. This means that: If a trigger raises an exception, that part of the transaction will be rolled back. If the trigger performs any DML itself (such as inserting a row into a log table), then that DML becomes a part of the main transaction. You cannot issue a COMMIT or ROLLBACK from within a DML trigger.
 | If you define your DML trigger to be an autonomous transaction, however, then any DML performed inside the trigger will be saved or rolled backwith your explicit COMMIT or ROLLBACK statementwithout affecting the main transaction. |
|
The following sections present the syntax for creating a DML trigger, provide reference information on various elements of the trigger definition, and explore an example that uses the many components and options for these triggers. 1.13.1.2. Creating a DML trigger To create (or replace) a DML trigger, use the syntax shown here: 1 CREATE [OR REPLACE] TRIGGER trigger name 2 {BEFORE | AFTER} 3 {INSERT | DELETE | UPDATE | UPDATE OF column list} ON table name 4 [FOR EACH ROW] 5 [WHEN (...)] 6 [DECLARE ... ] 7 BEGIN 8 ... executable statements ... 9 [EXCEPTION ... ] 10 END [trigger name];
The following table provides an explanation of these different elements: Line(s) | Description |
|---|
1 | States that a trigger is to be created with the name supplied. Specifying OR REPLACE is optional. If the trigger exists and REPLACE is not specified, then your attempt to create the trigger anew will result in an ORA-4081 error. It is possible, by the way, for a table and a trigger (or procedure and trigger, for that matter) to have the same name. We recommend, however, that you adopt naming conventions to avoid the confusion that will result from this sharing of names. | 2 | Specifies if the trigger is to fire BEFORE or AFTER the statement or row is processed. | 3 | Specifies the type of DML to which the trigger applies: INSERT, UPDATE, or DELETE. Note that UPDATE can be specified for the whole record or just for a column list separated by commas. The columns can be combined (separated with an OR) and may be specified in any order. Line 3 also specifies the table to which the trigger is to apply. Remember that each DML trigger can apply to only one table. | 4 | If FOR EACH ROW is specified, then the trigger will activate for each row processed by a statement. If this clause is missing, the default behavior is to fire only once for the statement (a statement-level trigger). | 5 | An optional WHEN clause that allows you to specify logic to avoid unnecessary execution of the trigger. | 6 | Optional declaration section for the anonymous block that constitutes the trigger code. If you do not need to declare local variables, you do not need this keyword. Note that you should never try to declare the NEW and OLD pseudo-records. This is done automatically. | 78 | The execution section of the trigger. This is required and must contain at least one statement. | 9 | Optional exception section. This section will trap and handle (or attempt to handle) any exceptions raised in the execution section only. | 10 | Required END statement for the trigger. You can include the name of the trigger after the END keyword to explicitly document which trigger you are ending. |
Here are a few examples of DML trigger usage: I want to make sure that whenever an employee is added or changed, all necessary validation is run. Notice that I pass the necessary fields of the NEW pseudo-record to individual check routines in this row-level trigger: CREATE OR REPLACE TRIGGER validate_employee_changes AFTER INSERT OR UPDATE ON employee FOR EACH ROW BEGIN check_age (:NEW.date_of_birth); check_resume (:NEW.resume); END;
The following BEFORE INSERT trigger captures audit information for the CEO compensation table. It also relies on the Oracle8i Database autonomous transaction feature to commit this new row without affecting the "outer" or main transaction: CREATE OR REPLACE TRIGGER bef_ins_ceo_comp AFTER INSERT ON ceo_compensation FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO ceo_comp_history VALUES (:NEW.name, :OLD.compensation, :NEW.compensation, 'AFTER INSERT', SYSDATE); COMMIT; END;
1.13.1.3. The WHEN clause Use the WHEN clause to fine-tune the situations under which the body of the trigger code will actually execute. In the following example, I use the WHEN clause to make sure that the trigger code does not execute unless the new salary is changing to a different value: CREATE OR REPLACE TRIGGER check_raise AFTER UPDATE OF salary ON employee FOR EACH ROW WHEN (OLD.salary != NEW.salary) OR (OLD.salary IS NULL AND NEW.salary IS NOT NULL) OR (OLD.salary IS NOT NULL AND NEW.salary IS NULL) BEGIN ...
In other words, if a user issues an UPDATE to a row and for some reason sets the salary to its current value, the trigger will and must fire, but the reality is that you really don't need any of the PL/SQL code in the body of the trigger to execute. By checking this condition in the WHEN clause, you avoid some of the overhead of starting up the PL/SQL block associated with the trigger. | The genwhen.sp file on the book's web site offers a procedure that will generate a WHEN clause to ensure that the new value is actually different from the old. |
|
In most cases, you will reference fields in the OLD and NEW pseudo-records in the WHEN clause, as in the example shown above. You may also, however, write code that invokes built-in functions, as in the following WHEN clause that uses SYSDATE to restrict the INSERT trigger to only fire between 9 A.M. and 5 P.M.: CREATE OR REPLACE TRIGGER valid_when_clause BEFORE INSERT ON frame FOR EACH ROW WHEN ( TO_CHAR(SYSDATE,'HH24') BETWEEN 9 AND 17 ) ...
1.13.1.4. Working with NEW and OLD pseudo -records Whenever a row-level trigger fires, the PL/SQL runtime engine creates and populates two data structures that function much like records. They are the NEW and OLD pseudo-records ("pseudo" because they don't share all the properties of real PL/SQL records). OLD stores the original values of the record being processed by the trigger; NEW contains the new values. These records have the same structure as a record declared using %ROWTYPE on the table to which the trigger is attached. Here are some rules to keep in mind when working with NEW and OLD: With triggers on INSERT operations, the OLD structure does not contain any data; there is no "old" set of values. With triggers on UPDATE operations, both the OLD and NEW structures are populated. OLD contains the values prior to the update; NEW contains the values the row will contain after the update is performed. With triggers on DELETE operations, the NEW structure does not contain any data; the record is about to be erased. You cannot change the field values of the OLD structure; attempting to do so will raise the ORA-04085 error . You can modify the field values of the NEW structure. You cannot pass a NEW or OLD structure as a record parameter to a procedure or function called within the trigger. You can pass only individual fields of the pseudo-record. See the gentrigrec.sp script on the book's web site for a program that will generate code transferring NEW and OLD values to records that can be passed as parameters. When referencing the NEW and OLD structures within the anonymous block for the trigger, you must preface those keywords with a colon, as in: IF :NEW.salary > 10000 THEN...
You cannot perform record-level operations with the NEW and OLD structures. For example, the following statement will cause the trigger compilation to fail: BEGIN :new := NULL; END;
1.13.1.5. Determining the DML action within a trigger Oracle offers a set of functions (also known as operational directives ) that allow you to determine which DML action caused the firing of the current trigger. Each of these functions returns TRUE or FALSE, as described below.
INSERTING Returns TRUE if the trigger was fired by an insert into the table to which the trigger is attached, and FALSE if not.
UPDATING Returns TRUE if the trigger was fired by an update of the table to which the trigger is attached, and FALSE if not.
DELETING Returns TRUE if the trigger was fired by a delete from the table to which the trigger is attached, and FALSE if not.
Using these directives, it is possible to create a single trigger that consolidates the actions required for each of the different types of operations. 1.13.2. DDL Triggers Oracle allows you to define triggers that will fire when Data Definition Language (DDL) statements are executed. Simply put, DDL is any SQL statement used to create or modify a database object such as a table or an index. Here are some examples of DDL statements: CREATE TABLE ALTER INDEX DROP TRIGGER
Each of these statements results in the creation, alteration, or removal of a database object. The syntax for creating these triggers is remarkably similar to that of DML triggers, except that the firing events differ and they are not applied to individual tables. 1.13.2.1. Creating a DDL Trigger To create (or replace) a DDL trigger, use the syntax shown here: 1 CREATE [OR REPLACE] TRIGGER trigger name 2 {BEFORE | AFTER } {DDL event} ON {DATABASE | SCHEMA} 3 [WHEN (...)] 4 DECLARE 5 Variable declarations 6 BEGIN 7 ... some code... 8 END;
The following table summarizes what is happening in this code: Line(s) | Description |
|---|
1 | Specifies that a trigger is to be created with the name supplied. Specifying OR REPLACE is optional. If the trigger exists and REPLACE is not specified, then good old Oracle error ORA-4081 will appear stating just that. | 2 | This line has a lot to say. It defines whether the trigger will fire before, after, or instead of the particular DDL event, as well as whether it will fire for all operations within the database or just within the current schema. Note that the INSTEAD OF option is available only in Oracle9i Release 1 and higher. | 3 | An optional WHEN clause that allows you to specify logic to avoid unnecessary execution of the trigger. | 47 | These lines simply demonstrate the PL/SQL contents of the trigger. |
Here's an example of a somewhat uninformed town crier trigger that announces the creation of all objects: /* File on web: uninformed_town_crier.sql */ SQL> CREATE OR REPLACE TRIGGER town_crier 2 AFTER CREATE ON SCHEMA 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('I believe you have created something!'); 5 END; 6 / Trigger created.
1.13.3. Database Event Triggers Database event triggers fire whenever database-wide events occur. There are five database event triggers:
STARTUP Fires when the database is opened
SHUTDOWN Fires when the database is shut down normally
SERVERERROR Fires when an Oracle error is raised
LOGON Fires when an Oracle session begins
LOGOFF Fires when an Oracle session terminates normally
As any DBA will immediately see, these triggers offer stunning possibilities for automated administration and very granular control. 1.13.3.1. Creating a database event trigger The syntax used to create these triggers is quite similar to that used for DDL triggers : 1 CREATE [OR REPLACE] TRIGGER trigger name 2 {BEFORE | AFTER} {database event} ON {DATABASE | SCHEMA} 3 DECLARE 4 Variable declarations 5 BEGIN 6 ... some code... 7 END;
There are restrictions regarding what events can be combined with what BEFORE and AFTER attributes. Some situations just don't make sense:
No BEFORE STARTUP triggers Even if such triggers could be created, when would they fire? Attempts to create triggers of this type will be met by this straightforward error message: ORA-30500: database open triggers and server error triggers cannot have BEFORE type
No AFTER SHUTDOWN triggers Again, when would they fire? Attempts to create such triggers are deflected with this message: ORA-30501: instance shutdown triggers cannot have AFTER type
No BEFORE LOGON triggers It would require some amazingly perceptive code to implement these triggers: "Wait, I think someone is going to log ondo something!" Being strictly reality-based, Oracle stops these triggers with this message: ORA-30508: client logon triggers cannot have BEFORE type
No AFTER LOGOFF triggers "No wait, please come back! Don't sign off!" Attempts to create such triggers are stopped with this message: ORA-30509: client logoff triggers cannot have AFTER type
No BEFORE SERVERERROR These triggers would be every programmer's dream! Think of the possibilities... CREATE OR REPLACE TRIGGER BEFORE_SERVERERROR BEFORE SERVERERROR ON DATABASE BEGIN diagnose_impending_error; fix_error_condition; continue_as_if_nothing_happened; END;
Unfortunately, our dreams are shattered by this error message: ORA-30500: database open triggers and server error triggers cannot have BEFORE type
|
