Re: use a variable name for an insert in a trigger for an audit

On 9dic, 2010, at 04:40 , Raimon Fernandez wrote:

> Hello,
>
> I have to audit all the changes for all rows of one database.
>
> I have a trigger that executes BEFORE any update or delete, and simply copy the row (INSERT INTO) into the replicated
table.
>
> For example, every table has the same name plus '_audit' at the end and belongs to the schema audit:
>
> table public.persons => audit.persons_audit
>
> I don't want to create specific triggers/functions for every table, so I want to modify the table_name in the INSERT
INTO,using the TG_TABLE_NAME, but I can't make it working. 
>
> Also I can't see a working solution in the archive, and some examples are quite messy to do, so maybe I have to
rethinkhow I'm doing thinks or just create a specific trigger for each table. 
>
> Here is my function, and I'm only testing now the INSERT:
>
> ...
> DECLARE
> tableRemote varchar;
> BEGIN
>
> IF TG_TABLE_NAME = 'assentaments' THEN
> tableRemote:='audit.'||TG_TABLE_NAME||'_audit';
> END IF;
>
>        --
>        -- Create a row in table_audit to reflect the operation performed on emp,
>        -- make use of the special variable TG_OP to work out the operation.
>        --
>
>         IF (TG_OP = 'DELETE') THEN
>            EXECUTE 'INSERT INTO audit.assentaments_audit SELECT CURRVAL(''audit_id_seq''),5, OLD.*';
>            RETURN OLD;
>        ELSIF (TG_OP = 'UPDATE') THEN
>            INSERT INTO tableRemote  SELECT CURRVAL('audit.audit_id_seq'),3,OLD.*;
>            RETURN OLD;
>        END IF;
>        RETURN NULL; -- result is ignored since this is an AFTER trigger
>    END;
> ...
>
> thanks,
>
> regards,
>

finally I've moved all the audit tables to a new schema called audit, and the tables being audited have now the same
nameas the 'master' tables. 

In the trigger function I want to change the default schema to audit to use the same tablename, but it seems that I
can'tchange the schema in the function. 

Also, as now the audit tables belong to the audit schema and have the same name, I'm trying to use just the
TG_TABLE_NAMEas this: 

 INSERT INTO TG_TABLE_NAME  SELECT CURRVAL('audit.audit_id_seq'),3,OLD.*;

but also isn't allowed ...

I have to specify always a fixed value for the INSERT INTO myTable to work ?

If I use:

 INSERT INTO assentaments  SELECT CURRVAL('audit.audit_id_seq'),3,OLD.*;

this works perfectly, as the trigger function belongs to the audit schema, I can use the same table name, but I can't
usethe TG_TABLE_NAME, and I have only two options: 

- use the same triggger function with IF ELSEIF to test wich table invoked the trigger function
- or just write a different trigger function for each table.

what are the best options ?

thanks for your guide!

regards,

r.



also I'm trying to change the default schema