CREATE TRIGGER

Purpose

Prerequisites

To issue this statement, you must have one of the following system privileges:

CREATE TRIGGER

This system privilege allows you to create a trigger in your own schema on a table in your own schema.

CREATE ANY TRIGGER

This system privilege allows you to create a trigger in any user's schema on a table in any user's schema.

If the trigger issues SQL statements or calls procedures or functions, then the owner of the schema to contain the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner, rather than acquired through roles.

To create a trigger, you must be using Oracle7 with PL/SQL installed.

Syntax

Keywords and Parameters

OR REPLACE

recreates the trigger if it already exists. You can use this option to change the definition of an existing trigger without first dropping it.

schema

is the schema to contain the trigger. If you omit schema, Oracle7 creates the trigger in your own schema.

trigger

is the name of the trigger to be created.

BEFORE

indicates that Oracle7 fires the trigger before executing the triggering statement. For row triggers, this is a separate firing before each affected row is changed.

AFTER

indicates that Oracle7 fires the trigger after executing the triggering statement. For row triggers, this is a separate firing after each affected row is changed.

DELETE

indicates that Oracle7 fires the trigger whenever a DELETE statement removes a row from the table.

INSERT

indicates that Oracle7 fires the trigger whenever an INSERT statement adds a row to table.

UPDATE OF

indicates that Oracle7 fires the trigger whenever an UPDATE statement changes a value in one of the columns specified in the OF clause. If you omit the OF clause, Oracle7 fires the trigger whenever an UPDATE statement changes a value in any column of the table.

ON

specifies the schema and name of the table on which the trigger is to be created. If you omit schema, Oracle7 assumes the table is in your own schema. You cannot create a trigger on a table in the schema SYS.

REFERENCING

specifies correlation names. You can use correlation names in the PL/SQL block and WHEN clause of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW. If your row trigger is associated with a table named OLD or NEW, you can use this clause to specify different correlation names to avoid confusion between the table name and the correlation name.

FOR EACH ROW

designates the trigger to be a row trigger. Oracle7 fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the WHEN clause.

If you omit this clause, the trigger is a statement trigger. Oracle7 fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met.

WHEN

specifies the trigger restriction. The trigger restriction contains a SQL condition that must be satisfied for Oracle7 to fire the trigger. See the syntax description of condition . This condition must contain correlation names and cannot contain a query.

You can only specify a trigger restriction for a row trigger. Oracle7 evaluates this condition for each row affected by the triggering statement.

pl/sql_block

is the PL/SQL block that Oracle7 executes to fire the trigger. For information on PL/SQL, including how to write PL/SQL blocks, see PL/SQL User's Guide and Reference.

Note that the PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, and SAVEPOINT).

Usage Notes

If a trigger produces compilation errors, it still will be created, but it will fail on execution. This means it effectively blocks all triggering DML statements until it is disabled, replaced by a version without compilation errors, or dropped.

To embed a CREATE TRIGGER statement inside an Oracle Precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

Triggers

You can use triggers for the following purposes:

• to provide sophisticated auditing and transparent event logging

• to automatically generate derived column values

• to enforce complex security authorizations and business constraints

• to maintain replicate asynchronous tables

Parts of a Trigger

Triggering statement The definition of the triggering statement specifies what SQL statements cause Oracle7 to fire the trigger.

DELETE INSERT UPDATE

You must specify at least one of these commands that causes Oracle7 to fire the trigger. You can specify as many as three.

ON

You must also specify the table with which the trigger is associated. The triggering statement is one that modifies this table.

Trigger restriction The trigger restriction specifies an additional condition that must be satisfied for a row trigger to be fired. You can specify this condition with the WHEN clause. This condition must be a SQL condition, rather than a PL/SQL condition.

Trigger action The trigger action specifies the PL/SQL block Oracle7 executes to fire the trigger.

Oracle7 evaluates the condition of the trigger restriction whenever a triggering statement is issued. If this condition is satisfied, then Oracle7 fires the trigger using the trigger action.

Types of Triggers

• when Oracle7 fires the trigger in relation to executing the triggering statement

• how many times Oracle7 fires the trigger

• BEFORE

• AFTER

• FOR EACH ROW

FOR EACH ROW option

BEFORE Option

BEFORE statement trigger: Oracle7 fires the trigger once before executing the triggering statement.

BEFORE row trigger: Oracle7 fires the trigger before modifying each row affected by the triggering statement.

AFTER Option

AFTER statement trigger: Oracle7 fires the trigger once after executing the triggering statement.

AFTER row trigger: Oracle7 fires the trigger after modifying each row affected by the triggering statement.

Table 4 - 10. Types of Triggers

For a single table, you can create each type of trigger for each of the following commands:

• DELETE

• INSERT

• UPDATE

If you create multiple triggers of the same type that fire for the same command on the same table, the order in which Oracle7 fires these triggers is indeterminate. If your application requires that one trigger be fired before another of the same type for the same command, combine these triggers into a single trigger whose trigger action performs the trigger actions of the original triggers in the appropriate order.

Enabling and Disabling Triggers

enabled

If a trigger is enabled, Oracle7 fires the trigger whenever a triggering statement is issued and the condition of the trigger restriction is met.

disabled

If a trigger is disabled, Oracle7 does not fire the trigger when a triggering statement is issued and the condition of the trigger restriction is met.

When you create a trigger, Oracle7 enables it automatically.

You can subsequently disable and enable a trigger with one of the following commands:

• the ALTER TRIGGER command with the DISABLE and ENABLE options

• the ALTER TABLE command with the DISABLE and ENABLE clauses

Snapshot Log Triggers

Example I

This example creates a BEFORE statement trigger named EMP_PERMIT_CHANGES in the schema SCOTT. This trigger ensures that changes to employee records are only made during business hours on working days:

CREATE TRIGGER scott.emp_permit_changes 
	BEFORE 
	DELETE OR INSERT OR UPDATE 
	ON scott.emp 
	DECLARE 
		dummy	INTEGER; 
	BEGIN 
		/* If today is a Saturday or Sunday, 

		   then return an error.*/ 
		IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR 
			TO_CHAR(SYSDATE, 'DY') = 'SUN') 
			THEN raise_application_error( -20501, 
		'May not change employee table during the weekend'); 
		END IF; 
		/*	Compare today's date with the dates of all	 
			company holidays. If today is a company holiday, 
			then return an error.								*/ 
		SELECT COUNT(*) 
			INTO dummy 
			FROM company_holidays 
			WHERE day = TRUNC(SYSDATE); 
		IF dummy > 0 
			THEN raise_application_error( -20501, 
			'May not change employee table during a holiday'); 
		END IF; 
		/*	If the current time is before 8:00AM or after		 
			6:00PM, then return an error.		

		*/ 
		IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR 
			TO_CHAR(SYSDATE, 'HH24') >= 18) 
			THEN raise_application_error( -20502, 
		'May only change employee table during working hours'); 
		END IF; 
	END; 

Oracle7 fires this trigger whenever a DELETE, INSERT, or UPDATE statement affects the EMP table in the schema SCOTT.

Since EMP_PERMIT_CHANGES is a BEFORE statement trigger, Oracle7 fires it once before executing the triggering statement.

The trigger performs the following operations:

1. If the current day is a Saturday or Sunday, the trigger raises an application error with a message that the employee table cannot be changed during weekends.

2. The trigger compares the current date with the dates listed in the table of company holidays.

3. If the current date is a company holiday, the trigger raises an application error with a message that the employee table cannot be changed during holidays.

4. If the current time is not between 8:00AM and 6:00PM, the trigger raises an application error with a message that the employee table can only be changed during business hours.

This example creates a BEFORE row trigger named SALARY_CHECK in the schema SCOTT. Whenever a new employee is added to the employee table or an existing employee's salary or job is changed, this trigger guarantees that the employee's salary falls within the established salary range for the employee's job:

CREATE TRIGGER scott.salary_check 
	BEFORE 
	INSERT OR UPDATE OF sal, job ON scott.emp 
	FOR EACH ROW 
	WHEN (new.job <> 'PRESIDENT') 
	DECLARE 
		minsal			NUMBER; 
		maxsal			NUMBER; 
	BEGIN 
		/* Get the minimum and maximum salaries for the
		  employee's job from the SAL_GUIDE table.	*/ 
		SELECT minsal, maxsal 
			INTO minsal, maxsal 
			FROM sal_guide 
			WHERE job = :new.job; 
		/* If the employee's salary is below the minimum or	*/ 
		/* above the maximum for the job, then generate an 	*/ 
		/* error.							*/ 
		IF (:new.sal < minsal OR :new.sal > maxsal) 
		THEN raise_application_error( -20601, 
			'Salary ' || :new.sal || ' out of range for job ' 
			|| :new.job || ' for employee ' || :new.ename ); 
		END IF; 
	END; 

Oracle7 fires this trigger whenever one of the following statements is issued:

• an INSERT statement that adds rows to the EMP table

• an UPDATE statement that changes values of the SAL or JOB columns of the EMP table

SALARY_CHECK has a trigger restriction that prevents it from checking the salary of the company president. For each new or modified employee row that meets this condition, the trigger performs the following steps:

1. The trigger queries the salary guide table for the minimum and maximum salaries for the employee's job.

2. The trigger compares the employee's salary with these minimum and maximum values.

3. If the employee's salary does not fall within the acceptable range, the trigger raises an application error with a message that the employee's salary is not within the established range for the employee's job.

Related Topics