What is a trigger?

What is a trigger?

Rating:

Trigger

A trigger is a kind of procedure that executes implicitly when a table or view is modified or some user actions or database system actions occur. Similar to a stored procedure, a trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. On the other hand, triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

A trigger can be an application trigger or a database trigger. An application trigger executes implicitly whenever a particular data manipulation language (DML) event occurs within an application. An example of an application that uses triggers extensively is one developed with Oracle Forms Developer.

A database trigger executes implicitly when a triggering event such as a DML statement (INSERT, UPDATE, or DELETE) on a table or view, or a DDL statement (CREATE or ALTER) is issued, regardless of which database user is connected or which application is being used. A database trigger also executes implicitly when some user actions or database system actions occur, e.g., when a database user logs on to the database or the database administrator (DBA) shuts down the database.

Parts of a database trigger

A database trigger has the following parts:

• Trigger timing: When defining a trigger, the trigger timing can be specified by using the BEFORE or AFTER option. The trigger timing specifies whether the trigger action is to be executed before or after the triggering statement.



• Triggering event or statement: A triggering event or statement is a SQL statement, database event, or user event that causes a trigger to fire. A triggering event can be one or more of the following:
  
  
  • An INSERT, UPDATE, or DELETE statement on a specific table or view
  
  
  • A CREATE, ALTER, or DROP statement on any schema object
  
  
  • A database startup or instance shutdown
  
  
  • A specific error message or any error message
  
  
  • A user logon or logoff


• Trigger restriction: A trigger restriction specifies a Boolean expression that must be true for the trigger to fire. The trigger action is not run if the trigger restriction evaluates to false or unknown.



• Trigger action: A trigger action is the procedure (PL/SQL block, Java program, or C callout) that contains the SQL statements and code to be run when a triggering statement is issued and the trigger restriction evaluates to true. A trigger action can contain SQL, PL/SQL, or Java statements. It can define Java language constructs and PL/SQL language constructs such as variables, constants, cursors, and exceptions. It can also call stored procedures. If the trigger is a row trigger, the statements in a trigger action have access to column values of the row being processed by the trigger. Correlation names provide access to the old and new values of each column.

Types of triggers

Triggers can be classified on the basis of the following trigger components:

• Trigger timing


• Triggering event or statement

Based on the trigger timing, triggers are of the following two types:

• BEFORE trigger: A BEFORE trigger executes the trigger action before the triggering statement is executed. It is generally used when the trigger action determines whether the triggering statement should be allowed to complete. By using a BEFORE trigger for this purpose, the unnecessary processing of the triggering statement and its eventual rollback (in case an exception is raised in the trigger action) can be eliminated.
  
  BEFORE triggers are also used to derive specific column values before completing a triggering INSERT or UPDATE statement, to initialize global variables or flags, and to validate complex business rules.



• AFTER trigger: An AFTER trigger executes the trigger action after the triggering statement is executed. An AFTER trigger is generally used to perform different actions on the same triggering statement if a BEFORE trigger is already present.
  
  When using triggers to provide sophisticated auditing, AFTER triggers are given priority. By using AFTER triggers, auditing information is recorded after the triggering statement is subjected to applicable integrity constraints, thereby preventing cases where the audit processing is carried out unnecessarily for statements that generate exceptions to integrity constraints.

Note: BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.

Based on the triggering event or statement, triggers can be of the following types:

• DML trigger: A DML trigger is a database trigger whose triggering event is a Data Manipulation Language (DML) statement such as an INSERT, UPDATE, or DELETE statement. A DML trigger can be a statement trigger or a row trigger.
  
  
  1. Statement trigger: A statement trigger defined for a table is fired once on behalf of the triggering statement, regardless of the number of affected rows, even if no rows are affected at all. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.
     
     Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or affected rows. A statement trigger is generally used to make a complex security check on the current time or user, to generate a single audit record, etc.
  
  
  
  2. Row trigger: A row trigger defined for a table 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 does not affect any row, the row trigger is not fired.
     
     Row triggers are useful if the code in the trigger action depends on the data provided by the triggering statement or affected rows.
  
  
  BEFORE and AFTER options apply to both statement and row triggers. Hence, by using the BEFORE and AFTER options, the following four types of row and statement triggers can be created:
  
  
  1. BEFORE statement trigger: This trigger runs the trigger action before the triggering statement is run.
  
  
  2. BEFORE row trigger: This trigger runs the trigger action before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, provided the trigger restriction was not violated.
  
  
  3. AFTER statement trigger: This trigger runs the trigger action after the triggering statement is run and integrity constraints, if any, are applied.
  
  
  4. AFTER row trigger: This trigger runs the trigger action after modifying each row affected by the triggering statement and applying integrity constraints, provided the trigger restriction was not violated. Unlike BEFORE ROW triggers, AFTER ROW triggers lock rows.
  
  A DML trigger can also be defined on a view by using the INSTEAD OF option. A DML trigger using the INSTEAD OF option is known as an INSTEAD OF trigger.
  
  
  • INSTEAD OF trigger: An INSTEAD OF trigger provides a transparent way of modifying a view that cannot be modified directly through DML (Data Manipulation Language) statements such as INSERT, UPDATE, and DELETE. An INSTEAD OF trigger is so called because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.
    
    An INSTEAD OF trigger defined on a view is fired when an INSERT, UPDATE, or DELETE statement is executed against the view. When fired, the INSTEAD OF trigger modifies the underlying tables appropriately. An INSTEAD OF trigger defined on a view is always a row trigger, i.e., the trigger is fired for each modified row of the view.
    
    INSTEAD OF triggers can be designed only for views and not for tables. The BEFORE and AFTER options cannot be used with INSTEAD OF triggers.


• DDL trigger: A DDL trigger is a database trigger whose triggering event is a Data Definition Language (DDL) statement, i.e., a DDL trigger fires when a DDL statement (such as a CREATE, ALTER, or DROP statement) is executed in the database or a particular schema.
  
  

  DDL Event	Function
  CREATE	Causes the Oracle server to fire the trigger whenever a CREATE statement creates a new database object in the database or schema.
  ALTER	Causes the Oracle server to fire the trigger whenever an ALTER statement modifies a database object in the database or schema.
  DROP	Causes the Oracle server to fire the trigger whenever a DROP statement removes a database object from the database or schema.

  
  A DDL trigger can be created at either the database level or schema level. The BEFORE or AFTER option can also be specified for defining the timing of the trigger.
  
  A DDL trigger fires only if the database object being created is a cluster, function, index, package, procedure, role, sequence, synonym, table, tablespace, trigger, type, view, or user



• System event trigger: A system event trigger is a database trigger defined on a system event. A system event trigger fires when a system event (such as startup, shutdown, logon, logoff, or error) occurs. Database triggers on system events can be defined at the database or schema level. For example, a trigger on database startup or shutdown can be defined only at the database level. However, a trigger on user logon or logoff can be defined at either the database level or schema level.
  
  Database triggers can be created on the following system events:
  
  

  System Event	Description
  SERVERERROR	Causes the Oracle server to fire the trigger whenever a server error message is logged.
  LOGON	Causes the Oracle server to fire the trigger whenever a user logs on to the database.
  LOGOFF	Causes the Oracle server to fire the trigger whenever a user logs off the database.
  STARTUP	Causes the Oracle server to fire the trigger whenever the database is opened.
  SHUTDOWN	Causes the Oracle server to fire the trigger whenever the database is shut down.

  
  Note:
  
• The trigger on a SERVERERROR event can only be an AFTER trigger.


• The trigger on a LOGON event can only be an AFTER trigger.


• The trigger on a LOGOFF event can only be a BEFORE trigger.


• The trigger on a STARTUP event can only be an AFTER trigger.


• The trigger on a SHUTDOWN event can only be a BEFORE trigger.

A database trigger can be a system trigger on a database or schema. A database trigger defined on a database fires for each event for all users. However, a database trigger defined on a schema fires for each event for that particular user.

Rating: