From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Raimon Fernandez <coder(at)montx(dot)com> |
Cc: | pgsql-general List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: use a variable name for an insert in a trigger for an audit |
Date: | 2010-12-09 23:12:32 |
Message-ID: | AANLkTi=16ZA4a7-1fHFVUZ+JyZgKoRG1L4J_Z+oLwsiN@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-interfaces |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2010-12-10 01:13:14 | Re: Tuning Postgres for single user manipulating large amounts of data |
Previous Message | Dmitriy Igrishin | 2010-12-09 22:10:10 | Fwd: Extended query protocol and exact types matches. |
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Figueiredo Jr. | 2011-03-25 02:39:19 | Npgsql 2.0.12 beta1 released! |
Previous Message | Raimon Fernandez | 2010-12-09 16:26:27 | Re: use a variable name for an insert in a trigger for an audit |