School of Information Systems

Database Trigger Object in Oracle

22 Mar 2021

A trigger is a PL/SQL block that is stored in the database and fired (executed) in response to a specified event. The Oracle database automatically executes a trigger when specified conditions occur. Triggers are similar to stored procedures. A trigger stored in the database contains PL/SQL in the form of an anonymous block, a call statement, or a compound trigger block. Procedures and triggers differ in the way that they are invoked.

Triggers are implicitly fired by the Oracle database when a triggering event occurs, no matter which user is connected or which application is being used. A trigger can be defined on the table, view, schema (schema owner), or database (all users).

Database trigger fires whenever a DML, a DLL, or system event occurs on a schema or database. Database triggers execute implicitly when any of the following events occur:

  • DML operations on a table
  • DML operations on a view, with an INSTEAD OF trigger
  • DDL statements, such as CREATE and ALTER

Notice that triggers are stored in the database separately from their associated tables.

Triggers can be defined only on tables, not on views. However, triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.

Database triggers also execute implicitly when some user actions or database system actions occur (for example, when a user logs on or the DBA shuts down the database).

In many cases, triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can permit DML operations against a table only if they are issued during regular business hours. The standard security features of Oracle, roles and privileges, govern which users can submit DML statements against the table. In addition, the trigger further restricts DML operations to occur only at certain times during weekdays. This is just one way that you can use triggers to customize information management in an Oracle database. In addition, develop database triggers in order to enhance features that cannot otherwise be implemented by the Oracle server or as alternatives to those provided by the Oracle server.

  • Security: The Oracle server allows table access to users or roles. Triggers allow table access according to data values.
  • Auditing: The Oracle server tracks data operations on tables. Triggers track values for data operations on tables.
  • Data integrity: The Oracle server enforces integrity constraints. Triggers implement complex integrity rules.
  • Referential integrity: The Oracle server enforces standard referential integrity rules. Triggers implement nonstandard functionality.
  • Table replication: The Oracle server copies tables asynchronously into snapshots. Triggers copy tables synchronously into replicas.
  • Derived data: The Oracle server computes derived data values manually. Triggers compute derived data values automatically.
  • Event logging: The Oracle server logs events explicitly. Triggers log events transparently.

Example of implementing a trigger database:

  • The trigger restricts inserts into the EMPLOYEES table during certain business hours, Monday through Friday.

Source:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm

https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch15.htm

Nur Anisa