Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Database Triggers PL/SQL – Part 4

Posted by Ravi Varma Thumati on November 11, 2009

Trigger defines an action the database should take when some database-related event occurs. Triggers may be used to supplement declarative referential integrity, to enforce complex business rules, or to audit changes to data. The code within a trigger, called a trigger body, is made up of PL/SQL blocks. It’s like a stored procedure that is fired when an insert, update or delete command is issued against associated table.

The execution of triggers is transparent to the user. Triggers are executed by the database when specific types of data manipulation commands are performed on specific tables. Such commands may include insert, update, and delete. Updates of specific columns may also be used as triggering events.

Because of their flexibility, triggers may supplement referential integrity; they should not be used to replace it. When enforcing the business rules in an application, you should first rely on the declarative referential integrity available with Oracle; use triggers to enforce rules that cannot be coded through referential integrity.

In other words, a trigger is a PL/SQL block that is associated with a table, stored in a database and executed in response to a specific data manipulation event. Triggers can be executed, or fired, in response to the following events:

  • A row is inserted into a table
  • A row in a table is updated
  • A row in a table is deleted

It is not possible to define a trigger to fire when a row is selected.

A database trigger has three parts namely a trigger statement, a trigger body and a trigger restriction. Trigger statement specifies the DML statements like insert, update, delete and it fire the trigger body. It also specifies the table to which the trigger associated. Trigger body is a PL/SQL bock that is executed when a triggering statement is issued. Restrictions on a trigger can be achieved using the WHEN clause as shown in the syntax for creating triggers. They can be included in the definition of a row trigger, wherein, the condition in the WHEN clause is evaluated for each row that is effected by the trigger.

A trigger is a database object, like a table or an index. When you define a trigger, it becomes a part of the database and is always executed when the event for which it is defined occurs. It doesn’t matter if the event is triggered by someone typing in a SQL statement using SQL* Plus, running a Client/Server program that updates the database, or running a utility like Oracle’s SQL Loader in order to bulk-load data. Because of this, triggers serves as a choke point, allowing you to perform critical data validation or computations in response to database changes, no matter what the source.

Types of Triggers

A trigger’s type is defined by the type of triggering transaction and by the level at which the trigger is executed. In the following sections, you will see descriptions of these classifications, along with relevant restrictions.

Row-Level Triggers

Row-level triggers execute once for each row in a transaction. Row-level triggers are the most common type of trigger; they are often used in data auditing applications. Row-level triggers are also useful for keeping distributed data in sync. Row-level triggers are created using the for each row clause in the create trigger command.

Statement-Level Triggers

Statement-level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into a table, then a statement-level trigger on that table would only be executed once. Statement-level triggers therefore are not often used for data-related activities; they are normally used to enforce additional security measures on the types of transactions that may be performed on a table. Statement-level triggers are the default type of trigger created via the create trigger command.

BEFORE and AFTER Triggers

Because triggers are executed by events, they may be set to occur immediately before or after those events. Since the events that execute triggers include database transactions, trigger can be executed immediately before or after insert, update and delete. For database-level events, additional restrictions apply; you cannot trigger an event to occur before a logon or startup takes place.

Within the trigger, you can reference the old and new values invoked by the transaction. The access required for the old and new data may determine which type of trigger you need. “Old” refers to the data, as it existed prior to the transaction; updates and deletes usually reference old values. “New” values are the data values that the transaction creates (such as the columns in an inserted record).

If you need to set a column value in an inserted row via your trigger, then you need to use a BEFORE INSERT trigger to access the “new” values. Using an AFTER INSERT trigger would not allow you to set the inserted value, since the row will already have been inserted into the table.

AFTER row-level triggers are frequently used in auditing applications, since they do not fire until the row has been modified. The row’s successful modification implies that it has passed the referential integrity constraints defined for that table.

Together with commonly used four types, it gives a total of 12 possible trigger types, which are listed in the following Table. Note that the SELECT statement is the only data manipulation statement for which no triggers can be defined.

  • Before update row
  • Before update statement
  • Before insert row
  • Before insert statement
  • Before delete row
  • Before delete statement
  • After update row
  • After update statement
  • After insert row
  • After insert statement
  • After delete row
  • After delete statement

Also note that one trigger can be defined to fire for more than one SQL statement.

INSTEAD OF Triggers

You can use INSTEAD OF triggers to tell Oracle what to do instead of performing the actions that invoked the trigger. For example, you could use an INSTEAD OF trigger on a view to redirect inserts into table or to update multiple tables that are part of a view. You can use INSTEAD OF triggers on either object views or relational views.

For example, if a view involves a join of two tables, your ability to use the update command on records in the view is limited. However, if you use an INSTEAD OF trigger, you can tell Oracle how to update, delete, or insert records in the view’s underlying tables when a user attempts to change values via the view. The code in the INSTEAD OF trigger is executed in place of the update, delete, or insert command you enter.

Uses of Triggers

The possible uses for database triggers are varied and are limited only by your imagination. Some common uses are listed below:

  • Enforcing business rules
  • Maintaining referential integrity
  • Enforcing security
  • Maintaining a historical log of changes
  • Generating column values, including primary key values
  • Replicating data

Syntax:

Create [ or replace ] trigger [user.]trigger_name

{ before | after | instead of }

{ delete | insert | update [ of column [, column] …] }

on [user.]{ Table | View }

for each { row | statement }

[ when (condition) ]

PL/SQL Block

Clearly, there is a great deal of flexibility in the design of a trigger. The before and after keywords indicate whether the trigger should be executed before or after the triggering transaction. If the instead of clause is used, the trigger’s code will be executed instead of the event that caused the trigger to be invoked. The delete, insert, and update keywords (the last of which may include a column list) indicate the type of data manipulation that will constitute a trigger event.

When the for each row clause is used, the trigger will be a row-level trigger; otherwise, it will be a statement-level trigger. The when clause is used to further restrict when the trigger is executed. The restrictions enforced in the when clause may include checks of old and new data values.

For example, suppose we want to monitor any adjustments to Salary column value that are greater than 10 percent. The following row-level BEFORE UPDATE trigger will be executed only if the new value of the salary column is more than 10 percent greater than its old value and add transaction details in audit table. This example also illustrates the use of the new keyword, which refers to the new value of the column, and the old keyword, which refers to the original value of the column.

Example

Create or replace trigger emp_salary_update_row

before update on emp

for each row

when (:New.Amount / :Old.Amount > 1.1)

usr varchar2(20);

Begin

Select user into usr from dual;

Insert into EMP_AUDIT values (:Old.salary, :New.salary, :Old.eno,

usr, to_char(sysdate,’HH:MI’),sysdate);

Commit;

End;

/

Trigger created.

Breaking the above created trigger command makes it easier to understand. Let’s do it:

Create or replace trigger emp_salary_update_row

The emp_salary_update_row is the trigger name, which indicates table name and it acts upon and

the type of trigger. One can define trigger with any valid name.

before update on emp

above statement indicates that this trigger applies to the Emp table and it will executed before

update transactions have been committed to the database.

for each row

Because of above statement, the trigger will apply to each row in the transaction. If this clause is

not used, then the trigger will execute at the statement level.

The When clause adds further criteria to the triggering condition. The triggering event not only must

be an update of the Ledger table, but also must reflect an increase of over 10 percent in the value of

the Amount column.

when (New.Amount / Old.Amount > 1.1)

The PL/SQL code shown in the following listing is the trigger body. The commands shown here are

to be executed for every update of the emp table that passes the when condition. For this to succeed,

the EMP_AUDIT table must exist, and the owner must have been granted privileges on that

table. This example inserts the old values from the emp record into EMP_AUDIT table before the

employee record is updated in emp table. Structure of EMP_AUDIT table is as per follows.

EMP_AUDIT

eno number(5)

old_salary number(9,2)

new_salary number(9,2)

user varchar2(20)

tr_time varchar2(10)

tr_date date

Begin

Select user into usr from dual;

Insert into EMP_AUDIT values (:Old.eno, :Old.salary,

:New.salary, usr, to_char(sysdate,’HH:MI’),sysdate);

commit;

End;

/

Above trigger makes the log of the emp table in the EMP_AUDIT table and maintains the track of updation in the table, the user name, Transaction Date, Transaction Time, All the old and new values of columns.

  • When referencing the New and Old keywords in the PL/SQL block, they are preceded by the colons(:) Using :Old and :New Values in Row Level Triggers

When Row level trigger fires once per row processed by the triggering statement. Inside the trigger, you can access the row that is currently being processed. This is done through keywords :new and :old.

Following describes what values are set to :Old and :New with the given triggering statement.

Triggering Statement

INSERT

: Old Undefined – all fields are NULL

: New Values that will be inserted when the statement is complete

UPDATE

: Old Original values for the row before the update

: New New values that will be updated when the statement is complete

DELETE

: Old Original values before the row is deleted

: New Undefined – all fields are NULL

They are valid only within row level triggers and not in statement level triggers. :Old values are not available if the triggering statement is INSERT and :new values are not available if the triggering statement is DELETE. Each column is referenced by using the expression :Old.ColumnName or :New.ColumnName. if a column is not updated by the triggering update statement then :old and :new values remain the same.

An Example of a Trigger with :New

Suppose for a moment that you wanted to be sure that all department names were stored using uppercase letters. Perhaps you are doing this to facilitate searching on that field. Following example shows one way to do this with a trigger.

Example

Create or replace trigger upperdname before insert or update on

dept for each row

Begin

:new.dname := upper(:new.dname);

End;

/

Trigger created.

Example

Following trigger does not allow the user to delete any row from a Try table.

Create Or Replace Trigger delete_rest_trig Before Delete On Try

Begin

Raise_Application_Error(-20011,’UNABLE TO DELETE’);

End;

/

In the above example, a new Term is used i.e. Raise_Application_Error()

Let’s discuss it in details:

Customizing Error Conditions

Oracle provides a procedure named raise_application_error that allows programmers to issue user-defined error messages.

Syntax :

Raise_Application_Error(Error_number, Message);

Where Error_number is a negative integer in the range –20000 to –20999. and Message Is a string up to 2048 bytes in length.

An application can call “Raise_Application_Error” only from an executing stored subprogram like stored procedures and functions, database triggers. Typically “Raise_Application_Error” is used in database triggers.

“Raise_Application_Error” ends the subprogram, rolls back any database changes it made, and returns a user-defined error number and message to the application.

Within a single trigger, you may establish different error conditions. For each of the error conditions you define, you may select an error message that appears when the error occurs. The error numbers and messages that are displayed to the user are set via the Raise_Application_Error procedure, which may be called from within any trigger.

Following example shows a statement-level BEFORE UPDATE or INSERT trigger on the Emp table. When a user attempts to Insert, modify or delete a row in the Emp table, this trigger is executed and checks two system conditions; that the day of the week is neither Saturday nor Sunday, and that the Oracle username is other than user ‘ADMIN’.

Example

Create or replace trigger emp_rest before insert or update or

delete on Emp

Declare

Weekend_Error Exception;

Invalid_User Exception;

Begin

If to_char(SysDate, ‘DY’) in (‘sat’,’sun’) then

Raise Weekend_Error;

End if;

If upper(User) != ‘ADMIN’ then

Raise Invalid_User;

End if;

Exception

When Weekend_Error then

Raise_Application_Error(-20001,’No Insertion or

Updation on Weekends’);

When Invalid_User then

Raise_Application_Error(-20002,’Insertion, Updation or

deletion only allowed to Admin Users’);

End;

/

Trigger created.

Study following code and find out use of following Trigger

Create or replace trigger check_date_time before insert or update

or delete on dept

Begin

If to_number(to_char(sysdate,’hh24′))>not in(10,18) or

to_char(sysdate, ‘dy’) not in (‘sun’, ‘sat’) then

raise_application_error(-20001,’no manipulations allowed in

table in non-office working hours’);

End if;

End;

Firing Triggers selectively using Trigger Predicates

Problem

We have a lot of tables in the database. Instead of writing three different triggers for INSERT, UPDATE, and DELETE operations, we want to write one trigger for each table in the system, and that trigger should handle any DML operations on that table individually. We need to know how to create such triggers and in the trigger body how to recognize the type of DML operation that caused the trigger to fire. How to fire triggers selectively using trigger predicates?

Solution

You can write a single trigger to handle multiple DML operations on a table. For instance, an INSERT,DELETE or UPDATE statement can fire the same trigger with the user of the ON INSERT OR DELETE OR UPDATE OF clause while creating the trigger. The trigger body can use the conditional predicates INSERTING,DELETING, AND UPDATING to execute specific blocks of code, depending upon the triggering statement.

Example

Create or replace trigger find_tran before insert or update or

delete on dept for each row

Begin

If Inserting then

raise_application_error(-20001,’Insertion Restricted’);

elsif updating then

raise_application_error(-20002,’Updation Restricted’);

elsif deleting then

raise_application_error(-20003,’Deletion Restricted’);

end if;

End;

/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: