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 <orat...@msn.com> wrote:
>
>
> On Aug 8, 9:58 am, Yuvaraj Sundaresan <yuvarajssundare...@gmail.com>
> 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 Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
