Database
triggers are procedures that are stored in the database and are implicitly
execute (fired) when the contents of a table are changes.
INTRODUCTION:
Oracle allows
the user to define procedures that are implicitly executed (i.e. executed by
Oracle itself), when an insert, update or delete is issued against a table from
SQL* Plus or through an application. These procedures are called database
triggers. The major point that make these triggers stand alone is that they are
fired implicitly (i.e. internally) by Oracle itself and not explicitly called
by the user, as done in normal procedures.
USE OF DATABASE TRIGGERS:
Database
triggers support Oracle to provide a highly customized database management
system. Some of the uses to which the database triggers can be put to customize
management information in Oracle are as follows:
- A trigger can permit DML statement against a table only if they are issued, during regular business hours or on predetermined weekdays.
- A trigger can also be used to keep an audit trail of a table (i.e. to store the modified and deleted records of the table) along with the operation performed and the time on which the operation was performed.
- It can be used to prevent invalid transactions.
- Enforce complex security authorizations.
Note:
- When a trigger is fired, a SQL statement inside the trigger can also fire the same or some other trigger (if exists), called as cascading, which must be considered.
- Excessive use of triggers form customizing the database can result in complex inter-dependencies between the triggers, which may be difficult to maintain in large application.
DATABASE TRIGGERS VS. PROCEDURES:
There are
very few differences between these two. In procedures it’s possible to pass parameters
which are not the case with triggers. A trigger is executed implicitly by the
Oracle itself upon modification of an associated table whereas to execute a
procedure, it has to be explicitly called by the user.
DATABASE
TRIGGERS VS.SQL*FORMS:
The Oracle
tool SQL* Forms can also define, store and execute triggers as part of an
application developed using the tool. However they differ from the database
triggers as described below:
- Database triggers are defined on a table, stored in the associated database and executed as a result of an insert, update or a delete statement being issued against a table, no matter which user, tool or application issues the statement.
- SQL*Forms triggers are part of SQL*Forms application and are fired only when a specific trigger point is executed within a specific application. If the transaction in the table performed through some other tool like SQL*Plus this triggers do not apply there.
SQL
statements within an SQL*Forms application can implicitly cause any associated
database trigger to be fired.
DATABASE TRIGGERS VS. DECLARATIVE INTEGRITY CONSTRAINTS:
Triggers as
well as declarative integrity constraints can be use to constraint data input.
However both have significant differences as mentioned below:
- A declarative integrity constraint is a statement about a database that is always true. A constraint applies to existing data in the table and any statement that manipulates the table. Triggers constrain what transaction can do. A trigger does not apply data loaded before the trigger was created, so it does not guarantee all data in table conforms to the rule established by an associated trigger.
- Also a trigger enforces transitional constraint which cannot be enforced by a declarative integrity constraint.
e.g.
Constraint such as * current level (i.e. quantity) of a producer cannot be less
than recorder level* in stock maintenance cannot be enforced by a declarative
integrity constraints.
HOW TO APPLY
DATABASE TRIGGERS:
A trigger has
three basic parts:
1.
A
triggering event or statement
2.
A
trigger restriction
3.
A
trigger action
Each part of
the trigger is explained after the syntax.
2. Triggering Events or Statement:
It
is a SQL statement that causes a trigger to be fired. It can be INSERT, UPDATE
or DELETE statement for a specific table. A triggering statement can also
specify multiple DML statements.
1. Trigger Restriction:
A
trigger restriction specifies a Boolean logical expression that must be TRUE
for the trigger to fire. It is an option available for triggers that are fired
for each row. Its function is to conditionally control the execution of a
trigger. A trigger restriction is specified using a WHEN clause.
2. Trigger Action:
A
trigger section is the procedure (PL/SQL block) that contains the SQL
statements and PL/SQL code to be executed when a triggering statement is issued
and the trigger restriction evaluates to TRUE. It can contain SQL and PL/SQL statements;
and define PL/SQL language constructs and can call stored procedures.
Additionally, for row triggers, the statements in a trigger action have access
to column values (new and old) of the current row being processed.
TYPES OF TRIGGERS:
When you define a trigger, you can
specify the number of times the trigger action is to be executed; one for every
row affected by the triggering statement (such as might be fired by and UPDATE
statement that updated many rows), or once for the triggering statement, no
matter how many rows it affects. The types of triggers are as explained below.
ROW TRIGGERS:
A row trigger is fired each time the
table is affected by the triggering statement. For example, if an UPDATE
statement updates multiple rows of a table, a row trigger is fired once for
each row affected by the UPDATE statement. IF the triggering statement affects
no rows, the trigger is not executed at all. Row trigger should be used when
the trigger action code depends on the data provided by the triggering statement
or rows that are affected.
e.g. if the trigger is keeping the
track of all the affected records.
Statement Triggers:
A row trigger is fired once on behalf
of the triggering statement, independent of the number of rows the triggering
statement affects (even if no rows are affected).
Statement riggers are useful if the
code in the trigger action does not depend on the data provided by the
triggering statement or the rows affected.
e.g. if the trigger makes the security
check on the time or the user.
Before VS. After Triggers:
When defining a trigger you can
specify the trigger timing, i.e. you can specify when the triggering action is
to be executed in relation to the triggering statement. BEFORE and AFTER apply
to both row and the statement triggers.
Before Triggers:
BEFORE triggers
execute the trigger action before the triggering statement. These types of
triggers are commonly used in the following situation.- BEFORE triggers are used when the trigger action should determine whether or not the triggering statement should be allowed to complete. By using a BEFORE trigger, you can eliminate unnecessary processing of the triggering statement.
- SQL*Forms triggers are part of SQL*Forms application and are fired only when a specific trigger point is executed within a specific application. If the transaction in the table performed through some other tool like SQL*Plus this triggers do not apply there.
DATABASE TRIGGERS VS. DECLARATIVE INTEGRITY CONSTRAINTS
- A declarative integrity constraint is a statement about a database that is always true. A constraint to existing data in the table and any statement that manipulated the table.Triggers constraint can do. A trigger does not apply to data loaded before the trigger was created, so it does not guarantee all data in table conforms to the rules established by an associated trigger.
- Also a trigger enforces transitional constraint which can not be enforced by a declarative integrity constraints.
HOW TO APPLY DATABASE TRIGGERS
A trigger has three basic parts:
- A triggering event or statement
- A trigger restriction
- A trigger action
Triggering Event or statement:
It is a SQL statement that causes a trigger to be fired. It can be INSERT, UPDATE or DELETE statement for a specific table. A triggering statement can also specify multiple DML statements.
2.Trigger Restriction:
3.Trigger Action:
A trigger action is the procedure (PL/SQL block) that constraint the SQL statements
and PL/SQL code to be executed when a triggering statement is issued and the trigger restriction
evaluated to TRUE. It can contain SQL and PL/SQL statements; can define PL/SQL
language constructs and can call stored procedures. Additionally, for row triggers, the statement
in a trigger action have access to column values (new and old) of the current row being processed.
