Saturday, 28 January 2017


What is a Database Trigger?
Database trigger is a PL/SQL block that is executed on an event in the database.  The event is related to a particular data manipulation of a table such as inserting, deleting or updating a row of a table.

Triggers may be used for any of the following:

*  To implement complex business rule, which cannot be implemented using integrity constraints.
*  To audit the process.  For example, to keep track of changes made to a table.
*  To automatically perform an action when another concerned action takes place.  For example, updating a table whenever there is an insertion or a row into another  table.

Triggers are similar to stored procedures, but stored procedures are called explicitly and triggers are called implicitly by Oracle when the concerned event occurs.

Note: Triggers are automatically executed by Oracle and their execution is transparent to users.
Types of Triggers
Depending upon, when a trigger is fired, it may be classified as :

*  Statement-level trigger
*  Row-level trigger
*  Before triggers
*  After triggers

Statement-level Triggers
A statement trigger is fired only for once for a DML statement irrespective of the number of rows affected by the statement.

For example, if you execute the following UPDATE command STUDENTS table, statement trigger for UPDATE is executed only for once.

update students   set  bcode=’b3’
where bcode = ‘b2’;

However, statements triggers cannot be used to access the data that is being inserted, updated or deleted.  In other words, they do not have access to keywords NEW and OLD, which are used to access data.

Statement-level triggers are typically used to enforce rules that are not related to data. For example, it is possible to implement a rule that says “no body can modify BATCHES table after 9 P.M”.

Statement-level trigger is the default type of trigger.

Row-level Trigger
A row trigger is fired once for each row that is affected by DML command.  For example, if an UPDATE command updates 100 rows then row-level trigger is fired 100 times whereas a statement-level trigger is fired only for once.

Row-level trigger are used to check for the validity of the data. They are typically used to implement rules that cannot be implemented by integrity constraints.

Row-level triggers are implemented by using the option FOR EACH ROW in CREATE TRIGGER statement.

Before Triggers
While defining a trigger, you can specify whether the trigger is to be fired before the command  (INSERT, DELETE, and UPDATE) is executed or after the command is executed.

Before triggers are commonly used to check the validity of the data before the action is performed. For instance, you can use before trigger to prevent deletion of row if deletion should not be allowed in the given case.

AFTER Triggers
After triggers are fired after the triggering action is completed. For example, If after trigger is associated with INSERT command then it is fired after the row is inserted into the table.

Possible Combinations
The following are the various possible combinations of database triggers.

*  Before Statement
*  Before Row
*  After Statement
*  After Row

Note: Each of the above triggers can be associated with INSERT, DELETE, and UPDATE commands resulting in a total of 12 triggers.

In the next section, we will see how to create database triggers.

Creating a Database Trigger
CREATE TRIGGER command is used to create a database trigger. The following details are to be given at the time of creating a trigger.

*  Name of the trigger
*  Table to be associated with
*  When trigger is to be fired - before or after
*  Command that invokes the trigger - UPDATE, DELETE, or INSERT
*  Whether row-level trigger or not
*  Condition to filter rows.
*  PL/SQL block that is to be executed when trigger is fired.

The following is the syntax of CREATE TRIGGER command.

        [OR {DELETE | INSERT |UPDATE [OF columns]}]...
ON table
[FOR EACH ROW [WHEN condition]]
PL/SQL block

If FOR EACH ROW option is used then it becomes a row-level trigger otherwise it is a statement-level trigger.

WHEN is used to fire the trigger only when the given condition is satisfied.  This clause can be used only with row triggers.

For example, the following trigger is fired only when AMOUNT is more than 1000.

create or replace trigger ..
before insert on payments
for each row
when  :new.amount > 1000

OF option allows you to specify updation of which columns will fire trigger. The list of columns can be given by separating column by comma.

REFERENCING is used to use new names instead of default correlation names OLD and NEW. See the section  “Correlation Names “

The following is a simple database trigger that is used to check whether date of joining of the student is less than or equal to system date. Otherwise it raises an error.

create or replace trigger students_bi_row
before insert
on students
for each row
    if > sysdate then
           (-20002,'Date of joining cannot be after system date.');
    end if;

STUDENTS_BI_ROW is the name of the trigger. It represents table name – STUDENTS, event of the trigger – BI (before insert) and type – ROW. Though trigger name can be anything, it is better you follow a convention while naming it.

FOR EACH ROW specifies that this trigger is a row-level trigger.

Condition :NEW.DJ > SYSDATE checks whether the value of DJ of the row being inserted is greater than system date. If the condition is true then it raises an error using RAISE_APPLICATION_ERROR procedure. 

Note: Use data dictionary view USER_TRIGGERS to list the existing triggers.

The following command can be used to display the name and body of each trigger in the current account.

select trigger_name, trigger_body from user_trigger;

Column TRIGGER_BODY is of LONG type. How many characters of this column will be displayed is determined by variable LONG in SQL*Plus.  Set variable LONG to a larger value to see the complete body of the trigger. The default value of LONG is 80.

The following is another trigger used to implement a rule that cannot be implemented by integrity constraints. The trigger checks whether payment made by the student is more than what the student is supposed to pay.

create or replace trigger payments_bi_row
before insert
on payments
for each row
  v_dueamt number(5);
   v_dueamt := getdueamt(:new.rollno);
   if  :new.amount > v_dueamt then
          raise_application_error(-20112,'Amount being paid is more than what is to be paid');
   end if;

The above trigger makes use of GETDUEAMT function that we created in the previous chapter.  It first gets the due amount of the given roll number. If the amount being paid – values of AMOUNT column – is more than the due amount then trigger fails.

Statement-level trigger example
The following is an example of statement-level trigger. As we have seen in the previous section, a statement trigger is fired only for once for the entire statement irrespective of the number of rows affected by the statement.

create or replace trigger payments_biud
before insert or update or delete
on payments
  if to_char(sysdate,'DY') = 'SUN' then
    raise_application_error(-20111,'No changes can be made on sunday.');
  end if;

The above trigger is fired whenever an UPDATE or DELETE or INSERT command is executed on PAYMENTS table. Trigger checks whether day of week of the system date is Sunday.  If condition is true then it raises an application error. Since the trigger is not concerned with data, it uses a statement-level trigger.

Valid Statements in trigger body
The following are the only valid statements in trigger body. Trigger can make use of existing stored procedures, functions and packages (as shown in the previous example).

q  DML commands
q  SELECT INTO command

Correlation Names
The default correlation names are NEW for new values and OLD for old values of the row. So, in order to access the values of new row use NEW and to access the values of old (existing) row, use OLD.

It is also possible to change these correlation names using REFERENCING option of CREATE TRIGGER command. This is done mainly to avoid names conflicts between table names and correlation names.

See the table 1, to understand the availability of correlation name with triggering commands.


Table 1: Availability of correlation names

NEW is not available with DELETE command because there are no new values. In the same way, OLD is not available with INSERT because a new row is being inserted and it doesn’t contain old values. Triggers based on UPDATE command can access both NEW and OLD correlation names. OLD refers to values that refer to values before the change and NEW refers to values that are after the change.

The following trigger prevents any change to AMOUNT column of PAYMENTS table. This is done by comparing old value with new value and if they differ that means the value is changed and trigger fails.

create or replace trigger payments_bu_row
before update
on payments
for each row
   if  :new.amount <> :old.amount then
          raise_application_error(-20113,'Amount cannot be changed. Please delete and reinsert the row, if required');
   end if;

Instead-of Trigger
These trigger are defined on relation-views and object-views.  These triggers are used to modify views that cannot be directly modified by DML commands. Unlike normal trigger, which are fired during the execution of DML commands, these triggers are fired instead of execution of DML commands. That means instead of executing DML command on the view, Oracle invokes the corresponding INSTEAD-OF trigger.

The following is a view based on STUDENTS and PAYMENTS tables.

create view  newstudent
select  s.rollno, name, bcode,gender, amount
from students s, payments p
where  s.rollno = p.rollno;
If you try to insert data into NEWSTUDENT table then Oracle displays the following error.

SQL> insert into newstudent values (15,'Joe','b2','m',2000);
insert into newstudent values (15,'Joe','b2','m',2000)
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

But, we want the data supplied to NEWSTUDENT view to be inserted into STUDENTS and PAYMENTS table. This can be done with an instead of trigger as follows:

create or replace trigger newstudent_it_bi_row
instead of insert
on newstudent
for each row
     -- insert into STUDENTS table first

     insert into students(rollno,bcode,name,gender,dj)
        values(:new.rollno, :new.bcode,, :new.gender,sysdate);

     -- insert a row into PAYMENTS table

     insert into payments
           values(:new.rollno, sysdate, :new.amount);

The above INSTEAD-OF trigger is used to insert one row into STUDENTS table with roll number, name, batch code and gender of the student. It then inserts a row into PAYMENTS table with roll number, date of payment  - sysdate, and amount.

Since we created an INSTEAD-OF trigger for INSERT command, Oracle invokes this trigger when an INSERT command is executed on NEWSTUDENTS view.

SQL> insert into newstudent values (15,'Joe','b2','m',2000);

So the above command inserts two rows – one into STUDENTS table and another into PAYMENTS table. The following two queries will ratify that.

select rollno, bcode,name, gender, dj
from students where rollno = 15;

   ROLLNO BCODE NAME                           G DJ
--------- ----- ------------------------------ - ---------
       15 b2    Joe                            m 30-Oct-01

select * from payments where rollno = 15;

   ROLLNO DP           AMOUNT
--------- --------- ---------
       15 30-OCT-01      2000
Knowing which command fired the trigger
When a trigger may be fired by more than one DML command, it may be required to know which DML command actually fired the trigger.

Use the following conditional predicates in the body of the trigger to check which command fired the trigger.


The following example is used to log information about the change made to table COURSES. The trigger makes use of a COURSES_LOG table, which is created with the following structure.

create table courses_log
(  cmd  number(1),
   pk   varchar2(20),
   dc   date,
   un   varchar2(20)

Now a row level trigger is used to insert a row into COURSES_LOG table whenever there is a change to COURSES table.
create or replace trigger  courses_biud_row
before insert or delete or update
on courses
for each row
    if  inserting then
            insert into courses_log values (1, :new.ccode,sysdate, user);
    elsif  deleting then
            insert into courses_log values(2,:old.ccode,sysdate,user);
            insert into courses_log values(3,:old.ccode,sysdate,user);
    end if;

After the trigger is created, if you execute an UPDATE command as follows:

update courses set fee = fee * 1.1 where ccode = 'ora';

it will fire COURSES_BIUD_ROW trigger, which will insert a row into COURSES_LOG table as follows:

SQL> select * from courses_log;

      CMD PK                   DC        UN
--------- -------------------- --------- --------------------
        3 ora                  30-OCT-01 BOOK

Enabling and disabling triggers
A database trigger may be either in enabled state or disabled state.

Disabling trigger my improve performance when a large amount of table data is to be modified, because the trigger code is not executed.

For example,

UPDATE students set name = upper(name);

will run faster if  all the triggers fired by UPDATE on STUDENTS table are disabled.

Use ALTER TRIGGER command to enable or disable triggers as follows:

alter trigger  payments_bu_row disable;

A disabled trigger is not fired until it is enabled.  Use the following command to enable a disabled trigger.

alter trigger payments_bu_row enable;

The following command disables all triggers of STUDENTS table.

alter table  students disable all triggers;

Dropping a Trigger
When a trigger is no longer needed it can be dropped using DROP TRIGGER command  as follows.

drop trigger payments_bu_row;
Database triggers are used to implement complex business rules that cannot be implemented using integrity constraints. Triggers can also be used for logging and to take actions that should be automatically performed.

Trigger may be fired before the DML operation or after DML operation. Trigger may be fired for each row affected by the DML operation or for the entire statement.

INSTEAD-OF trigger are called instead of executing the given DML command. They are defined on relational-views and object-views (will be discussed later in this book).

A trigger can be disabled to increase the performance when a lot of data manipulations are to be done on the table.

Friday, 27 January 2017


Oracle supports LOBs(Large Objects) which can hold large amount of raw binary data, such as  graphics images, as well as large amount of character data.

Oracle extended SQL DDL and DML to provide support for LOBs. You can also manipulate LOBs using DBMS_LOB package and OCI (Oracle Call Interface).

Depending upon the way in which LOBs are stored they can be classified as follows.

Internal LOBs
These are stored in the database tablespace. They support transaction processing like any other scalar data type. CLOB, BLOB and NCLOB belong to this category.

External LOBs
These are not stored in the database. Instead they are stored in the Operating System files. Only a pointer pointing to the actual data is stored in the database. They do not support transaction processing and integrity checking.  BFILE data type belongs to this category.

See figure 1, to understand how data is stored in internal lob and external lob.

LOB Datatypes
The following are the different LOB datatypes that are supported by Oracle8.

Data Type
The data is consisting of single-byte character data.
The data is consisting of multi-byte or single-byte fixed length character data that corresponds to the national character set.
The data is consisting of RAW binary data, such as bitmap images.
The data is stored in an operating system file. Only a reference to the file is stored in the database.  This is the example for External LOB.
Table 1: LOB Datatypes.

In the above example the table contains three columns – ID, CLOB_COL and BFILE_COL. CLOB_COL is of CLOB type and BFILE_COL is of BFILE type. CLOB column stores lob locator, which points to the location where the complete data is stored. BFILE column contains the name of the file (a.bmp) but the file is physically stored outside the database as a file in the operating systems file system.

LOB Locator
The data of the LOB column is NOT stored in the row along with the other columns of the row, instead only a locator is stored in the database and the actual data is stored elsewhere.  The locator is similar to a pointer and points to the location where the data is actually stored.  In case of Internal LOB the data is stored within the database, and in case of external LOB the data is stored outside the database as a file in the file system of the operating system.

The value that is stored in the row to point to the actual location of the Internal LOB is called as LOB Locator. It is used to locate the LOB data that is stored elsewhere in the database.
LONG datatype and LOBs are similar in some respects and differ in other. LOB data types can be taken as an extension to LONG RAW data type. LONG RAW is the only data type that supported large binary data in Oracle7. The following are the differences between LONG RAW data type and LOB data types.

LONG type
LOB type
Can contain up to 2 GB of data
Can contain up to 4 GB of data
A table can contain only one LONG column
A table can contain more than one LOB column
A sub-query cannot select a LONG column
A subquery can select LOB column

 Defining and Manipulating LOBs
A LOB column is defined just like any other column. Data of the LOB column can be accessed either directly or through DBMS_LOB package. Let us first see how to create a table with LOB columns.

The following example creates a table with a CLOB type column and a BFILE type column.

create table  lob_table
(     id    number(5), 
      clob_col     clob,
      bfile_col    bfile

The following functions are used to manipulate LOB columns.

These functions are part of the SQL DML. They are used to initialize LOB locator to empty locator in INSERT and UPDATE commands.

Note: Before you start writing data to a LOB using either OCI or DBMS_LOB  package, the LOB column must be initialized to empty locator using these functions.

The following example stores empty locator into CLOB_COL.

insert into lob_table (id, clob_col)
      values (100, empty_clob());

This is used to initialize a BFILE column.  This is used in SQL INSERT and UPDATE commands.

BFILENAME( Directory_alias, Filename)   RETURN   BFILE;

Is the alias of the directory in which the file is stored. This directory alias is created using CREATE DIRECTORY command.
Is the name of the file, which contains the contents that are accessed using BFILE column.

insert into  lob_table 
 values( 101, EMPTY_CLOB(), BFILENAME(‘IMAGES’,’BMP1.BMP’));

The above INSERT command inserts a row into LOB_TABLE in which BFILE_COL refers to BMP1.BMP file in directory with alias  IMAGES.

Directory Alias
Directory alias is to be created using CREATE DIRECTORY command. Directory alias is used to refer to a physical directory (directory in the file system of the operating system) using an alias in Oracle.

The directory alias can be used to change the physical directory to which alias points, without having to change the alias itself.  This makes the job of making BFILE column pointing to the right directory and right file easier, even though the files are moved from one physical directory to another.

create directory  “IMAGES” as ‘c:\bitmaps’;

The above statement creates a directory alias called IMAGES, which is an alias to the physical directory C:\BITMAPS.

Note: The directory alias is case sensitive. When using directory alias in BFILENAME function, if proper case is not given then Oracle doesn’t recognize the directory alias. But at the time of creation, Oracle converts the directory alias to uppercase if the name is not given in double quotes (“ “). So if you want to preserve the case of the directory alias then enclose it in double quotes.

Now, the alias IMAGES can be used to refer to the physical directory in BFILENAME  function as shown in the example below.

Insert into lob_table 
 values(101, ‘Some data in CLOB cloumn’,

Note: it is not possible to display the value of BFILE column using SELECT command in SQL*PLUS.

Users who have to access the directory, should be granted privilege to access the directory as follows:

grant read on directory  LOB_DIR  to  Srikanth;

The above GRANT command grants READ permission on LOB_DIR directory to user SRIKANTH.

Deleting LOBs
When a LOB value is deleted, the locator to the LOB value as well as the LOB value are deleted in case of internal LOBs (CLOB, NCLOB, and  BLOB). But in case of BFILE (external LOB) only the locator is deleted and the actual data is not deleted. This is obvious considering the fact that in case of external LOBs database stores only a pointer to the actual content but not the content itself.  So for BFILEs you need to delete data manually.

Note: The function discussed above deal with complete LOB column. They do not allow you to modify a part of the LOB column. For information regarding modifying a part of the LOB column, see DBMS_LOB package, later in this chapter.

DBMS_LOB Package
Functions and procedures in DBMS_LOB package can be used to perform various operations related to LOBs. These methods of DBMS_LOB are very important while dealing with LOBs as normal functions such as SUBSTR cannot be used with LOBs.

The following is the list of functions and procedures in DBMS_LOB package.

READ(locator,nobytes,offset, output)
Reads nobytes of a LOB value starting from offset and places the read piece in output.
WRITE(locator, nobytes, offset, buffer)
Writes  nobytes from  buffer  into a LOB starting at offset  in a LOB value.
APPEND(dest_locator, source_locator)
Appends one LOB value at the end of another LOB value.
ERASE(locator, nobytes, offset)
Erases  nobytes  from  the given offset of  a LOB value.
TRIM(locator, newlength)
Reduces the size of a LOB value by trimming characters on the right.
SUBSTR(locator, nobytes, offset)
Extracts a portion of a LOB value and returns that value. The difference between this and READ procedure is that READ procedure places the extracted value in the given variable whereas this returns the value.
This is a combination of READ and WRITE procedures. This modifies a LOB value by replacing a part of it with data from another LOB.
Returns the length of a LOB value.
Searches for the given pattern and returns the position at which the pattern is found. If offset is given search starts at the offset. And if occur parameter is also given then it looks for occur number of occurrence.  
Compares two LOB values and returns 0 if they are same, 1 if  first one is bigger than second one, and -1 if second one is bigger than first one.  offset1 and offset2  may be used to specify  at which position in first and second LOBs the search should begin. nobytes  specifies the number of bytes to be compared.
Table 2: DBMS_LOB Functions and Procedures

Examples using DBMS_LOB  Package
The following  examples  use  procedures and functions  given in the  table 2. 

INSTR function
The following example looks for the pattern  ‘Oracle’   in   CLOB_COL  column of the  row where ID is 10 in LOB_TABLE.

    c_lob       CLOB;
    pos         Number(2);
   /* get the value of  CLOB_LOB column
      from the  row  where  ID  is  10  */

   select   clob_col  into   c_lob
   from  lob_table
   where    id =  10;

  /* Use INSTR  function to find out the
     position where the pattern  ‘Oracle’ 
     occurs  in the  LOB value.
     Search start at first character and look for the
     first occurrence of the pattern

  pos :=  dbms_lob.instr(c_lob,’Oracle’,1,1);

  -- if INSTR returns 0 if means pattern is not found
  if  pos = 0 then
      dbms_output.put_line(‘Could not file pattern Oracle’);
      dbms_output.put_line(‘Found Oracle at =‘ || to_char(pos));
  end if;


COPY  procedure
The following example copies 10 characters  of one LOB to  another lob.

  source_clob    CLOB;
  dest _clob     CLOB;

   /* get  CLOB_COLs of  ID  20   */
   select   clob_col  into    source_clob
   from     lob_table
   where    id = 20;

   /* retrieve   CLOB_COL  of  id  10  for
      updation  */

   select   clob_col  into    dest_clob
   from     lob_table
   where    id = 15
   for update;  -- required to update LOB value

   /*Now copy  data with the following parameters
       No. of character to be copied  = 10
       Offset in the destination  LOB = 50
       Offset  in the source LOB      = 10  */

   dbms_lob.copy( dest_clob,source_clob,10,50,10);


1.           BFILE Related Procedures and Functions
The following are the procedures and functions in DBMS_LOB that are related to BFILE data type.

Procedure or Function
Opens the specified file.
Closes the specified file
Closes all open files.
Checks whether the file referenced by BFILE locator exists.
Gets the name of external file referenced by BFILE locator.
Checks whether external file is currently open.
Table 3: BFILE Related Procedures and Functions in DBMS_LOB Package.

The following example displays the directory and filename of a BFILE column.

    bf      bfile;
    d varchar2(20);
    f varchar2(20);
    /* get the  BFILE_COL value for the  ID  20 */
    select   bfile_col into  bf
    from     lob_table
    where    id = 20;
    /* get directory name into d and filename into f */

    dbms_output.put_line('Directory: ' || d || '  File : '|| f);

The following example checks whether the file referenced by a BFILE_COL is existing on the disk.

 bf bfile;
   /* get the  BFILE_COL value for the  ID  20 */
    select bfile_col into  bf
    from   lob_table
    where  id = 20;

   /* FILEEXISTS  returns  non-zero if  the  given  locator 
    points to an existing file on the disk */

    if dbms_lob.fileexists(bf) <> 0 then
       dbms_output.put_line(‘ BFILE for ID 20 is found ‘);
        dbms_output.put_line(‘ BFILE for ID 20 is not found‘);
    end if;


The following program is used to read the content of BFILE_COL of LOB_TABLE.

    value BFILE;
    buf char(100);
    amt BINARY_INTEGER := 100;
    pos INTEGER :=1;
    SELECT bfile_col INTO value FROM lob_table
    WHERE id = 1;
    dbms_lob.fileopen(value, dbms_lob.file_readonly);
    LOOP,amt,pos, buf);
        -- process contents of buf
        pos := pos + amt;

The above program first retrieves BFILE_COL column’s data from LOB_TABLE. Then FILEOPEN method is used to open the file using BFILE locator. Then LOOP is used to read contents from BFILE using READ method until READ method raises NO_DATA_FOUND exception. Each time a chunk of the data is read and processed.

DBMS_LOB.FILE_READONLY constant is used to specify that the file is to be opened file read-only.

Oracle8 provides a new set of datatypes called LOB data types. These data types can support up to 4GB of data and do not have restrictions that constrain LONG and LONG RAW datatypes. LOB types like BLOB, CLOB and NCLOB store  data  in the database  but BFILE type stores  data  out of database and maintains only a locator to the actually data.

Oracle8 enhanced its SQL to support LOB data types.  Oracle also provided a new package DBMS_LOB, which contains a collection of procedures and functions that manipulates LOB values.

SQL Interview Questions and Answers

There is given sql interview questions and answers that has been asked in many companies. For PL/SQL interview questions, visit our next...