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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general