On Aug 11, 5:13 pm, Yuvaraj Sundaresan <[email protected]> wrote: > This is right but i need which column is changed only that column should > available in update statement. > > For Eg: In a employee table name, emp_id, age, sex are available columns and > when i updating name then my update statement should be > update employee > set name = :new.name > where emp_id = :new.emp_id > > > > On Tue, Aug 9, 2011 at 8:00 PM, ddf <[email protected]> wrote: > > > On Aug 8, 9:58 am, Yuvaraj Sundaresan <[email protected]> > > wrote: > > > Dear friends, > > > I need one urgent help, my requirement is one table many people will > > > update at the end of the day i need all the update script of that table. > > So > > > i used the trigger to build the update statement dynamically but its not > > > working see the below coding and help to proceed. In this coding after > > > execute immediate its not working. > > > > create or replace trigger TEST > > > after insert OR UPDATE OR DELETE on person_table REFERENCING OLD AS OLD > > > NEW AS NEW > > > for each row > > > declare > > > SQL1 VARCHAR2(4000); > > > SQL2 VARCHAR2(4000); > > > SQL3 VARCHAR2(4000); > > > SQL4 VARCHAR2(4000); > > > begin > > > IF INSERTING THEN > > > NULL; > > > ELSIF UPDATING THEN > > > SQL1 := 'UPDATE person_table '; > > > FOR I IN (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME > > = > > > 'PERSON_TABLE') LOOP > > > DBMS_OUTPUT.PUT_LINE(1); > > > EXECUTE IMMEDIATE('SELECT :OLD.'||I.COLUMN_NAME||' FROM DUAL') > > INTO > > > SQL2; > > > DBMS_OUTPUT.PUT_LINE(2); > > > EXECUTE IMMEDIATE('SELECT :NEW.'||I.COLUMN_NAME||' FROM DUAL') > > INTO > > > SQL3; > > > IF SQL2 <> SQL3 THEN > > > IF SQL4 IS NOT NULL THEN > > > SQL4 := SQL4 ||' , '||I.COLUMN_NAME||' = '||SQL3; > > > ELSE > > > SQL4 := SQL4 ||' , '||I.COLUMN_NAME||' = '||SQL3; > > > END IF; > > > END IF; > > > END LOOP; > > > DBMS_OUTPUT.PUT_LINE(3); > > > SQL1 := SQL1 || SQL4 ||' WHERE NAME = '||:NEW.NAME; > > > DBMS_OUTPUT.PUT_LINE(4||'--'||SQL1); > > > INSERT INTO MY_SQL VALUES (SQL1); > > > ELSIF DELETING THEN > > > NULL; > > > END IF; > > > end TEST; > > > What, exactly, are you trying to do with this? The error I see is > > > ERROR at line 1: > > ORA-01008: not all variables bound > > ORA-06512: at "BING.TEST", line 14 > > ORA-04088: error during execution of trigger 'BING.TEST' > > > because of the selects against DUAL you're trying to make > > dynamically. Your trigger can be reduced to: > > > SQL> create or replace trigger TEST > > 2 after insert OR UPDATE OR DELETE on person_table -- REFERENCING > > OLD AS OLD NEW AS NEW > > 3 for each row > > 4 declare > > 5 SQL1 VARCHAR2(4000); > > 6 SQL2 VARCHAR2(4000); > > 7 SQL3 VARCHAR2(4000); > > 8 SQL4 VARCHAR2(4000); > > 9 begin > > 10 IF INSERTING THEN > > 11 NULL; > > 12 ELSIF UPDATING THEN > > 13 SQL1 := 'UPDATE person_table set yarp = '||:new.yarp||', > > znip = '''||:new.znip||''', pling = '''||:new.pling||''' where name = > > '''||:new.name||''''; > > 14 DBMS_OUTPUT.PUT_LINE(1); > > 15 DBMS_OUTPUT.PUT_LINE(2); > > 16 DBMS_OUTPUT.PUT_LINE(3); > > 17 DBMS_OUTPUT.PUT_LINE('4 --'||SQL1); > > 18 INSERT INTO MY_SQL VALUES (SQL1); > > 19 ELSIF DELETING THEN > > 20 NULL; > > 21 END IF; > > 22 end TEST; > > 23 / > > > Trigger created. > > > SQL> > > SQL> show errors > > No errors. > > SQL> > > SQL> set serveroutput on size 1000000 > > SQL> > > > And it will function correctly: > > > SQL> insert into person_table > > 2 values(1, 'Bleebo','Tran', sysdate); > > > 1 row created. > > > SQL> > > SQL> update person_table > > 2 set name = 'Roopwinger' where yarp = 1; > > 1 > > 2 > > 3 > > 4 --UPDATE person_table set yarp = 1, znip = 'Tran', pling = '09- > > AUG-11' where name = 'Roopwinger' > > > 1 row updated. > > > SQL> > > SQL> select * from my_sql; > > > SQLTXT > > > ------------------------------------------------------------------------------------------------------------------------------------ > > UPDATE person_table set yarp = 1, znip = 'Tran', pling = '09-AUG-11' > > where name = 'Roopwinger' > > > SQL> > > > David Fitzjarrell > > > -- > > You received this message because you are subscribed to the Google > > Groups "Oracle PL/SQL" group. > > To post to this group, send email to [email protected] > > To unsubscribe from this group, send email to > > [email protected] > > For more options, visit this group at > >http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text - > > - Show quoted text -
The code I wrote can be modified by you to meet your needs. We are here to assist, not to do your work for you. David Fitzjarrell -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
