All About Triggers
Triggers in Oracle:
A trigger is a PL/SQL block or a PL/SQL
procedure associated with a table, view, schema, or the database. The
triggers executes implicitly whenever a particular event takes place.
Types of Triggers:
1] Application Triggers:
Application triggers execute implicitly
whenever a particular data manipulation language (DML) event occurs
within an application (ex. Oracle Forms, Oracle Reports)
2] Database Triggers:
These triggers are fired whenever a
data event (such as DML) or system event (such as logon or shutdown)
occurs on a schema or database.
There are four types of database triggers:
- Table-level triggers can initiate activity before or after an INSERT, UPDATE, or DELETE event.
- View-level triggers define what can be done to the view.
- Database-level triggers can be activated at startup and shutdown of a database.
- Session-level triggers can be used to store specific information.
Benefits of Database triggers:
– Provide enhanced and complex security checks
– Provide enhanced and complex auditing
– Enforce dynamic data integrity constraints
– Enforce complex referential integrity constraints
– Ensure that related operations are performed together implicitly
Creating DML Triggers:
A triggering statement contains:
1] Trigger timing:
BEFORE: Execute the trigger body before the triggering DML event on a table.
AFTER: Execute the trigger body after the triggering DML event on a table.
INSTEAD OF: Execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.
2] Triggering event:
Triggering user event tells which DML statement causes the trigger to execute? You can use any of the following:
• INSERT
• UPDATE
• DELETE
3] Trigger type:
Trigger type tells should the trigger body execute for each row the statement affects or only once?
• Statement:
The trigger body executes once for the triggering event. This is the
default. A statement trigger fires once, even if no rows are affected at
all.
• Row: The
trigger body executes once for each row affected by the triggering
event. A row trigger is not executed if the triggering event affects no
rows.
4] Trigger body:
Trigger body tells what action should the trigger perform? The trigger body is a PL/SQL block or a call to a procedure.
PL/SQL Trigger Execution Hierarchy:
The following hierarchy is followed when a trigger is fired.
1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each
affected row. This event will alternates between BEFORE and AFTER row
level triggers.
4) Finally the AFTER statement level trigger fires.
Syntax of Triggers:
The Syntax for creating a trigger is:
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;
- CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
- {BEFORE | AFTER | INSTEAD OF } -
This clause indicates at what time should the trigger get fired. i.e
for example: before or after updating a table. INSTEAD OF is used to
create a trigger on a view. before and after cannot be used to create a
trigger on a view.
- {INSERT [OR] | UPDATE [OR] | DELETE}
– This clause determines the triggering event. More than one triggering
events can be used together separated by OR keyword. The trigger gets
fired at all the specified triggering event.
- [OF col_name] - This clause
is used with update triggers. This clause is used when you want to
trigger an event only when a specific column is updated.
- CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
- [ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.
- [REFERENCING OLD AS o NEW AS n] -
This clause is used to reference the old and new values of the data
being changed. By default, you reference the values as : old.column_name
or :new.column_name. The reference names can also be changed from old
(or new) to any other user-defined name. You cannot reference old values
when inserting a record, or new values when deleting a record, because
they do not exist.
- [FOR EACH ROW] - This clause
is used to determine whether a trigger must fire when each row gets
affected ( i.e. a Row Level Trigger) or just once when the entire sql
statement is executed(i.e.statement level Trigger).
- WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.
Example of DML Statement Trigger:
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR(SYSDATE,'HH24:MI')
NOT BETWEEN '08:00' AND '18:00')
THEN RAISE_APPLICATION_ERROR (-20500,'You may
insert into EMPLOYEES table only during business hours.');
END IF;
END
What are conditional predicates?
When creating a database trigger,
several triggering events can be combined into one by using the
INSERTING, UPDATING, and DELETING conditional predicates within the
trigger body. In this way, a user can create a trigger that runs a
different code based on the type of the triggering statement that fires
the trigger. For example, conditional predicates can be used to create a
database trigger that restricts all data manipulation events (INSERT,
UPDATE, DELETE, etc.) on a table to certain business hours Monday
through Friday.
Example of using conditional predicates in triggers:
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
IF (TO_CHAR (SYSDATE,'DY') IN ('SAT','SUN')) OR
(TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '18')
THEN
IF DELETING THEN
RAISE_APPLICATION_ERROR (-20502,'You may delete from
EMPLOYEES table only during business hours.');
ELSIF INSERTING THEN
RAISE_APPLICATION_ERROR (-20500,'You may insert into
EMPLOYEES table only during business hours.');
ELSIF UPDATING ('SALARY') THEN
RAISE_APPLICATION_ERROR (-20503,'You may update
SALARY only during business hours.');
ELSE
RAISE_APPLICATION_ERROR (-20504,'You may update
EMPLOYEES table only during normal hours.');
END IF;
END IF;
END;
What are OLD and NEW qualifiers?
The OLD and NEW qualifiers are used to reference the values of a column before and after the data change, respectively.
The OLD and NEW qualifiers can be used
only with row triggers. They cannot be used with statement triggers. The
OLD and NEW qualifiers must be prefixed with a colon (:) in every SQL
and PL/SQL statement except when they are referenced in a WHEN
restricting clause.
Example of using OLD and NEW qualifiers:
CREATE OR REPLACE TRIGGER audit_emp_values
AFTER DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_emp_table (user_name, timestamp,
id, old_last_name, new_last_name, old_title,
new_title, old_salary, new_salary)
VALUES (USER, SYSDATE, :OLD.employee_id,
:OLD.last_name, :NEW.last_name, :OLD.job_id,
:NEW.job_id, :OLD.salary, :NEW.salary );
END;
INSTEAD OF trigger:
The Oracle INSTEAD-OF trigger has the
ability to update normally non-updateable views. Simple views are
generally updateable via DML statements issued against the view.
However, when a view becomes more complex it may lose its
“updateable-ness,” and the Oracle INSTEAD-OF trigger must be used.
INSTEAD OF triggers are valid for DML events on views. They are not valid for DDL or database events.
If a view is inherently updatable and has INSTEAD OF
triggers, then the triggers take preference. In other words, the
database fires the triggers instead of performing DML on the view.
Restrictions on INSTEAD OF Triggers
- INSTEAD OF triggers are valid only for views. You cannot specify an INSTEAD OF trigger on a table.
- You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value.
Example of INSTEAD OF trigger:
In this example, an order_info view is created to display information about customers and their orders:
CREATE VIEW order_info AS
SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
o.order_id, o.order_date, o.order_status
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;
Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view.
CREATE OR REPLACE TRIGGER order_info_insert
INSTEAD OF INSERT ON order_info
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO customers
(customer_id, cust_last_name, cust_first_name)
VALUES (
:new.customer_id,
:new.cust_last_name,
:new.cust_first_name);
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (
:new.order_id,
:new.order_date,
:new.customer_id);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=-20107,
msg='Duplicate customer or order ID');
END order_info_insert;
You can now insert into both base tables through the view (as long as all NOT NULL columns receive values):
INSERT INTO order_info VALUES
(999, 'Smith', 'John', 2500, '13-MAR-2001', 0);
Difference between Database Triggers and Stored Procedures:
Database Triggers:
- Database triggers are defined with CREATE TRIGGER statement.
- The data dictionary that contains the source code is USER_TRIGGERS.
- They are implicitly invoked
- COMMIT, SAVEPOINT, and ROLLBACK are not allowed.
- We cannot pass parameters to Database triggers and they cannot return a value.
Stored Procedures:
- Stored Procedures are defined with CREATE PROCEDURE statement.
- The data dictionary that contains the source code is USER_SOURCE.
- They are explicitly invoked.
- COMMIT, SAVEPOINT, and ROLLBACK are allowed.
- We can pass parameters to Stored procedures and they can return a value.
How to know Information about Triggers:
We can use the data dictionary view ‘USER_TRIGGERS’ to obtain information about any trigger.
The below statement shows the structure of the view ‘USER_TRIGGERS’
TRIGGER_NAME — Name of the trigger
TRIGGER_TYPE – The type is BEFORE, AFTER, INSTEAD OF
TRIGGER_EVENT — The DML operation firing the trigger
TABLE_OWNER – Owner of the table
BASE_OBJECT_TYPE – Object type
TABLE_NAME – Name of the database table
COLUMN_NAME – Name of the Column
REFERENCING_NAMES – Name used for :OLD and :NEW
WHEN_CLAUSE – The when_clause used
STATUS – The status of the trigger
DESCRIPTION – Description
ACTION_TYPE – Type of action
TRIGGER_BODY — The action to take
We can see PL/SQL Syntax errors (compilation errors) in the ‘USER_ERRORS’ data dictionary view
Managing triggers:
- Disable or reenable a database trigger:
ALTER TRIGGER trigger_name DISABLE | ENABLE
- Disable or reenable all triggers for a table:
ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS
- Recompile a trigger for a table:
ALTER TRIGGER trigger_name COMPILE
- To remove a trigger from the database, use the DROP TRIGGER syntax:
DROP TRIGGER trigger_name;
Oracle mutating trigger table errors:
The Oracle mutating trigger error
occurs when a trigger references the table that owns the trigger,
resulting in the “ORA-04091: table name is mutating, trigger/function
may not see it.” message.
How to solve it?
Don’t use triggers –
The best way to avoid the mutating table error is not to use triggers.
While the object-oriented Oracle provides “methods” that are associated
with tables, most savvy PL/SQL developers avoid triggers unless
absolutely necessary.
Use an “after” trigger -
If you must use a trigger, it’s best to avoid the mutating table error
by using an “after” trigger. For example, using a trigger “:after update
on xxx”, the original update has completed and the table will not be
mutating.
Use autonomous transactions
– You can avoid the mutating table error by marking your trigger as an
autonomous transaction, making it independent from the table that calls
the procedure.
