Home » SQL & PL/SQL » SQL & PL/SQL » "instead of triggers"
"instead of triggers" [message #112488] Fri, 25 March 2005 17:23 Go to next message
avdba_22
Messages: 5
Registered: March 2005
Junior Member
Has any body used above mentioned triggers? I read some documentation but having problems in using it. If anyone has some good examples or any kind of explanation it would be very helpful.

Appreciate all your help.

Thank you.
Re: "instead of triggers" [message #112500 is a reply to message #112488] Sat, 26 March 2005 01:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Instead of triggers are very useful when you have complex views. A complex view cannot be updated without such triggers:
SCOTT> create table t1
  2  ( id int primary key
  3  , name varchar2(10)
  4  )
  5  /

Table created.

SCOTT> create table t2
  2  ( id int primary key
  3  , t1_id references t1(id)
  4  , name varchar2(10)
  5  )
  6  /

Table created.

SCOTT> 
SCOTT> create or replace view v1
  2  ( t1_id
  3  , t1_name
  4  , t2_id
  5  , t2_name
  6  ) as (
  7  select t1.id
  8  ,      t1.name
  9  ,      t2.id
 10  ,      t2.name
 11  from   t1
 12  ,      t2
 13  where  t2.t1_id(+) = t1.id
 14  )
 15  /

View created.
SCOTT> insert all
  2  into t1 values( 1, 'John')
  3  into t1 values( 2, 'Jack')
  4  into t2 values( 1, 1, 'Diana')
  5  select * from dual
  6  /

3 rows created.

SCOTT> 
SCOTT> select *
  2  from   v1
  3  /

     T1_ID T1_NAME         T2_ID T2_NAME
---------- ---------- ---------- ----------
         1 John                1 Diana
         2 Jack

So, here we have our complex view. It is impossible to directly insert/update/delete from it:
SCOTT> insert into v1 values (3, 'Mike', 2, 'Janet')
  2  /
insert into v1 values (3, 'Mike', 2, 'Janet')
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SCOTT> 
SCOTT> update v1
  2  set    t2_name = 'Rose'
  3  where  t2_id = 1
  4  /
set    t2_name = 'Rose'
       *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SCOTT> 
SCOTT> delete v1
  2  where  t1_id = 2
  3  /
delete v1
       *
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table

We need an instead-of trigger here:
SCOTT> create trigger v1_io
  2  instead of insert or update or delete
  3  on v1
  4  for each row
  5  begin
  6    if inserting
  7    then
  8      insert into t1
  9      ( id
 10      , name
 11      ) values
 12      ( :new.t1_id
 13      , :new.t1_name
 14      );
 15      insert into t2
 16      ( id
 17      , t1_id
 18      , name
 19      ) values
 20      ( :new.t2_id
 21      , :new.t1_id
 22      , :new.t2_name
 23      );
 24    end if;
 25    if updating
 26    then
 27      update t2
 28      set    name = :new.t2_name
 29      where  id = :new.t2_id
 30      ;
 31      update t1
 32      set    name = :new.t1_name
 33      where  id = :new.t1_id
 34      ;
 35    end if;
 36    if deleting
 37    then
 38      /* Delete the row in t2 */
 39      delete t2
 40      where  id = :old.t2_id
 41      ;
 42      /* Delete t1 if no t2 is left */
 43      delete t1
 44      where  id = :old.t1_id
 45      and    not exists (select null
 46                         from   t2
 47                         where  t1_id = :old.t1_id
 48                        )
 49      ;
 50    end if;
 51  end;
 52  /

Trigger created.

This trigger tells oracle to execute the appropriate code in case of an insert, update or delete on the view.
The actual DML on the view is not executed (hence the name: INSTEAD OF trigger)
SCOTT> insert into v1 values (3, 'Mike', 2, 'Janet')
  2  /

1 row created.

SCOTT> 
SCOTT> update v1
  2  set    t2_name = 'Rose'
  3  where  t2_id = 1
  4  /

1 row updated.

SCOTT> 
SCOTT> delete v1
  2  where  t1_id = 2
  3  /

1 row deleted.

SCOTT> 
SCOTT> select *
  2  from   v1
  3  /

     T1_ID T1_NAME         T2_ID T2_NAME
---------- ---------- ---------- ----------
         1 John                1 Rose
         3 Mike                2 Janet


hth
Re: "instead of triggers" [message #349107 is a reply to message #112488] Thu, 18 September 2008 22:30 Go to previous messageGo to next message
renu4dwh
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
Just the best example on instead of trigger...Thank u so much
Re: "instead of triggers" [message #349113 is a reply to message #112500] Thu, 18 September 2008 23:32 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
thank you frank ,this is the best explaination i have come across regarding instead of triggers.

Regards,
Navneet
Re: "instead of triggers" [message #349128 is a reply to message #112488] Fri, 19 September 2008 00:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Frank ..

You are rocking Cool Cool Cool

Thumbs Up
Rajuvan
Re: "instead of triggers" [message #349136 is a reply to message #112500] Fri, 19 September 2008 01:20 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

Frank,

Great!! Very nicely and simply explained.

Regards
Harshad
Re: "instead of triggers" [message #380733 is a reply to message #112500] Tue, 13 January 2009 03:04 Go to previous messageGo to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Hi frank,
thanks for a very simple example but
if i write:
insert into v1 values (7, 'Mike1', 8, 'Janet1')

Query1: then will it insert row in view or table?
Query2: will it insert it in base table and view both?


i have tried this example its inserting in both base table and view also.
but if i have created an instead of trigger on tabe for insert then i think it will not insert value in view, but it will insert same value in base table is it correct?

because i have read the same sentence in a material is as follows:
***************************************************************************
CREATE OR REPLACE TRIGGER PERSON_VIEW_INSERT
INSTEAD OF INSERT ON PERSON_VIEW
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(’INSERTING: ’ || :NEW.NAME);
-- we can also do
-- INSERT INTO PERSON(ID,NAME,DOB) VALUES (N,:NEW.NAME,SYSDATE);
END;


When we do an insert statement on PERSON_VIEW:
INSERT INTO PERSON_VIEW(NAME) VALUES (’SUPERMAN’);
Which produces the result:
INSERTING: SUPERMAN

So, what did just happen??? Did we insert a value into a view? No, not really. What we did was fire a trigger when someone tried to insert a value into a VIEW.
Now, as the comment in the code indicates, we can actually simulate the insertion statement (by inserting the value into the PERSON table ourselves).

************************************************************************
Re: "instead of triggers" [message #380737 is a reply to message #380733] Tue, 13 January 2009 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: "instead of triggers" [message #380743 is a reply to message #380733] Tue, 13 January 2009 03:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
modhiyakomal
i think it will not insert value in view, but it will insert same value in base table

View does NOT contain data; think of it as of a stored SELECT statement. When "inserting into a view", data actually ends up in a table, not in a view, but can be selected from both table and view (unless view's WHERE clause restricts it).
Re: "instead of triggers" [message #380861 is a reply to message #380743] Tue, 13 January 2009 10:55 Go to previous message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Thanks
Previous Topic: understanding types
Next Topic: Adding additional columns to query
Goto Forum:
  


Current Time: Sun Dec 11 04:10:43 CST 2016

Total time taken to generate the page: 0.05835 seconds