DATABASE TRIGGERS

DATABASE TRIGGERS

Triggers are similar to procedures or functions in that they are named PL/SQL blocks with declarative, executable, and exception handling sections. A trigger is executed implicitly whenever the triggering event happens. The act of executing a trigger is known as firing the trigger.

RESTRICTIONS ON TRIGGERS

  1. Like packages, triggers must be stored as stand-alone objects in the database and cannot be local to a block or package.
  2. A trigger does not accept arguments.

USE OF TRIGGERS

  1. Maintaining complex integrity constraints not possible through declarative constraints enable at       table creation.
  2. Auditing information in a table by recording the changes made and who made them.
  3. Automatically signaling other programs that action needs to take place when changes are made to a table.
  4. Perform validation on changes being made to tables.
  5. Automate maintenance of the database.

TYPES OF TRIGGERS

  1. DML Triggers
  2. Instead of Triggers
  3. DDL Triggers
  4. System Triggers
  5. Suspend Triggers

CATEGORIES
    Timing — Before or After
    Level — Row or Statement

Row level trigger fires once for each row affected by the triggering statement. Row level trigger is identified by the FOR EACH ROW clause.

Statement level trigger fires once either before or after the statement.

DML TRIGGER SYNTAX
Create or replace trigger <trigger name>
{Before | after} {Insert or update or delete} on <table name>
[For each row]
[When (…)]
[Declare]
— Declaration
Begin
— Trigger body
[Exception]
— Exception section
End <trigger name>;

DML TRIGGERS
A DML trigger is fired on an INSERT, UPDATE, or DELETES operation on a database table. It can be fired either before or after the statement executes, and can be fired once per affected row, or once per statement.

The combination of these factors determines the types of the triggers. These are a total of 12 possible types (3 statements * 2 timing * 2 levels).

STATEMENT LEVEL
Statement level trigger fires only once.

 ROW LEVEL

Row level trigger fires once for each row affected by the triggering statement.

ORDER OF DML TRIGGER FIRING

  1. Before statement level
  2. Before row level
  3. After row level
  4. After statement level

 CORRELATION IDENTIFIERS IN ROW-LEVEL TRIGGERS

Inside the trigger, you can access the data in the row that is currently being processed. This is accomplished through two correlation identifiers – :old and :new.

A correlation identifier is a special kind of PL/SQL bind variable. The colon in front of each indicates that they are bind variables, in the sense of host variables used in embedded PL/SQL, and indicates that they are not regular PL/SQL variables. The PL/SQL compiler will treat them as records of type

Triggering_table%ROWTYPE.

Although syntactically they are treated as records, in reality they are not. :old and :new are also known as pseudorecords, for this reason.

TRIGGERING STATEMENT :OLD :NEW
————————————– —————————- ———————————————–
INSERT all fields are NULL. values that will be inserted when the
statement is completed.
—————————————————————————————————————————-
UPDATE original values for the new values that will be updated when
row before the update the statement is completed.
—————————————————————————————————————————-
DELETE original values before all fields are NULL.
the row is deleted.
—————————————————————————————————————————-

 REFERENCING CLAUSE

If desired, you can use the REFERENCING clause to specify a different name for :old ane :new. This clause is found after the triggering event, before the WHEN clause.

Syntax:
REFERENCING [old as old_name] [new as new_name]

 WHEN CLAUSE

WHEN clause is valid for row-level triggers only. If present, the trigger body will be executed only for those rows that meet the condition specified by the WHEN clause.

Syntax:
WHEN trigger_condition;

Where trigger_condition is a Boolean expression. It will be evaluated for each row. The :new and :old records can be referenced inside trigger_condition as well, but like REFERENCING, the colon is not used there. The colon is only valid in the trigger body.

 TRIGGER PREDICATES

There are three Boolean functions that you can use to determine what the operation is.
The predicates are
     1 INSERTING
     2 UPDATING
     3 DELETING

 

 INSTEAD-OF TRIGGERS

Instead-of triggers fire instead of a DML operation. Also, instead-of triggers can be defined only on views. Instead-of triggers are used in two cases:
1 To allow a view that would otherwise not be modifiable to be modified.
2 To modify the columns of a nested table column in a view.

 DDL TRIGGERS

Oracle allows you to define triggers that will fire when Data Definition Language statements are executed.

Syntax:

Create or replace trigger <trigger_name>
{Before | after} {DDL event} on {database | schema}
[When (…)]
[Declare]
— declaration
Begin
— trigger body
[Exception]
— exception section
End <trigger_name>;

 WHEN CLAUSE

If WHEN present, the trigger body will be executed only for those that meet the condition specified by the WHEN clause.

 SYSTEM TRIGGERS

System triggers will fire whenever database-wide event occurs. The following are the database event triggers. To create system trigger you need ADMINISTER DATABASE TRIGGER privilege.

  1.   STARTUP
  2.   SHUTDOWN
  3.   LOGON
  4.   LOGOFF
  5.   SERVERERROR

Syntax:
Create or replace trigger <trigger_name>
{Before | after} {Database event} on {database | schema}
[When (…)]
[Declare]
— declaration section
Begin
— trigger body
[Exception]
— exception section
End <trigger_name>;

 SERVERERROR

The SERVERERROR event can be used to track errors that occur in the database. The error code is available inside the trigger through the SERVER_ERROR attribute function.

 SERVER_ERROR ATTRIBUTE FUNCTION

It takes a single number type of argument and returns the error at the position on the error stack indicated by the argument. The position 1 is the top of the stack.

 SUSPEND TRIGGERS

This will fire whenever a statement is suspended. This might occur as the result of a space issue such as exceeding an allocated tablepace quota. This functionality can be used to address the problem and allow the operatin to continue.

Syntax:

Create or replace trigger <trigger_name>
after suspend on {database | schema}
[When (…)]
[Declare]
— declaration section
Begin
— trigger body
[Exception]
— exception section
End <trigger_name>;

 AUTONOMOUS TRANSACTION

Prior to Oracle8i, there was no way in which some SQL operations within a transaction could be committed independent of the rest of the operations. Oracle allows this, however, through autonomous transactions. An autonomous transaction is a transaction that is started within the context of another transaction, known as parent transaction, but is independent of it. The autonomous transaction can be committed or rolled back regardless ot the state of the parent transaction.

 RESTRICTIONS ON AUTONOMOUS TRANSACTION

  1. If an autonomous transaction attempts to access a resource held by the main transaction, a deadlock can occur in you program.
  2. You cannot mark all programs in a package as autonomous with a single PRAGMA declaration. You must indicate autonomous transactions explicity in each program.
  3. To exit without errors from an autonomous transaction program that has executed at least one INSERT or UPDATE or DELETE, you must perform an explicit commit or rollback.
  4. The COMMIT and ROLLBACK statements end the active autonomous transaction, but they do not force the termination of the autonomous routine. You can have multiple COMMIT and/or ROLLBACK statements inside your autonomous block.
  5. You can not rollback to a savepoint set in the main transaction.
  6. The TRANSACTIONS parameter in the oracle initialization file specifies the maximum number of transactions allowed concurrently in a session. The default value is 75 for this, but you can increase the limit.

MUTATING TABLES
There are restrictions on the tables and columns that a trigger body may access. In order to define these restrictions, it is necessary to understand mutating and constraining tables.
A mutating table is table that is currentlty being modified by a DML statement and the trigger event also DML statement. A mutating table error occurs when a row-level trigger tries to examine or change a table that is already undergoing change.
A constraining table is a table that might need to be read from for a referential integrity constraint.

 HOW TO AVOID MUTATING TABLE ERROR ?

  • By using autonomous transaction
  • By using statement level trigger
  

Related posts:

Leave a Reply