Using Triggers
While working with a database application, you may want to add programmatic
functionality that executes when specific operations occur in the database.
For example, there may be typical scenarios such as the following:
- Example 1: You may want to insert data into a table.
You find that the new data is inconsistent with the existing data in
the table. You may want the system to raise an error that will cause the
transaction to be rolled back.
- Example 2: You may want the system to record information such as the timing and the details of the user modifying data in a table.
Triggers help the user deal with these and other complex situations. A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or database. Triggers are used to ensure data integrity by checking on data in a consistent fashion. A trigger executes implicitly whenever a particular event takes place.
- Improved
data security
Triggers provide enhanced and complex security checks, auditing.
- Improved
data integrity
Triggers enforce dynamic data integrity and complex referential integrity constraints. Triggers ensure that related operations are performed together implicitly.
Triggers can be either of the following:
- Application trigger: This trigger fires whenever an event occurs with a particular application
- Database trigger: This trigger fires whenever a data event (such as DML) or system event (such as logon or shutdown) occurs on a schema or database.
- You can design triggers to perform related actions and centralize global operations.
- You should not design triggers where functionality is already built in to the Oracle server.
- Avoid designing triggers that duplicate other triggers.
- You can create stored procedures and invoke them in a trigger if the PL/SQL code is very lengthy.
- Excessive use of triggers can result in complex interdependencies that may be difficult to maintain in large applications.