use a variable name for an insert in a trigger for an audit
От | Raimon Fernandez |
---|---|
Тема | use a variable name for an insert in a trigger for an audit |
Дата | |
Msg-id | C96F6F8C-3D51-4733-BE51-241928DC77BB@montx.com обсуждение исходный текст |
Ответ на | Re: SELECT is immediate but the UPDATE takes forever (Raimon Fernandez <coder@montx.com>) |
Ответы |
Re: use a variable name for an insert in a trigger for an audit
|
Список | pgsql-general |
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 howI'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,
В списке pgsql-general по дате отправления: