GeekInterview.com
  I am new, Sign me up!
 
GeekInterview.com  >  Interview Questions  >  Oracle  >  PL/SQL
Go To First  |  Previous Question  |  Next Question 
 PL/SQL  |  Question 173 of 241    Print  
Why we use instead of trigger. what is the basic structure of the instead of trigger. Explain specific business reason of it's use

  
Total Answers and Comments: 1 Last Update: April 09, 2007     Asked by: globalanil18 
  
 Sponsored Links

 
 Best Rated Answer
—

No best answer available. Please pick the good answer available or submit your answer.
April 09, 2007 04:44:01   #1  
hindol_saha Member Since: March 2007   Contribution: 15    
RE: Why we use instead of trigger. what is the basic s...
Conceptually INSTEAD OF triggers are very simple. You write code that the Oracle server will execute when a program performs a DML operation on the view. Unlike a conventional BEFORE or AFTER trigger an INSTEAD OF trigger takes the place of rather than supplements Oracle's usual DML behavior. (And in case you're wondering you cannot use BEFORE/AFTER triggers on any type of view even if you have defined an INSTEAD OF trigger on the view.)
CREATE OR REPLACE TRIGGER images_v_insert
INSTEAD OF INSERT ON images_v
FOR EACH ROW
BEGIN
/* This will fail with DUP_VAL_ON_INDEX if the images table
|| already contains a record with the new image_id.
*/
INSERT INTO images
VALUES (:NEW.image_id :NEW.file_name :NEW.file_type
:NEW.bytes);

IF :NEW.keywords IS NOT NULL THEN
DECLARE
/* Note: apparent bug prevents use of :NEW.keywords.LAST.
|| The workaround is to store :NEW.keywords as a local
|| variable (in this case keywords_holder.)
*/
keywords_holder Keyword_tab_t : :NEW.keywords;
BEGIN
FOR the_keyword IN 1..keywords_holder.LAST
LOOP
INSERT INTO keywords
VALUES (:NEW.image_id keywords_holder(the_keyword));
END LOOP;
END;
END IF;
END;

Once we've created this INSTEAD OF trigger we can insert a record into this object view (and hence into both underlying tables) quite easily using: 

INSERT INTO images_v VALUES (Image_t(41265 'pigpic.jpg' 'JPG' 824   Keyword_tab_t('PIG' 'BOVINE' 'FARM ANIMAL')));

This statement causes the INSTEAD OF trigger to fire and as long as the primary key value (image_id 41265) does not already exist the trigger will insert the data into the appropriate tables.

Similarly we can write additional triggers that handle updates and deletes. These triggers use the predictable clauses INSTEAD OF UPDATE and INSTEAD OF DELETE.


 
Is this answer useful? Yes | No

 Related Questions

 PL/SQL  uses  block  structure  as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL. 
Latest Answer : PL/SQL is a block-structured language, meaning that programs can be divided into logical blocks. A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional). ...
 There are two types of cursors, Implicit  Cursor and Explicit Cursor.PL/SQL uses Implicit Cursors for queries.User  defined cursors are called Explicit Cursors. They can be declared and 
Latest Answer : Cursors are of two types1. Implicit Cursors: -  Whenever we execute sql statements oracle server assigns a work area called private sql area to store precessed infomation. The most recently used work are can be accessed using SQL%. In implicit cursors ...
 WHERE  CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a cursor. Database Triggers 
Latest Answer : When referencing the current row from an explicit cursor, use the WHERE CURRENT OF clause. This allows you to apply updates and deletes to the row currently being addressed, without the need to explicitly reference the ROWID. You must include the FOR ...
 Database  trigger  is stored PL/SQL program unit associated with a specific database   table.   Usages   are  Audit  data  modifications,  
Latest Answer : A database trigger is a named pl/sql block associated with a table and fires automatically when an event occurs or something happens. Data auditing , Implementing complex business rules, security are main uses of database triggers. ...
                Insert         Update          
Latest Answer : Triggers can be classified based on the below factors -(1) Trigger Event:INSERTUPDATEDELETE(2) Trigger Type:STATEMENT LevelROW Level(3) Trigger Timings:BEFOREAFTERINSTEAD OF ...
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
 It  is  not  possible.  As  triggers are defined for each table, if you use COMMIT   of    ROLLBACK  in  a  trigger,  it  
 The table columns are referred as OLD.column_name and NEW.column_name.For triggers related to INSERT only NEW.column_name values only available.For  triggers related to UPDATE only OLD.column_name 
Latest Answer : OLD and NEW are two virtual tables available during database trigger execution.UPDATE statement has access to  both old and new values.INSERT  statement has access only to new values. Old values are NULL for insert statement.DELETE ...
What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
 Mutation of table occurs. 
 The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.      e.g. PRAGMA EXCEPTION_INIT 
Latest Answer : PRAGMA EXCEPTION_INIT statement associate the declared exception with the standard Oracle server error number. PRAGMA EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That allows you to refer to any internal ...
 Raise_application_error  is  a  procedure  of  package  DBMS_STANDARD which allows  to  issue an user_defined error messages from stored sub-program or database 
Latest Answer : You can use this procedure to issue user-defined error messages from stored subprograms.You can report errors to your application and avoid returning unhandled exceptions.Syntax: raise_application_error (error_number,message[, {TRUE | FALSE}]); ...

 Sponsored Links

 
Related Articles

Service Oriented Java Business Integration Review

Service Oriented Java Business Integration Review Introduction If you ve read through the texts which give you an introduction to SOA or Web Services you will often find them to be quite frustrating and the reason for this is because they spend too much time referencing business processes which are
 

Business Intelligence Key Performance Indicators

Business Intelligence Key Performance Indicators What are Key Performance Indicators Key Performance Indicators are also known as Key Success Indicators they help an organization to better define and measure their progress toward professional goals Once an organization has clearly identified its nee
 

How to Best Use Business Intelligence to Your Advantage

How to Best Use Business Intelligence to Your Advantage It is no big secret that today s businesses rely heavily on data and the information it delivers about their companies Whether it is for the purpose of customer acquisition improving operational performance or understanding competitors all of t
 

Business Intelligence in Information Technology

Business Intelligence in Information Technology IT stands for Information Technology Information Technology is a system designed to gather process or distribute information IT can be inspected in terms of the following three groupings; the data gathering the data processing and the data distribut
 

Querying Data with Oracle XQuery

Querying Data with Oracle XQuery Starting with Oracle Database 10g Release 2 you can take advantage of a full featured native XQuery engine integrated with the database With Oracle XQuery you can accomplish various tasks involved in developing PHP Oracle XML applications operating on any kind of dat
 

Using Oracle XML DB Repository

Using Oracle XML DB Repository Another variation on accessing and manipulating XML content stored in Oracle database is provided by Oracle XML DB repository which is an essential component of Oracle XML DB mosgoogle NOTE Oracle XML DB repository also known as XML repository is a hierarchically organ
 

Business Intelligence Integration

Business Intelligence Integration How to Integrate Business Intelligence within Your Organization Before you Integrate Business Intelligence into your Organization Business Intelligence has developed into strategic process over the years of its extraordinary growth even so it still needs to be incor
 

Business Intelligence Tips

Business Intelligence Tips Understand Your Organization first Truly understanding your company and how it works is perhaps one of the most important Business Intelligence tips anyone can offer This includes understanding the business itself the processes involved the data and how that data is used b
 

Using Oracle Database for Storing, Modifying, and Retrieving XML Data

Using Oracle Database for Storing Modifying and Retrieving XML Data With Oracle XML DB you have various XML storage and XML processing options allowing you to achieve the required level of performance and scalability One of the most interesting things about Oracle XML DB is that it allows you to per
 

Business Performance Management

Business Performance Management What is Business Performance Management Business Performance Management is most commonly described as a set of processes that help companies or organizations optimize their business performance It is specifically designed to organize automate and analyze business meth
 

About Us -  Privacy Policy -  Terms and Conditions -  Contact -  Ask Question -  Propose Category -  Site Updates 

Copyright © 2005 - 2009 GeekInterview.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape