creating audit tables
От | Scott Cain |
---|---|
Тема | creating audit tables |
Дата | |
Msg-id | 1097725097.1920.59.camel@localhost.localdomain обсуждение исходный текст |
Ответы |
Re: creating audit tables
|
Список | pgsql-general |
Hello, I am trying to create audit tables for all of the tables in my database. The function, table and trigger create statements are below. Apparently, I am not doing it quite right, because I get these messages when I try to run the create statements below: CREATE FUNCTION CREATE FUNCTION CREATE TABLE CREATE TABLE GRANT ERROR: function audit_update() does not exist ERROR: function audit_delete() does not exist Why do I get a message that the functions don't exist when they were just successfully created? Thanks much, Scott Here's the ddl: CREATE FUNCTION audit_update(varchar) RETURNS trigger AS ' DECLARE audit_table varchar; table_name varchar; BEGIN table_name = $1; audit_table = ''audit_'' || table_name; INSERT INTO audit_table VALUES (SELECT OLD.*,now(),''U'' FROM table_name); return NEW; END ' LANGUAGE plpgsql; CREATE FUNCTION audit_delete(varchar) RETURNS trigger AS ' DECLARE audit_table varchar; table_name varchar; BEGIN table_name = $1; audit_table = ''audit_'' || table_name; INSERT INTO audit_table VALUES (SELECT OLD.*,now(),''D'' FROM table_name); return OLD; END ' LANGUAGE plpgsql; create table tableinfo ( tableinfo_id serial not null, primary key (tableinfo_id), name varchar(30) not null, primary_key_column varchar(30) null, is_view int not null default 0, view_on_table_id int null, superclass_table_id int null, is_updateable int not null default 1, modification_date date not null default now(), constraint tableinfo_c1 unique (name) ); CREATE TABLE audit_tableinfo ( tableinfo_id integer, name varchar, primary_key_column varchar, is_view integer, view_on_table_id integer, superclass_table_id integer, is_updateable integer, modification_date date, transaction_date timestamp not null, transaction_type char not null ); GRANT ALL on audit_tableinfo to PUBLIC; CREATE TRIGGER tableinfo_audit_u BEFORE UPDATE ON tableinfo FOR EACH ROW EXECUTE PROCEDURE audit_update('tableinfo'); CREATE TRIGGER tableinfo_audit_d BEFORE DELETE ON tableinfo FOR EACH ROW EXECUTE PROCEDURE audit_delete('tableinfo'); -- ------------------------------------------------------------------------ Scott Cain, Ph. D. cain@cshl.org GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory
В списке pgsql-general по дате отправления: