Trigger Database Management System

Trigger Database Management System

TRIGGER Database Management System

TRIGGER

A trigger:

  • Is a PL/SQL block or a PL/SQL procedure associated with a table, view, Schema, or the database
  • Executes implicitly whenever a particular event takes place
  • Can be either:

Application trigger: Fires whenever an event occurs with a particular application

Database trigger: Fires whenever a data event (such CSDML) or system event (such as logon Or shutdown) occurs on a Schend or database.

Types of Triggers

Application Trigger

TRIGGER Database Management System : Application triggers execute 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.

Database triggers execute implicitly when a data event such as DML on a table (an INSERT, UPDATE, or DELETE triggering Statement), an INSTEAD OF trigger on a view, or data definition language (DDL) statements such as CREATE and ALTER are issued, no matter which user is connected or which application is used. Database triggers also execute implicitly when some user actions or database System actions occur, for example, when a user logs on, or the DBA shut downs the database.

Note: Database triggers can be defined on tables and on views. If a DML operation is issued on a view, the INSTEAD OF trigger defines what actions take place. If these actions include DML operations on tables, then any triggers on the base tables are fired.

Database triggers can be system triggers on a database or a Schema. With a database, triggers fire for each event for all users, with a Schema, triggers fire for each event for that Specific user.

Database Trigger

Before coding the trigger body, decide on the values of the components of the trigger: the trigger timing, the triggering event, and the trigger type.

TRIGGER Database Management System

If multiple triggers are defined for a table, be aware that the order in which multiple triggers of the same pe fire is arbitrary. To ensure that triggers of the same type are fired in a particular order, consolidate the triggers into one trigger that Calis separate procedures in the desired order.

BEFORE Triggers

This type of trigger is frequently used in the following situations:

  • To determine whether that triggering Statement should be allowed to complete. (This situation enables you to eliminate unnecessary processing of the triggering statement and its eventual rollback in Cases where an exception is raised in the triggering action.)
  • To derive Column values before completing a triggering INSERT or UPDATE statement.
  • To initialize global variables or flags, and to validate complex business rules.

AFTER Triggers

This type of trigger is frequently used in the following situations:

  • To complete the triggering statement before executing the triggering action.
  • To perform different actions on the same triggering statement if a BEFORE trigger is already present.

INSTEAD OF Triggers

This type of trigger is used to provide a transparent way of modifying views that cannot be modified directly through SOLDML statements because the view is not inherently modifiable. You can write INSERT, UPDATE, and DELETE statements against the view. The INSTEAD OF trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables.

DML Trigger Components

Triggering user event: Which DML statement causes the trigger to execute? You can use any of the following:

  • INSERT
  • UPDATE
  • DELETE

 

The Triggering Event

The triggering event or statement can be an INSERT, UPDATE, or DELETE statement on a table.

  • When the triggering event is an UPDATE statement, you can include a column list to identify which columns must be changed to fire the trigger. You cannot specify a column list for an INSERT or for a DELETE statement, because they always affect entire rows.

…UPDATE OF Sal…

  • The triggering event can contain one, two, or all three of these DML operations.

.. INSERT or UPDATE or DELETE

…INSERT or UPDATE OF job_id…

 

DML Trigger Components

Trigger type: 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.

 

Statement Triggers and Row Triggers

You can specify that the trigger will be executed once for every row affected by the triggering statement (such as a multiple row UPDATE) or Once for the triggering Statement, no matter how many rows it affects.

Statement Trigger

A statement trigger is fired once on behalf of the triggering event, even if no rows are affected at all.

Statement triggers are useful if the trigger action does not depend on the data from rows that are affected or on data provided by the triggering event itself: for example, a trigger that performs a complex Security check on the Current user.

Row Trigger

A row trigger fires each time the table is affected by the triggering event. If the triggering event affects no rows, a row trigger is not executed.

Row triggers are useful if the trigger action depends on data of rows that are affected or on data provided by the triggering event itself.

Trigger Body

The trigger action defines what needs to be done when the triggering eventis issued. The PL/SQL block can contain SQL and PL/SQL statements, and can define PL/SQL constructs such as variables, Cursors, exceptions, and so on. You can also call a PL/SQL procedure or a Java procedure.

Additionally, row triggers use correlation names to access the old and new column values of the row being processed by the trigger.

Note: The size of a trigger cannot be more than 32 K.

Example:

/* This trigger track changes being made to the Emp table, and stores this information in Audit table and Audit table values.

CREATE OR REPLACE TRIGGER emp audit

AFTER INSERT OR UPDATE OR DELETE ON emp

FOR EACH ROW

DECLARE

Time_now date;

Terminal char(10);

BEGIN

Time now:=SYSDATE,

Terminal:=USERENV(TERMINAL”);

IF INSERTING THEN

INSERT INTO audit_table

VALUES(audit seq. NEXTVAL, user, time now, terminal,

“EMP”, “INSERT, new.empno);

ELSE IF DELETING THEN

INSERT INTO audit_table

VALUES(audit seq.NEXTVAL user, time now, terminal,

“EMP”,”DELETE”, old.empno);

ELSE

INSERT INTO audit_table

VALUES (audit seq.NEXTVAL, user, time now, terminal,

EMP, UPDATE, old.empno);

IF UPDATING (‘SAL”) THEN

INSERT INTO audit table values

VALUES(audit_seq. CURRVAL, “SAL’, :old.sal, :new.sal)

ELSE IF UPDATING(“DPETINO”) THEN

INSERT INTO audit_table values

VALUES(audit_seq.CURRVAL, ‘DEPTNO’,:old.deptno, new.deptno);

END IF;

END IF;

END;

/