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;
--
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
