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

On Thursday, December 9, 2010, Raimon Fernandez <coder(at)montx(dot)com> wrote:
>
> 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 rethink how 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 name as 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't change 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_NAME as 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 use the 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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-gener>

Use 'execute' passing record through 'using' as text, recasting and
expanding record in query.

merlin