Re: TCL trigger doesn't work after deleting a column

Hi Tom,

Tom Lane wrote:
> =?ISO-8859-1?Q?Josu=E9_Maldonado?= <josue@lamundial.hn> writes:
>
>>Noticed that the loop does not go through all fields:
>
>
> Hard to believe.  Could you give us a complete example, not a partial
> one?

This is the code in the trigger function:

-- Function: public.audit_log()

-- DROP FUNCTION public.audit_log();

CREATE OR REPLACE FUNCTION public.audit_log()
   RETURNS trigger AS
'
  elog NOTICE "Inicio: "
if {[string match $TG_op UPDATE]} {
   foreach id [array names OLD] {
    #if { $OLD($id) != $NEW($id) } {
       elog NOTICE "ID tiene $id)"
       elog NOTICE "OLD tiene $OLD($id)"
       elog NOTICE "NEW tiene $NEW($id)"
       # tcl says $NEW(duser) does not exist
       # elog NOTICE "USER tiene $NEW(duser)"
       set lcsql "insert into audit (accion, campo, oldval, newval,
tabla, usuario ) "
       #append lcsql "values
(\'UPD\',\'$id\',\'$OLD($id)\'::text,\'$NEW($id)\'::text,\'$1\',\'$NEW(duser)\')"

    #spi_exec "$lcsql"
    #}
    }
}

if {[string match $TG_op INSERT]} {
   foreach id [array names NEW] {
     if { [info exists NEW($id)] } {
            set lcsql "insert into audit (accion, campo, newval, tabla,
usuario ) "
    append lcsql "values
(\'INS\',\'$id\',\'$NEW($id)\',\'$1\',\'$NEW(duser)\')"
    spi_exec "$lcsql"
     }
   }
}

if {[string match $TG_op DELETE]} {

   foreach id [array names OLD] {
     if { [info exists OLD($id)] } {
    set lcsql "insert into audit (accion, campo, oldval, tabla, usuario ) "
    append lcsql "values
(\'DEL\',\'$id\',\'$OLD($id)\',\'$1\',\'$OLD(duser)\')"
    spi_exec "$lcsql"
    return [array get OLD]
     }
   }
}
return [array get NEW]
' LANGUAGE 'pltcl' VOLATILE;


And this is the way a defined the trigger in my table

-- Trigger: tinv_auditor on public.tinv

-- DROP TRIGGER tinv_auditor ON public.tinv;

CREATE TRIGGER tinv_auditor
   AFTER INSERT OR UPDATE OR DELETE
   ON public.tinv
   FOR EACH ROW
   EXECUTE PROCEDURE public.audit_log('tinv');

Thanks,