A script-enabled browser is required for this page to function properly.

About Database Triggers

A database trigger is nearly identical in concept to the Oracle Forms trigger. The difference lies in the event that causes the trigger to fire and the location where the subsequent code is performed.

Database triggers are PL/SQL blocks that are associated with a given table; they fire upon the execution of UPDATE, INSERT, or DELETE operations against that table. They may fire BEFORE or AFTER each row the operation affects, or each statement. The combinations give a maximum possibility of twelve triggers for any table. While a trigger executes, it runs under the security domain (Schema) of its creator, not the current user.

Within the body of the database trigger, your PL/SQL code may refer to both the old and the new values of the columns being affected. For an INSERT, the old values are non-existent, while for a DELETE, the new values do not exist. This makes data validation simple to implement, and auditing changed values extremely easy.

A database trigger can perform complex data verification that could not be feasibly declared as a constraint. If a database trigger fails with an error, the triggering statement (i.e., the INSERT, UPDATE, or DELETE that fired the trigger) is rolled back.

In a simple example, you could write the following trigger to prevent updates on the EMP table during weekends, unless the current user exists in a special WEEKEND_UPDATE_OK table, in which case the updateis allowed.

CREATE OR REPLACE TRIGGER update_on_weekends_check
BEFORE UPDATE OF sal ON EMP 
FOR EACH ROW 
DECLARE
my_count number(4); 
BEGIN
SELECT COUNT(u_name) FROM WEEKEND_UPDATE_OK INTO my_count 
WHERE u_name = user_name; 
IF my_count=0 THEN 
RAISE_APPLICATION_ERROR(20508, 'Update not allowed'); 
END IF; 
END;

Besides providing arbitrarily complex data validation, database triggers can also be used to perform any cause-and-effect sequence. This makes database triggers particularly well suited for data auditing operations, data replication, and distributed data integrity checking (since Constraints cannot reference remote databases). If database triggers are written to supplant forms-side triggers (e.g. for table auditing) then the forms-side functionality will have to be disabled to avoid duplicating table operations.

One alternative is to use a package variable as a flag to communicate between the form and the database-side triggers (or procedures). In that manner, a decision can be made within the trigger or procedure on whether a particular operation might have already been performed by the Oracle Forms code.

Triggers (as well as stored procedures and functions) raise errors with the RAISE_APPLICATION_ERROR procedure. The RAISE_APPLICATION_ERROR procedure assigns an error number within the special range 20000-20999 and provides an error message. Since it is your PL/SQL database trigger that prepares the error message, the message can vary. Within the Oracle Forms application, these errors can be trapped with the methods mentioned in the next section.


About database trigger privileges

About declarative database Constraints

Creating a database trigger