Home » SQL & PL/SQL » SQL & PL/SQL » INSTEAD OF UPDATE TRIGGER DOUBT (ORACLE 10g, Window Xp)
INSTEAD OF UPDATE TRIGGER DOUBT [message #335885] Thu, 24 July 2008 01:37 Go to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Hi All,

I have a doubt regarding INSTEAD OF UPDATE TRIGGER. Let me explain my doubt with example.

I Have a table as below.
CREATE TABLE MYTABLE
   (	"ID" NUMBER NOT NULL ENABLE, 
	"FNAME" VARCHAR2(200), 
	"LNAME" VARCHAR2(200), 
	"ADDRS" VARCHAR2(4000), 
	 CONSTRAINT "MYTABLE_PK" PRIMARY KEY ("ID"))


On this table I have a view as
CREATE OR REPLACE FORCE VIEW  "MYVIEW" ("ID", "FNAME", "LNAME", "ADDRS") AS 
  SELECT  "ID","FNAME","LNAME","ADDRS" 
 FROM MYTABLE;

ON this view I have created a instead of update trigger as
CREATE OR REPLACE TRIGGER trg_iou instead OF

UPDATE ON myview FOR EACH ROW
DECLARE
-- Declare general purpose variables

v_code NUMBER;
v_errm VARCHAR2(64);
v_id NUMBER(18,   0) := :NEW.ID;
v_fnameuf NUMBER(1,  0) := 0;
v_fname VARCHAR2(200) := :NEW.FNAME;
v_lnameuf NUMBER(1,   0) := 0;
v_lname VARCHAR2(200) := :NEW.LNAME;
v_addrs VARCHAR2(20):= :NEW.ADDRS;

BEGIN

     
      UPDATE mytable  SET id = v_id,    fname = v_fname,
          lname = v_lname, ADDRS = v_addrs
        WHERE id = v_id;

    
  EXCEPTION
  WHEN others THEN
    v_code := SQLCODE;
    v_errm := SUBSTR(sqlerrm,   1,   128);
    DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm);
    raise_application_error(-20002,   v_errm);

  END;


Now I am updating view bu execution below commands
Update myview set fname='DDDD' where id =1;


Now my doubt is that while executing above update statement my update trigger will fire a update query on MYTABLE using all the column name.BUT in actual update statement I am using only 'FNAME'.
I don't want to go with three update statement for each column inside my trigger(Using IF UPDATING('FNAME') THEN ...).I just want to fire a single update statement in trigger body.

I have this doubt b'coz this approach(Using All columns in update statement) will create problem if my base table already have statemnet level triggers(i.e. Suppose MYTABLE has three diffrent trigger for each column's update event.).


Please tell me how can I handle the same. This is a example please don't go that trigger is not required here etc.

Thanks In advance.

Bhadu
Re: INSTEAD OF UPDATE TRIGGER DOUBT [message #335909 is a reply to message #335885] Thu, 24 July 2008 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't want to go with three update statement for each column inside my trigger(Using IF UPDATING('FNAME') THEN ...)

Why?

Regards
Michel
Re: INSTEAD OF UPDATE TRIGGER DOUBT [message #335940 is a reply to message #335885] Thu, 24 July 2008 03:45 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
@Michel

My base table 'MyTable' has update trigger on it.If i'll go for three updates with IF condition then update trigger on base table'll be fired more then once.

update myview set fname='jjj' , lanme='jjjj' where id =1;

For above statement update trigger on base table will be fired two times that is not required.
Re: INSTEAD OF UPDATE TRIGGER DOUBT [message #335945 is a reply to message #335940] Thu, 24 July 2008 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For instance, build a string for your final update (using bind variables).

And remove your "when others" part it is a bug.

Regards
Michel
Re: INSTEAD OF UPDATE TRIGGER DOUBT [message #335959 is a reply to message #335885] Thu, 24 July 2008 04:53 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
You are saying that I should go with dynamic sql.
But again there is a big prob with dynamic sql b'coz in dynamic sql I have to pass bind variables.
EXECUTE IMMEDIATE <dynamic sql> using v_fname,f_lastname...etc


Here I am not sure how much bind variable i have to pass.

when I am updating two column then i have to pass 2 variables. when I am using update with one column then one variable. But in Execute Immediate statement i cannot change number of bind variables according to update condition.

Again if I'll go with simple update string i.e.

V_sql varchar2(4000) := 'update mytable set ;
IF UPDATING('FNAME') THEN
v_sql := v_sql || ' FNAME ='''||v_fname||''';
END IF;
... so on.


Then problem will come in picture during data type conversation.
B'coz in real scenario my table have clob, timestamp,date etc field.

This is an example in real scenario my table has 35 columns with clob,date, timestamp,number,varchar2,char etc datatypes.
Re: INSTEAD OF UPDATE TRIGGER DOUBT [message #336869 is a reply to message #335959] Tue, 29 July 2008 04:36 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Hi All,

@Michel Thanks for your help.
My problem has been solved by using 'DBMS_SQL'.


Thanx for your help. Cool
Re: INSTEAD OF UPDATE TRIGGER DOUBT [message #336870 is a reply to message #335885] Tue, 29 July 2008 04:38 Go to previous messageGo to next message
rajesh_bhadu
Messages: 48
Registered: June 2007
Member
Here is trigger ..as per my requirement please tell me your suggestions are most welcome.

create or replace
TRIGGER TRG_IOU_MYVIEW
 INSTEAD OF UPDATE ON MYVIEW 
 for each row
 declare 
 v_sql varchar2(3000);
  cursor_name INTEGER;
   rows_processed INTEGER;
   v_count int := 0;
BEGIN
 cursor_name := dbms_sql.open_cursor;
  v_sql := 'update mytable set ';
  IF UPDATING('FNAME') THEN
  BEGIN
 
   IF(v_count >0) THEN
    v_sql := v_sql || ',';
   END IF;
    v_sql := v_sql || 'FNAME = :f';
    v_count := v_count+1;
  
  END;
  END IF;
   IF UPDATING('LNAME') THEN
  BEGIN
 
   IF(v_count >0) THEN
    v_sql := v_sql || ',';
   END IF;
    v_sql := v_sql || 'LNAME = :l';
    v_count := v_count+1;
  
  END;
  END IF;
   IF UPDATING('ADDRS') THEN
  BEGIN
  
   IF(v_count >0) THEN
    v_sql := v_sql || ',';
   END IF;
   v_sql := v_sql || 'ADDRS = :a';
    v_count := v_count+1;
  
  END;
  END IF;
  v_sql := v_sql || ' where id =:i';
   DBMS_SQL.PARSE(cursor_name, v_sql,
                   dbms_sql.native);
   IF UPDATING('FNAME') THEN
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':f', :new.fname);
   END IF;
   IF UPDATING('LNAME') THEN
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':l', :new.lname);
   END IF;
    IF UPDATING('ADDRS') THEN
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':a', :new.ADDRS);
   END IF;
  DBMS_SQL.BIND_VARIABLE(cursor_name, ':i', :new.ID);
  
    rows_processed := dbms_sql.execute(cursor_name);
    DBMS_SQL.close_cursor(cursor_name);
END;

Re: INSTEAD OF UPDATE TRIGGER DOUBT [message #337074 is a reply to message #335885] Tue, 29 July 2008 20:39 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
are you maybe looking for somethng like this;

declare
   col1_updated_flag_v varchar2(1);
   col2_updated_flag_v varchar2(1);
   col3_updated_flag_v varchar2(1);
begin
   if updating('col1') then col1_updated_flag_v := 'Y'; end if;
   if updating('col2') then col2_updated_flag_v := 'Y'; emd if;
   if updatimg('col3') then col3_updated_flag_v := 'Y'; end if;

   update sometable set
          col1 = case when col1_updated_flag_v := 'Y' then :new.col1 else :old.col1 end
        , col2 = case when col2_updated_flag_v := 'Y' then :new.col2 else :old.col2 end
        , col2 = case when col3_updated_flag_v := 'Y' then :new.col3 else :old.col3 end
   where pk = :old.pk
   ;
end;
/

Kevin
Previous Topic: Cursor cache in nested queries ?
Next Topic: Aggregate Window Functions Causes Full Table Scan
Goto Forum:
  


Current Time: Fri Dec 09 07:27:12 CST 2016

Total time taken to generate the page: 0.13982 seconds