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

Hi Tom,

Tom Lane wrote:
> =?ISO-8859-1?Q?Josu=E9_Maldonado?= <josue(at)lamundial(dot)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,