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

[Josué Maldonado]

Hi Tom,

Tom Lane wrote:
=?ISO-8859-1?Q?Josu=E9_Maldonado?= <[EMAIL PROTECTED]> 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,



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend