Re: Comparing :OLD and :NEW-Record in a Trigger

Originally posted by Thomas Stuefer
> I want compare a record in a Trigger with :OLD and :NEW and audit the
> changes in another table. But I don't want hardcode each field
> separate ...
>
>
> The following Trigger uses EXECUTE IMMEDIATE for this problem and
> compiles
> successfully, but when firing it hangs and doesn't display any
> error or
> message.
>
>
> Thank you for your help !
>
>
>
>
>
> CREATE OR REPLACE TRIGGER tr_manipos_ko
>
> BEFORE UPDATE ON manipos
>
> FOR EACH ROW
>
>
>
> DECLARE
>
> sVariableOld VARCHAR2(2000);
>
> sVariableNew VARCHAR2(2000);
>
>
>
> sSqlString VARCHAR2(250);
>
>
>
> BEGIN
>
> FOR recTabColumns IN (SELECT column_name
>
> FROM user_tab_columns
>
> WHERE table_name = 'MANIPOS') LOOP
>
>
>
> sSqlString := 'SELECT :OLD.' || recTabColumns.column_name || ',
> ' ||
>
> ':NEW.' || recTabColumns.column_name || '
> FROM
> dual';
>
> EXECUTE IMMEDIATE sSqlString INTO sVariableOld, sVariableNew;
>
>
>
> IF sVariableOld sVariableNew THEN
>
> -- Do something like audit the changes in a table
>
> NULL;
>
> END IF;
>
> END LOOP;
>
>
>
> END;
>
> /

That's not going to work. The dynamic SQL has no idea what :OLD and :NEW refer to. In any case, this would be horribly inefficient.

Your best bet here is to hard code each field, which you don't want to do. But you CAN use dynamic SQL to generate the trigger code so that you don't have to type in all the comparisons by hand.

--
Posted via http://dbforums.com