Copying data with triggers

Поиск
Список
Период
Сортировка
От Keith Wong
Тема Copying data with triggers
Дата
Msg-id 4.3.2.7.0.20000822003607.00b0d6b0@mail.e-magine.com.au
обсуждение исходный текст
Ответ на RE: Speed or configuration  (The Hermit Hacker <scrappy@hub.org>)
Ответы Re: Copying data with triggers
Список pgsql-sql
Hi all,

In the database I'm designing, I want to have audit tables that keep a log 
of all inserts, updates and deletes that occur
on any table.

e.g. If i had a table Info,
create table Info ( info_id SERIAL,                          some_data text
)

I would also have a corresponding audit table

create table AudInfo (aud_key_id SERIAL,                               info_id int4,
some_datatext,                               aud_operation_type,                               aud_log_time timestamp
defaultnow()
 
)

now I tried creating a trigger on Info, so that whenever an insert occurs, 
the records are copied to the audit table.

create function tp_info () returns opaque as 'begin    -- insert into audit table    insert into AudInfo (info_id,
some_data,aud_operation_type) values 
 
(new.info_id, new.some_data, ''i'');    return new;end;
' language 'plpgsql';

create trigger tp_info before insert on Infofor each row execute procedure tp_info();

This doesn't work however. A parse error occurs in the first line.
I suspect that I cannot insert a record into another table in a trigger. 
I'm not sure why though.

Anybody else done similar operations within a trigger procedure? Or know of 
a work around?

Cheers,
Keith.



В списке pgsql-sql по дате отправления:

Предыдущее
От: Jan Wieck
Дата:
Сообщение: Re: 8K Limit, whats the best strategy?
Следующее
От: "jason watkins"
Дата:
Сообщение: copy DELETES to audit table