On Thursday, December 9, 2010, Raimon Fernandez <co...@montx.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@postgresql.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
