Oracle Triggers – The Complete Guide

Oracle triggers are a handy but often misused function of the Oracle database. Learn all about them in this article.

 

What Are Oracle Triggers?

A trigger is a piece of PL/SQL code on your database that runs when certain events happen.

It’s like a stored procedure, but you can’t explicitly call the trigger. It can only run when the event that the trigger is linked to is run.

Triggers can be enabled and disabled. When they are enabled, the database will automatically run it when the triggering event occurs. If it is disabled, it does not run.

Types of Triggers in Oracle

There are a few different types of triggers in Oracle. They can be grouped into three categories:

• DML Triggers
• Schema Triggers (also called DDL triggers)
• Database Triggers

Within each of these categories, there are several types of triggers.

DML Trigger

The main type of trigger that I’ve seen used is the DML trigger. They are called that because they run when a DML statement is executed (INSERT, UPDATE, or DELETE).

The triggers can run either BEFORE the statement is executed on the database, or AFTER the statement is executed.

Because of this, these triggers are often named or referred to as “when they run” and “what statement they run on”.

The triggers include:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

I’ll go into detail on each of these triggers later in this article. You can click on the links to be taken to the place in this article where I detail the triggers.

Schema Triggers

Schema triggers, or DDL triggers, are triggers that fire on events that happen on any object within the schema.

It fires when the user who owns the schema is the current user, and they initiate the triggering event.

The events that Oracle triggers can be created on are:

• ALTER
• ANALYZE
• ASSOCIATE STATISTICS
• AUDIT
• COMMENT
• CREATE
• DISASSOCIATE STATISTICS
• DROP
• GRANT
• NOAUDIT
• RENAME
• REVOKE
• TRUNCATE
• DDL

I’ll also go into more detail on these triggers in the Schema Triggers section later in this article.

Database Triggers

A database trigger is created on the database, and it is run whenever the event the trigger is linked to occurs. This happens no matter which user is logged on to the database.

The events that can have database triggers created on them are:

• AFTER STARTUP
• BEFORE SHUTDOWN
• AFTER DB_ROLE_CHANGE
• AFTER SERVERERROR
• AFTER LOGON
• BEFORE LOGOFF
• AFTER SUSPEND

I’ll go into more details on these Oracle triggers in the Database Trigger section of this article. If you’re looking for information on SQL Server triggers, read Vivek Johari’s article on an introduction to triggers.

Reasons to Use Oracle Triggers

So, we now know what a trigger is. It’s a piece of code that runs at certain events.

Why would you want to use a trigger?

There are several reasons to use a trigger in a database:

• Log events that occur in the database
• Prevent invalid transactions (perform validation before tables are updated)
• Automatically generate values for columns
• Modify tables when DML statements are run on views
• Enforce complex business rules that you can’t do with constraints
• Prevent DML operations on tables during certain times (e.g. outside of business hours)
• Enforce referential integrity in distributed databases
• Enforce complicated security rules
• Track or record database events

The code that goes on a trigger will run on the event that triggers it. This means you don’t need to make sure that applications call stored procedures to perform the functionality you want. Any time a specific action is performed, the trigger will fire.

Let’s take a look at how to create a DML trigger.

Creating a Trigger

To create a trigger, you use the CREATE TRIGGER statement. The details of the statement will change depending on if you’re creating a DML trigger, a schema trigger, or a database trigger.

The most common triggers I’ve seen are the DML triggers. The CREATE TRIGGER statement for those triggers looks like this:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} dml_event ON table_name
[FOR EACH ROW]
  [DECLARE variables]
  BEGIN
  pl_sql_code
  [EXCEPTION exception_code]
  END;

This statement includes:

• OR REPLACE: An optional clause. If you specify this, it means the trigger with the same name is replaced with this trigger. It allows you to change the trigger without having to drop the trigger using the DROP TRIGGER statement.
• trigger_name: The name of the trigger in the database.
• BEFORE|AFTER: Specifies if you want the trigger to run before the statement or after the statement.
• dml_event: The DML event that the trigger runs on, which could be INSERT, UPDATE, or DELETE.
• table_name: The name of the table that the event runs on.
• FOR EACH ROW: This means that the trigger runs for each row affected by the statement. If this is omitted, the trigger is run once.
• DECLARE: This section allows you to declare variables to use within the trigger.
• pl_sql_code: This is the body of your trigger and is where you put the code that defines what you want to do.
• EXCEPTION exception_code: This is the Exception section and it’s where you put your error handling, if you have any.

The trigger name must be unique when compared to other triggers in the same schema. The names don’t need to be unique when compared to other objects (e.g. tables or procedures), but it is recommended to give them unique names within the schema for all objects (e.g. avoid naming a trigger the same as a procedure).

Let’s take a look at each of the DML triggers and see some examples of them.

NEW and OLD Values in Triggers

When you write code for DML triggers, you write them for a statement. You often need to reference the data that is included in the statement. For example:

• The data that is being inserted in an INSERT statement
• The data that is being updated (both the old and the new data) in an UPDATE statement
• The data that is being deleted in a DELETE statement

This data can be referenced using two variables called :NEW and :OLD.

The :NEW and :OLD data mean two different things, depending on which statement is run.

Statement	:NEW	:OLD
INSERT	The data being inserted into the table	Not applicable
UPDATE	The new data that will be in the table	The old data, or the data that is being replaced
DELETE	Not applicable	The data that is being removed from the table

How do you refer to a particular column value?

You add a period, then the column name.

For example, if you want to refer to the first_name before the data is updated in an UPDATE statement, you would use:

:OLD.first_name

If you want to refer to the new employee salary in an INSERT statement, you can use:

:NEW.salary

Further to the table above, you can only read and update these values in certain situations (either statement level or row level).

This table outlines when you can and can’t read or update these values.

Statement	Scope	Timing	Read :NEW	Update :NEW	Read :OLD	Update :OLD
INSERT	Statement	BEFORE	No	No	N/A	N/A
INSERT	Row	BEFORE	Yes	Yes	N/A	N/A
INSERT	Statement	AFTER	No	No	N/A	N/A
INSERT	Row	AFTER	Yes	No	N/A	N/A
UPDATE	Statement	BEFORE	No	No	No	No
UPDATE	Row	BEFORE	Yes	Yes	Yes	Yes
UPDATE	Statement	AFTER	No	No	No	No
UPDATE	Row	AFTER	Yes	No	Yes	No
DELETE	Statement	BEFORE	N/A	N/A	No	No
DELETE	Row	BEFORE	N/A	N/A	Yes	Yes
DELETE	Statement	AFTER	N/A	N/A	No	No
DELETE	Row	AFTER	N/A	N/A	Yes	No

Refer to the examples below for each trigger type for some examples of these in the triggers.

BEFORE INSERT Trigger

A BEFORE INSERT trigger is a trigger that runs before an INSERT statement is executed.

The syntax of the BEFORE INSERT trigger is:

CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE INSERT ON table_name
[FOR EACH ROW]
  [DECLARE variables]
  BEGIN
  pl_sql_code
  [EXCEPTION exception_code]
  END;

Some things to note about the BEFORE INSERT trigger:

• You can’t create a BEFORE INSERT trigger on a view, unless it is an editioning view.
• If you’re running a statement trigger (by omitting the FOR EACH ROW clause), you can’t read the :NEW or :OLD variables.
• If you’re running a row trigger (using FOR EACH ROW):
  • You can read and update the :NEW values
  • You can’t read or update the :OLD values, as they don’t exist for an INSERT trigger.

Let’s see an example of the BEFORE INSERT trigger:

CREATE TRIGGER employee_bef_insert
BEFORE INSERT ON employee
FOR EACH ROW

DECLARE
  currentUsername VARCHAR2(20);

BEGIN

  --Get the current username
  SELECT USER
  INTO currentUsername
  FROM dual;

  --Set the created by value to the current user
  :NEW.created_by := currentUsername;

  --Set the created date to today
  :NEW.created_date := SYSDATE;

END;

This trigger is designed to set the created_date and created_by columns for the records that are inserted.

The FOR EACH ROW is specified so that the code runs for each row that is inserted.

The current username is retrieved from the USER function. Then, the created_by column in the NEW record is set to this username. Also, the created_date is set to today’s date and time.

This is a simple and common way to use a trigger. It ensures that the created_date and created_by values are set at the database level, which results in a more accurate method of logging this audit data.

Also, a common way to use the BEFORE INSERT trigger is to create an auto increment column. In Oracle 12c, the feature was added to define a column as auto increment. But, if you’re on 11g or earlier, then you need to use a BEFORE INSERT trigger.

For more information, read this guide on creating an AUTO INCREMENT column in Oracle.

The example used for the trigger is:

CREATE TRIGGER student_bi
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
  SELECT student_id_seq.nextval
  INTO :NEW.student_id
  FROM dual;
END;

It gets the nextval of a sequence, and stores it into the primary key of the table.

AFTER INSERT Trigger

The AFTER INSERT trigger is a trigger that runs after an INSERT statement is executed.

The syntax of the AFTER INSERT trigger is:

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER INSERT ON table_name
[FOR EACH ROW]
[DECLARE variables]
BEGIN
  pl_sql_code
[EXCEPTION exception_code]
END;

Some things to note about the AFTER INSERT trigger:

• You can’t create a AFTER INSERT trigger on a view, unless it is an editioning view.
• If you’re running a statement trigger (by omitting the FOR EACH ROW clause), you can’t read the :NEW or :OLD variables.
• If you’re running a row trigger (using FOR EACH ROW):
  • You can read but not update the :NEW values
  • You can’t read or update the :OLD values, as they don’t exist for an INSERT trigger.

Let’s see an example of the AFTER INSERT trigger:

CREATE TRIGGER employee_aft_insert
AFTER INSERT ON employee
BEGIN
  INSERT INTO statement_log (current_user, statement_exec_time, statement_type, table)
  VALUES (USER, SYSDATE, 'INSERT', 'employee');
END;

This is a simple trigger that runs at the statement level, because I omitted the FOR EACH ROW clause.

Each time an insert statement is run on the employee table, this code is run. It inserts a row into the statement_log table, with details about the current user, current date and time, the type of statement that was run, and the name of the table.

Note that because this is a statement trigger, I can’t use the :NEW values. This is because :NEW refers to a single record (e.g. :NEW.first_name, :NEW.salary), and if there are multiple records inserted, we can’t tell which record the :NEW would refer to.

BEFORE UPDATE Trigger

A BEFORE UPDATE trigger is a trigger that runs before an UPDATE statement is executed.

The syntax of the BEFORE UPDATE trigger is:

CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE UPDATE ON table_name
[FOR EACH ROW]
[DECLARE variables]
BEGIN
  pl_sql_code
[EXCEPTION exception_code]
END;

Some things to note about the BEFORE UPDATE trigger:

• You can’t create a BEFORE UPDATE trigger on a view, unless it is an editioning view.
• If you’re running a statement trigger (by omitting the FOR EACH ROW clause), you can’t read the :NEW or :OLD variables.
• If you’re running a row trigger (using FOR EACH ROW), you can read and update the :NEW and :OLD values.

Let’s see an example of a BEFORE UPDATE trigger:

CREATE OR REPLACE TRIGGER employee_bef_update:
BEFORE UPDATE ON employee
FOR EACH ROW

DECLARE
currentUsername VARCHAR2(20);

BEGIN

  --Get the current username
  SELECT USER
  INTO currentUsername
  FROM dual;

  --Set the updated by value to the current user
  :NEW.updated_by := currentUsername;

  --Set the updated date to today
  :NEW.updated_date := SYSDATE;

END;

 

 

This trigger runs before the employee table is updated. It sets the updated_by value to the current user, and the updated_date value to today’s date.

This is a good way of ensuring the correct data is captured in these two columns.

AFTER UPDATE Trigger

An AFTER UPDATE trigger is a trigger that runs after an UPDATE statement is executed.

The syntax of the AFTER UPDATE trigger is:

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER UPDATE ON table_name
[FOR EACH ROW]
[DECLARE variables]
BEGIN
  pl_sql_code
[EXCEPTION exception_code]
END;

Some things to note about the AFTER UPDATE trigger:

• You can’t create a AFTER UPDATE trigger on a view, unless it is an editioning view.
• If you’re running a statement trigger (by omitting the FOR EACH ROW clause), you can’t read the :NEW or :OLD variables.
• If you’re running a row trigger (using FOR EACH ROW), you can read but not update the :NEW and :OLD values.

Let’s see an example of an AFTER UPDATE trigger:

CREATE TRIGGER employee_aft_update
AFTER UPDATE ON employee
FOR EACH ROW

DECLARE
  currentUsername VARCHAR2(20);

BEGIN

  --Get the current username
  SELECT USER
  INTO currentUsername
  FROM dual;

  --Add old data to audit table
  INSERT INTO employee_audit (
  employee_id
  first_name,
  last_name,
  salary,
  old_updated_by,
  old_updated_date,
  new_updated_by,
  new_updated_date)
  VALUES (
  :OLD.employee_id,
  :OLD.first_name,
  :OLD.last_name,
  :OLD.salary,
  :OLD.updated_by,
  :OLD.updated_date,
  currentUsername,
  SYSDATE
  );

END;

This example inserts the data before the update of the employee table into an employee_audit table. It makes use of the :OLD variable to get the data before the update, and capture who performed the updated and at what time.

UPDATE Triggers on a Column

When you run an UPDATE statement, you specify which columns you want to update.

You can define a trigger on the UPDATE statement to only run if a specific column is updated.

If the triggering statement includes one of these columns in it, then the trigger is run. If the column is not in the list of columns in the trigger, the trigger is not run.

Let’s see an example.

Assume we have this statement to run on our table.

UPDATE employee
SET email_address = "[email protected]"
WHERE employee_id = 53;

We could have a trigger that checks if the email address is also a customer’s email address, and set a flag if it is. We would only want to run this when the email address is updated.

Our trigger might look like this:

CREATE TRIGGER employee_bef_update
BEFORE UPDATE OF email_address ON employee
FOR EACH ROW

DECLARE
customerRecordCount NUMBER(5)

BEGIN

  SELECT COUNT(*)
  INTO customerRecordCount
  FROM customer c
  WHERE c.email_address = :NEW.email_address;

  IF customerRecordCount > 0 THEN
    :NEW.customer_flag = 1;
  ELSE
    :NEW.customer_flag = 0;
  END IF;

END;

Notice on the second line: “BEFORE UPDATE OF email_address ON employee”. We specify “UPDATE OF email_address” to ensure that this trigger is only run when the email_address is run.

The trigger then performs a SELECT statement into a variable, and depending on the result of this variable, it sets a different value for a column.

If we wanted to specify multiple columns, we could separate them by commas.

For example, the line would then be:

BEFORE UPDATE OF email_address, first_name, last_name ON employee

Some things to note about columns in UPDATE triggers:

• You can’t specify columns with UPDATE statements with INSTEAD OF triggers.
• You can’t specify columns to update with collection columns
• If you specify an object column, the trigger is fired if any of the object column attributes are modified.

BEFORE DELETE Trigger

The BEFORE DELETE trigger is a trigger that runs before a DELETE statement is executed.

The syntax of the BEFORE DELETE trigger is:

CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE DELETE ON table_name
[FOR EACH ROW]
[DECLARE variables]
BEGIN
  pl_sql_code
[EXCEPTION exception_code]
END;

Some things to note about the BEFORE DELETE trigger:

• You can’t create a BEFORE DELETE trigger on a view, unless it is an editioning view.
• If you’re running a statement trigger (by omitting the FOR EACH ROW clause), you can’t read the :OLD values.
• If you’re running a row trigger (using FOR EACH ROW), you can read and update the :OLD values.

Let’s see an example of an BEFORE DELETE trigger:

CREATE OR REPLACE employee_bef_delete
BEFORE DELETE ON employee
BEGIN

  INSERT INTO delete_log (impacted_table, current_user, current_date)
  VALUES ('employee', USER, SYSDATE);

END;

This trigger will insert a record into the delete_log table before each delete statement is run. It stores the table name, the user who ran the statement, and the current date and time.

AFTER DELETE Trigger

The AFTER DELETE trigger is a trigger that runs before a DELETE statement is executed.

The syntax of the AFTER DELETE trigger is:

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER DELETE ON table_name
[FOR EACH ROW]
[DECLARE variables]
BEGIN
  pl_sql_code
[EXCEPTION exception_code]
END;

Some things to note about the AFTER DELETE trigger:

• You can’t create a AFTER DELETE trigger on a view, unless it is an editioning view.
• If you’re running a statement trigger (by omitting the FOR EACH ROW clause), you can’t read the :OLD values.
• If you’re running a row trigger (using FOR EACH ROW), you can read but not update the :OLD values.

Let’s look at an example of the AFTER DELETE trigger:

CREATE TRIGGER employee_aft_update
AFTER DELETE ON employee
FOR EACH ROW

DECLARE
  currentUsername VARCHAR2(20);

BEGIN

  --Get the current username
  SELECT USER
  INTO currentUsername
  FROM dual;

  --Add old data to audit table
  INSERT INTO employee_audit (
  employee_id
  first_name,
  last_name,
  salary,
  old_updated_by,
  old_updated_date,
  new_updated_by,
  new_updated_date)
  VALUES (
  :OLD.employee_id,
  :OLD.first_name,
  :OLD.last_name,
  :OLD.salary,
  :OLD.updated_by,
  :OLD.updated_date,
  currentUsername,
  SYSDATE
  );

END;

This trigger inserts a record into the employee_audit table for each record deleted from the employee table. It stores all of the data from the deleted record, as well as who deleted it and what day and time they deleted it.

DML Triggers for Multiple Events

You can define a trigger that handles multiple events (INSERT/UPDATE/DELETE) on a single table. This is done by using the OR keyword.

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} DELETE OR INSERT OR UPDATE ON table_name
[FOR EACH ROW]
[DECLARE ...]
BEGIN
...
[EXCEPTION]
END;

You can define two or three of INSERT, UPDATE, or DELETE when creating the trigger. This will mean the trigger will run when either of those statements are executed.

One thing to be aware of is that the rules for :NEW and :OLD still apply. So, if you have a trigger that runs on INSERT and UPDATE statements, and you refer to the :OLD values, you need to be careful where you reference it.

You can use the keywords INSERTING, UPDATING, or DELETING to determine what kind of statement was run. This is often done with a CASE statement.

• INSERTING is when an INSERT statement is run or when an INSERT part of a MERGE statement is run.
• UPDATING is when an UPDATE statement is run or when an UPDATE part of a MERGE statement is run.
• DELETING is when a DELETE statement is run, but not when a DELETE part of a MERGE statement is run.

For example:

CREATE OR REPLACE TRIGGER employee_trigger
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW

BEGIN

  CASE
    WHEN INSERTING THEN
      --Code here
    WHEN UPDATING THEN
      --Code here
  END CASE;

END;

Schema Triggers

A schema trigger is a trigger that is fired on a particular event on any object in the schema. It fires when the user who owns the schema is the current user, and they initiate the triggering event.

It fires when the user who owns the schema is the current user, and they initiate the triggering event.

Schema triggers can be created on DDL events or on database events. Refer to the table below to see which events are valid.

These schema triggers are created using this syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} trigger_event [OR event...]
ON SCHEMA
[DECLARE variables]
BEGIN
  plsql_code
[EXCEPTION exception_code]
END;

The main difference between the syntax for these triggers and the DML triggers is the “ON SCHEMA” keyword, and the actual trigger events used.

Examples of each of the events would look very similar, so I’ll show you one example using a schema trigger.

CREATE OR REPLACE TRIGGER grant_trigger
AFTER GRANT
ON SCHEMA

DECLARE
  grant_list dbms_standard.ora_name_list_t;
BEGIN
  INSERT INTO grant_audit (current_user, grant_date, grantee)
  VALUES (
  USER,
  SYSDATE,
  grant_list(1)
  );

END;

If you wanted to create a trigger that fired on multiple events, you can use the OR keyword between each event.

Here is an example of a trigger that fires on a GRANT or REVOKE event.

CREATE OR REPLACE TRIGGER grant_trigger
AFTER GRANT OR REVOKE
ON SCHEMA

DECLARE
  grant_list dbms_standard.ora_name_list_t;
BEGIN
  INSERT INTO grant_audit (current_user, grant_date, grantee)
  VALUES (
  USER,
  SYSDATE,
  grant_list(1)
  );

END;

There are a range of commands and functions you can use when working with system events, such as getting the list of grantees. These are detailed in this Oracle page.

Database Triggers

A database trigger is created on the database, and it is run whenever a database specific event occurs. This happens no matter which user is logged on to the database.

You can create database triggers on the DDL events mentioned in the DDL and Database Events section. This means that the trigger will fire for all users who perform the event, not just if the current user owns the schema. Refer to the table below to see which events are valid.

An example of a database trigger is:

CREATE OR REPLACE TRIGGER trg_logon_audit
AFTER LOGON
ON DATABASE

BEGIN
  INSERT INTO logon_audit (current_username, current_date)
  VALUES (USER, SYSDATE);
END;

This might not be the best use of a logon trigger, but it’s just showing the concept of a database-level trigger for the logon event.

DDL and Database Events

Both schema and database triggers can be triggered on DDL and database events. A DDL event occurs when a DDL statement is run, and a database event occurs when specific things happen on the database.

The schema events are:

Event	Fired When	Valid for Schema Trigger	Valid for Database Trigger
ALTER	an ALTER statement modifies a database object. Does not fire on ALTER DATABASE commands.	Yes	Yes
ANALYZE	the database collects or deletes statistics, or validates the structure of a database object.	Yes	Yes
ASSOCIATE STATISTICS	the database associates statistics with a database object.	Yes	Yes
AUDIT	the AUDIT statement is run.	Yes	Yes
COMMENT	a comment on a database object is added to the data dictionary.	Yes	Yes
CREATE	a CREATE statement is run and an object is added to the data dictionary. Does not run when CREATE DATABASE or CREATE CONTROLFILE statements are run.	Yes	Yes
DISASSOCIATE STATISTICS	the database disassociates statistics with a database object.	Yes	Yes
DROP	a DROP statement removes an object from the data dictionary.	Yes	Yes
GRANT	a user grants system privileges, roles, or object privileges to another user or role.	Yes	Yes
NOAUDIT	a NOAUDIT statement is run.	Yes	Yes
RENAME	a RENAME statement is run and changes the name of a database object.	Yes	Yes
REVOKE	a REVOKE statement removes system privileges, roles, or object privileges from another user or role.	Yes	Yes
TRUNCATE	a TRUNCATE statement is run and rows are removed from a table.	Yes	Yes
DDL	any of these DDL statements are run.	Yes	Yes

The database events are:

Event	Fired When	Valid for Schema Trigger	Valid for Database Trigger
AFTER STARTUP	the database is opened.	No	Yes
BEFORE SHUTDOWN	the instance of the database is shut down.	No	Yes
AFTER DB_ROLE_CHANGE	a role change occurs between primary and standby, in a Data Guard configuration.	No	Yes
AFTER SERVERERROR	a server error message is logged and the Oracle database determines it is safe to fire error triggers.	Yes	Yes
AFTER LOGON	an application logs on to the database.	Yes	Yes
BEFORE LOGOFF	an application logs off the database.	Yes	Yes
AFTER SUSPEND	a server error causes a transaction to be suspended.	Yes	Yes

INSTEAD OF DML Triggers

An INSTEAD OF trigger is a trigger that is defined to run instead of a particular DML statement, or a system trigger defined on the CREATE statement.

The INSTEAD OF DML trigger is run by the database instead of the actual command it is based on.

Some things to note about INSTEAD OF triggers:

• It cannot be conditional.
• It’s always a row-level trigger
• It can read :OLD and :NEW values, but can’t change them.

INSTEAD OF triggers can be dangerous and can hurt the design of your database if they are used inappropriately or too often.

One way to use INSTEAD OF triggers is to update a view that cannot be updated.

For example, consider this view:

CREATE VIEW department_employee_info AS
SELECT d.department_id, d.department_name,
e.employee_id, e.first_name, e.last_name
FROM department d
INNER JOIN employee e ON d.department_id = e.department_id;

This view is not updatable because the primary key of the employee table (employee_id) is not unique in this view.

If a user tries to INSERT into this view, they will get an error.

However, we could create an INSTEAD OF trigger on the INSERT statement to say “instead of inserting into the view, we will insert into the underlying tables”.

This trigger could be:

CREATE OR REPLACE TRIGGER dept_emp_info_insert
INSTEAD OF INSERT ON department_employee_info

DECLARE
  duplicate_records EXCEPTION;
  PRAGMA EXCEPTION_INIT (duplicate_records, -00001);
BEGIN
  INSERT INTO department (department_id, department_name)
  VALUES (
  :NEW.department_id,
  :NEW.department_name
  );

  INSERT INTO employee (employee_id, first_name, last_name, department_id)
  VALUES (
  :NEW.employee_id,
  :NEW.first_name,
  :NEW.last_name,
  :NEW.department_id
  );

EXCEPTION
  WHEN duplicate_records THEN
    RAISE_APPLICATION_ERROR (
      num=> -20101,
      msg=> 'Duplicate department or employee ID inserted.'
    );

END;

This trigger will fire whenever an INSERT statement on the department_employee_info table is performed. Instead of performing the INSERT, this code will run. It inserts data into the employee and department tables.

When the user queries the department_employee_info view again, they should see their data.

INSTEAD OF CREATE Triggers

The other type of INSTEAD OF trigger is the INSTEAD OF CREATE trigger.

This is a system trigger that is defined on the CREATE statement. It runs on the database instead of the CREATE statement being run.

An example of the INSTEAD OF CREATE trigger is shown here:

CREATE OR REPLACE TRIGGER instead_of_create_trg
INSTEAD OF CREATE ON SCHEMA
BEGIN
  INSERT INTO create_attempt (current_user, current_date)
  VALUES (USER, SYSDATE);
END;

Compound Triggers

A compound trigger allows you to combine more than one timing point for a single object in a single trigger. The timing points that can be used are:

• Before Statement
• Before Each Row
• After Statement
• After Each Row

These are useful Oracle triggers because they allow you to combine the functionality into a single trigger, which allows for variables to be shared if necessary, and for a cleaner solution.

The syntax of a compound trigger is shown here:

CREATE [OR REPLACE] TRIGGER trigger_name
FOR trigger_event ON table_name
COMPOUND TRIGGER

  [variable declaration]

  BEFORE STATEMENT IS
  BEGIN
  --Code goes here
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
  --Code goes here
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
  --Code goes here
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
  --Code goes here
  END AFTER STATEMENT;

END;

You can combine multiple trigger events into this compound table as well. So, you can use the INSERTING/UPDATING/DELETING checks in each of the sections in this syntax to further define your trigger.

Modifying a Trigger

Oracle triggers cannot be modified directly. To change the definition of a trigger, you either need to:

• Drop and recreate the trigger.
• Create the new trigger using the CREATE OR REPLACE keywords.

There is an ALTER TRIGGER statement, but that is only used for recompiling, enabling, or disabling triggers, as I’ll cover later in this article.

How to Drop a Trigger

Like many other database objects, triggers can be removed. This is done by dropping the trigger using the DROP TRIGGER statement.

The syntax is:

DROP TRIGGER trigger_name;

For example:

DROP TRIGGER employee_bef_insert;

This will drop the employee_bef_insert trigger.

To drop a trigger, either:

• The trigger must be in your schema.
• You must have the DROP ANY TRIGGER system privilege.

To drop a trigger in another user’s schema, you must also have the ADMINISTER DATABASE TRIGGER privilege.

How to Enable a Trigger

Triggers can be enabled or disabled. As mentioned earlier, a trigger can only be executed if it is enabled. Triggers are enabled by default.

To enable a trigger, run the ALTER TRIGGER command:

ALTER TRIGGER trigger_name ENABLE;

For example, to enable the employee_bef_insert trigger:

ALTER TRIGGER employee_bef_insert ENABLE;

If you want to enable all triggers on a table, you can use the ALTER TABLE command:

ALTER TABLE table_name ENABLE ALL TRIGGERS;

For example, to enable all triggers on the employee table:

ALTER TABLE employee ENABLE ALL TRIGGERS;

How to Disable a Trigger

To disable a trigger, you use the ALTER TRIGGER command. If a trigger is disabled, it is not executed when the linked statement is executed.

To disable a trigger, run the ALTER TRIGGER command:

ALTER TRIGGER trigger_name DISABLE;

 

For example, to disable the employee_bef_insert trigger:

ALTER TRIGGER employee_bef_insert DISABLE;

If you want to disable all triggers on a table, you can use the ALTER TABLE command:

ALTER TABLE table_name DISABLE ALL TRIGGERS;

For example, to disable all triggers on the employee table:

ALTER TABLE employee DISABLE ALL TRIGGERS;

How to Rename a Trigger

Triggers can be renamed using the ALTER TRIGGER command.

ALTER TRIGGER trigger_name RENAME TO new_trigger_name;

For example, to rename the employee_bef_insert trigger to employee_bi_trg:

ALTER TRIGGER employee_bef_insert RENAME TO employee_bi_trg;

When you rename a trigger, the database rebuilds the remembered source of the trigger in the views USER_SOURCE, ALL_SOURCE, and DBA_SOURCE. Comments and formatting may change in the TEXT column of those views.

Best Practices for Designing Triggers

There is some debate in the development community over whether triggers are useful, dangerous, or even necessary.

Like many tools used by programmers, they can be useful if used correctly. Here are some thoughts and best practices for developing Oracle triggers:

• Don’t use them for duplicating Oracle database features (such as privileges, referential integrity, sequence value generation, rejecting bad data otherwise possible with constraints)
• Limit the size of the triggers. If the code is too large, put the code in a stored procedure and call it from the trigger,
• Don’t create recursive triggers.
• Avoid using triggers on the database if you can help it.
• Adding DML triggers to tables will impact performance.
• Putting non-transactional tasks inside triggers can cause problems. Non-transactional work may include emails or job creation.
• Don’t create a chain of triggers. This can lead to messy solutions and can be hard to debug.

Toon Koppelaars has a blog that discusses when triggers can be used, which has some great information.

Privileges Required for Oracle Triggers

There are several privileges required for creating triggers. To create a trigger in your schema:

• You need to have the CREATE TRIGGER privilege
• You either:
  • Own the table mentioned by the trigger
  • Have the ALTER privilege for the table mentioned by the trigger
  • Have the ALTER ANY TABLE privilege

To create a trigger in another schema, or reference a table in another schema from a trigger in your schema:

• You need to have the CREATE ANY TRIGGER privilege
• You need to have the EXECUTE privilege on any packages referenced by the trigger

To create a trigger on the database:

• You need to have the ADMINISTER DATABASE TRIGGER privilege.

Mutating Table Errors with Triggers

One error you might get when working with triggers is called the “mutating table” error, or ORA-04091.

This happens because:

• You’ve written a trigger on a table.
• The trigger is on a row-level event.
• Within this trigger code, you reference the table that the trigger is based on.

There are several solutions to this, some good and some bad. I will detail this in a separate article.

 

So, there’s my guide on Oracle triggers. If you have any questions, leave them in the comments section below.