13.1 How do I…Create a database trigger? Problem
I need to create database triggers on the tables used by my application. I also want to use triggers to enforce business rules, manage redundancy, and perform cascading deletes. I want to create triggers that execute once when a statement is executed or for each row modified in the table. How do I create a database trigger on a table?
Technique
The CREATE TRIGGER statement is used to create a database trigger on a table. Each trigger is given a name and can be programmed to execute before or after an event on a table. The syntax used to create a trigger is as follows:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} triggering_event ON table_name
[REFERENCING {NEW AS|OLD AS} qualifier name]
[FOR EACH ROW]
[WHEN (expression)]
[DECLARE
Optional declarative section contains all local types,
variables, constants, cursors and subprogram declarations.]
BEGIN
Executable section of the trigger.
[EXCEPTION
Optional exception section to handle errors.]
END;
The triggering_event following the BEFORE or AFTER keyword can be an INSERT, UPDATE, or DELETE event or a combination of any of these three events. Any combination of triggering events can be included in the same database trigger. The name of each trigger must be unique within its schema.
There are two types of triggers: statement triggers and row triggers. A statement trigger executes only once for the triggering statement, irrespective of the number of rows affected. A row trigger is created by specifying the FOR EACH ROW option, which causes the trigger to fire once to each row created, modified, or deleted by the triggering statement. If the option is not included, the trigger is considered to be a statement trigger. The possible trigger types that can be created on a table are shown in Table 13.1.
Table 13.1 Possible trigger types allowed on a given table
An optional WHEN clause can be used to restrict the records for which a row trigger fires. The WHEN clause contains a Boolean expression, which is evaluated for each row that is affected by the trigger. If the expression evaluates to FALSE for a given row, the trigger body is not executed for that row. The optional REFERENCING clause can be used to avoid name conflicts with the NEW and OLD qualifiers.
Name Function BEFORE INSERT Fires once before an INSERT statement BEFORE INSERT FOR EACH ROW Fires before each new record is created AFTER INSERT Fires once after an INSERT statement AFTER INSERT FOR EACH ROW Fires after each new record is created BEFORE UPDATE Fires once before an UPDATE statement BEFORE UPDATE FOR EACH ROW Fires before each record is updated AFTER UPDATE Fires once after an UPDATE statement AFTER UPDATE FOR EACH ROW Fires after each record is updated BEFORE DELETE Fires once before a DELETE statement BEFORE DELETE FOR EACH ROW Fires before each record is deleted AFTER DELETE Fires once after a DELETE statement AFTER DELETE FOR EACH ROW Fires after each record is deleted Steps
1. Run SQL*Plus and connect as the WAITE user account. Run CHP13_1.SQL, as shown in Figure 13.1, to create a sample table displaying the triggers created in this How-To.
The sample table, DEPT13, is a simple table with three columns. In the steps that follow, triggers will be created on the table and the capabilities of triggers will be demonstrated.
2. Run the CHP13_2.SQL file in SQL*Plus. The CREATE TRIGGER statement contained in the file creates a simple trigger that fires before a record is created. The results of the operation are shown in Figure 13.2.
The CREATE OR REPLACE TRIGGER keywords create the trigger with the specified trigger name that must be unique within the schema. Because the CREATE OR REPLACE keywords are used, another trigger with the same name in the schema will be replaced, regardless of the table the trigger is on. However, a trigger name can have the same name as that of a table or procedure.
Line 2 specifies the triggering statement with the BEFORE INSERT ON clause, which makes the trigger fire after the INSERT operation takes place on the DEPT13 table. Lines 3 and 4 present the syntax used to declare variables for database triggers, even though the variable created is not used. Unlike stored procedures, the DECLARE statement is used at the beginning of the declarative section, when creating database triggers. Lines 5 through 7 contain the executable section of the trigger. The only executable line in the trigger displays a message that the trigger has fired, by using the PUT_LINE procedure of the DBMS_OUTPUT package.
The trigger is automatically enabled when created. Any new data added to the table after the trigger is created will make it fire. The next step creates a trigger that only fires when the data meets certain criteria.
3. Run the CHP13_3.SQL file in SQL*Plus, as shown in Figure 13.3. The CREATE TRIGGER statement contained in the file creates a trigger that fires after new records are inserted into the table. It also includes a WHEN clause restricting when the trigger is fired.
Line 1 is the CREATE TRIGGER statement to create the new trigger. Line 2 specifies the triggering event and the trigger will fire after a new record is inserted. The FOR EACH ROW clause in line 3 ensures the trigger will fire once for each new row created. Line 4 specifies a WHEN clause making the trigger fire only when the DEPT_NO column of the new record is greater than three. The NEW represents the new record inserted into the table. The expression in the WHEN clause must evaluate to a Boolean TRUE or FALSE. Lines 5 through 7 contain the trigger body. The PUT_LINE procedure from the DBMS_OUTPUT package shown in line 6 will display a message when the trigger is fired.
Now two triggers are on the sample table. The first trigger will fire any time a new record is inserted into the table. The trigger just created will only fire when the DEPT_NO column is greater than three, and only after the record has been successfully inserted. The next step creates a trigger that fires when records in the table are updated.
4. Run the CHP13_4.SQL in SQL*Plus, as shown in Figure 13.4. The CREATE TRIGGER statement contained in the file creates a trigger that executes when the DEPT_NAME column in the DEPT13 table is updated.
Line 1 is the CREATE TRIGGER clause. Line 2 specifies the triggering event. The trigger fires whenever the DEPT_NAME column is updated but after the record is updated. If the OF DEPT_NAME clause is not on the trigger, the trigger fires when any table column is updated. The FOR EACH ROW clause in line 3 causes the trigger to execute once for each record updated. Lines 4 through 6 present a trigger body displaying a message when the trigger fires.
5. Run the CHP13_5.SQL in SQL*Plus, as shown in Figure 13.5. The CREATE TRIGGER statement contained in the file creates a trigger that executes after a DELETE statement has executed successfully.
Line 1 creates the trigger with the name DEPT_AD_13. Line 2 specifies that the trigger will execute after the DELETE statement has run. The absence of a FOR EACH ROW clause means the statement will fire only once when the DELETE statement is executed, regardless of the number of rows deleted. Lines 3 through 5 contain the trigger body. The statement contained in line 4 displays a message when the trigger fires.
The sample table created in Step 1 now contains four database triggers. In the following steps, the data in the tables will be manipulated to make the triggers fire.
6. Run the CHP13_6.SQL in SQL*Plus. The SERVEROUTPUT SQL*Plus variable is set to ON to display the output from the triggers within SQL*Plus. The INSERT statement contained in the file inserts a record into the sample table. Figure 13.6 displays the results of the operation.
The INSERT statement creates a new record with a DEPT_NO value of 5. The BEFORE INSERT trigger will fire, but the AFTER INSERT statement will not, due to the WHEN clause contained in the statement.
One message is displayed because only the BEFORE INSERT trigger fires. The WHEN clause in the AFTER INSERT trigger prevents it from firing, because the value of the DEPT_NO column is not greater than 5. The next step creates a record that does not prevent the AFTER INSERT trigger from firing.
7. Run the CHP13_7.SQL file in SQL*Plus. The INSERT statement contained in the file creates a new record which has a DEPT_NO column greater than 5. Because the record created will have a DEPT_NO column value greater than 5, both the BEFORE INSERT and AFTER INSERT triggers will fire. Figure 13.7 shows the results in SQL*Plus.
Both the BEFORE INSERT and AFTER INSERT triggers are fired because the value of the DEPT_NO column does not prevent the AFTER INSERT trigger from firing. In the next step, an UPDATE statement will be used to fire the BEFORE UPDATE trigger.
8. Run the CHP13_8.SQL file in SQL*Plus. The UPDATE statement contained in the file updates each of the records in the table. The UPDATE statement will make the AFTER UPDATE trigger fire for each revised row, because the DEPT_NAME column is updated. The AFTER UPDATE OF DEPT_NAME clause in the CREATE TRIGGER statement in Step 6 only lets the trigger fire when the DEPT_NAME column is modified. Modifying other columns without the DEPT_NAME column will not fire the trigger. Figure 13.8 shows how the UPDATE statement causes the triggers to fire.
Because four rows were updated by the statement, the AFTER UPDATE trigger was fired four times. Had the FOR EACH ROW clause not been specified in the CREATE TRIGGER statement, the trigger would have fired only once. In the next step, all records will be deleted to show the operation of the AFTER DELETE trigger presented in Step 8.
9. Execute a DELETE statement in CHP13_9.SQL to delete all the rows in the table and then drop the table. Figure 13.9 shows the operation and the output generated by the AFTER DELETE trigger.
Because the AFTER DELETE trigger does not contain the FOR EACH ROW option, the trigger was only executed once by the DELETE statement. All four underlying triggers created in this How-To are dropped along with the table.
How It Works
The CREATE TRIGGER statement is used to create database triggers on a table. Up to 12 triggers can be created on a single table. Table 13.1 shows the list of triggers that can be created.
Step 1 creates a sample table used throughout this How-To as the table for the new triggers. Step 2 creates a BEFORE INSERT trigger, which executes before a new row is inserted in the table. Step 3 creates an AFTER INSERT trigger, which includes the FOR EACH ROW clause and a WHEN clause. The WHEN clause can only be used on triggers containing the FOR EACH ROW clause and restricts firing the trigger to rows satisfying a Boolean condition. Step 4 creates an AFTER UPDATE trigger, which fires for each modified row but only when a particular column is updated. Step 5 creates an AFTER DELETE statement, which only fires once for each transaction. The absence of the FOR EACH ROW clause causes the trigger to fire only once per transaction.
The remaining steps demonstrate the execution of the database triggers by executing INSERT, UPDATE, and DELETE statements on the table. The table on which the row trigger is defined is called as a mutating table. An ORA-04091 error occurs if a row trigger (but not statement trigger) attempts to query or modify a mutating table that was in the middle of being modified by the statement that fired the trigger.
Comments
Use triggers when you need to guarantee that certain actions are performed when tables are modified. Avoid using triggers to perform operations that other Oracle features can execute. For example, referential integrity can be enforced using primary and foreign key constraints defined on the tables, but triggers come in handy if your needs are more complex, such as DELETE and UPDATE cascades. Oracle corporation recommends that trigger should be 60 lines or less, and if the trigger requires more than 60 lines of code, then it is better to pack the code in a separate stored procedure, and call the stored procedure from the trigger. Exception handling in triggers is the same as with stored procedures. Transaction control statements like COMMIT, ROLLBACK, and SAVEPOINT are not allowed in a trigger, but they can be issued inside a stored procedure called by the trigger. LONG and LONG RAW variables are not allowed in a trigger; AFTER row triggers are a bit more efficient than BEFORE row triggers.
To create a trigger on a table, the user account must either own the table or have the capability to create a trigger with the CREATE TRIGGER or CREATE ANY TRIGGER privilege; or must have the ALTER TABLE privilege for that table or have the ALTER ANY TABLE privilege. The trigger owner must also have direct privileges to schema objects referenced by the trigger.