我们有一个应用程序,它会根据用户请求从表中删除一行。我无法更改应用程序代码。但是,我希望根据正在删除的行的信息,从几个其他表中获取信息并将一行插入另一个表中(类似于日志记录)。
如何在PostgreSQL中实现这一目标?
我们有一个应用程序,它会根据用户请求从表中删除一行。我无法更改应用程序代码。但是,我希望根据正在删除的行的信息,从几个其他表中获取信息并将一行插入另一个表中(类似于日志记录)。
如何在PostgreSQL中实现这一目标?
编写一个触发器函数。类似这样:
CREATE OR REPLACE FUNCTION trg_backup_row()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO other_tbl
SELECT (OLD).*, t.other_col -- all columns of from old table
-- SELECT OLD.col1, OLD.col2, t.other_col -- alternative: some cols from old tbl
FROM third_tbl t
WHERE t.col = OLD.col -- link to third table with info from deleted row
AND <unique_condition_to_avoid_multiple_rows_if_needed>;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
还有一个触发器 ON DELETE
。像这样:
CREATE TRIGGER delaft
AFTER DELETE
ON tbl
FOR EACH ROW
EXECUTE PROCEDURE trg_backup_row();
Best make it a trigger AFTER DELETE
and FOR EACH ROW
.
To return all columns from the old table use the syntax (OLD).*
. See the manual about accessing composite types. Alternatively OLD.*
is valid syntax, too, because OLD
is added to the FROM
clause implicitly. For a VALUES
expression it would have to be (OLD).*
, though. Like:
INSERT INTO other_tbl
VALUES((OLD).*, some_variable)
You can include values from any other table like I demonstrate. Just make sure to get a single row, or you create multiple entries.
As the trigger fires AFTER
the event, the function can RETURN NULL
.
针对@couling的留意评论。
虽然外键可以被声明为DEFERRED
, 但这只会推迟完整性检查,而不是删除本身。在此AFTER DELETE
触发器被调用时,在其之前执行的触发器或者由ON DELETE CASCADE
外键引起的被删除的行将不再可见。(显然,这一切都在一个事务中进行。这些细节对于其他事务来说并不重要,它们将看到所有或没有影响。请参考手册了解更多关于MVCC模型和事务隔离的内容。)
因此,如果您想在INSERT
中包含依赖于这种方式的行的值,请确保在这些行被删除之前调用这个触发器。您可能需要将此触发器设置为BEFORE DELETE
。
或者这也可能意味着您需要按顺序订购触发器,BEFORE
触发器显然要先于AFTER
触发器执行。而在同一级别的触发器将按照字母顺序执行。
然而,只要我在这里非常准确,我还可以补充一点,即在其他BEFORE
触发器中对行(或相关行)所做的更改仅在此之前调用这些触发器时才可见。
我的建议是将其作为AFTER
触发器,因为如果其他触发器可能会在操作进行到一半时取消(回滚)DELETE
,则它更不容易出现问题,并且更加经济实惠 - 只要没有上述情况。
delete
之后使用了on delete cascade
引用,那么将作为其一部分被删除的子元素是否仍然可用于日志记录目的? - Philip Couling您可能需要使用我编写的函数来保留历史数据。简短描述:
历史数据存储在名为audit的单独模式中。因此,第一步是创建此模式:
CREATE SCHEMA audit;
SELECT * FROM audit.users WHERE user_id = 5;
因此,相同的查询可以在两个模式中使用,但要检索历史数据,必须在表名前加上“audit.”。
如果您希望为数据库中的所有表自动创建删除触发器,可以执行以下查询:
SELECT * FROM audit_gen_triggers();
CREATE OR REPLACE FUNCTION audit_delete()
RETURNS trigger AS
$BODY$DECLARE
t_name text;
query_op text;
primary_keys text;
c record;
key_arr text;
keys_arr text;
p_r text;
BEGIN
t_name := 'audit.' || TG_TABLE_NAME;
IF NOT EXISTS(SELECT 1 FROM pg_tables WHERE schemaname = 'audit' AND
tablename = TG_TABLE_NAME) THEN
EXECUTE 'SELECT _audit_table_creator(table_name := ($1)::name)'
USING TG_TABLE_NAME;
END IF;
FOR c IN SELECT pg_attribute.attname
FROM pg_index, pg_class, pg_attribute
WHERE
pg_class.oid = TG_TABLE_NAME::regclass AND
indrelid = pg_class.oid AND
pg_attribute.attrelid = pg_class.oid AND
pg_attribute.attnum = ANY(pg_index.indkey) AND
indisprimary LOOP
key_arr := c.attname || ', ($1).' || c.attname;
keys_arr := concat_ws(',', keys_arr, key_arr);
END LOOP;
keys_arr := '{' || keys_arr || '}';
EXECUTE 'SELECT _where_clause_creator(VARIADIC ($1)::text[])'
INTO p_r USING keys_arr;
-- raise notice 'tablica where: %', p_r;
-- zapisz do tabeli audytowanej wszystkie usuniete wartosci
query_op := 'INSERT INTO '|| t_name ||
' SELECT NEXTVAL(''serial_audit_'
|| TG_TABLE_NAME ||'''::regclass),
CURRENT_USER, ''' || TG_OP || ''',
NULL,
NOW(),
($1).*
FROM ' || TG_TABLE_NAME ||
' WHERE ' || p_r;
EXECUTE query_op USING OLD;
RETURN OLD;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
触发器:
CREATE TRIGGER table_name_delete_audit
BEFORE DELETE
ON table_name
FOR EACH ROW
EXECUTE PROCEDURE audit_delete();
其他使用的功能:
CREATE OR REPLACE FUNCTION _array_position(anyarray, anyelement)
RETURNS integer AS
$BODY$
SELECT i
FROM (SELECT generate_subscripts($1, 1) as i, unnest($1) as v) s
WHERE v = $2
UNION ALL
SELECT 0
LIMIT 1;
$BODY$
LANGUAGE sql STABLE
COST 100;
CREATE OR REPLACE FUNCTION _audit_table_creator(table_name name)
RETURNS void AS
$BODY$
DECLARE
query_create text;
BEGIN
query_create := 'DROP TABLE IF EXISTS temp_insert;
DROP TABLE IF EXISTS temp_insert_prepared';
EXECUTE query_create;
query_create := 'DROP SEQUENCE IF EXISTS serial_audit_' || table_name;
EXECUTE query_create;
query_create := 'CREATE SEQUENCE serial_audit_' || table_name || ' START 1;
ALTER TABLE serial_audit_' || table_name ||
' OWNER TO audit_owner;';
EXECUTE query_create;
query_create := 'CREATE TEMPORARY TABLE temp_insert_prepared ( '
|| table_name || '_audit_id bigint DEFAULT
nextval(''serial_audit_' || table_name || '''::regclass),
who_altered text DEFAULT CURRENT_USER,
alter_type varchar(6) DEFAULT ''INSERT'',
changed_columns text,
shift_time timestamp(0) without time zone DEFAULT NOW(),
PRIMARY KEY(' || table_name || '_audit_id )) ON COMMIT DROP';
EXECUTE query_create;
query_create := 'CREATE TEMPORARY TABLE temp_insert ON COMMIT DROP AS TABLE
' || table_name;
EXECUTE query_create;
query_create := 'CREATE TABLE audit.' || table_name ||
' AS SELECT a.*, b.* FROM temp_insert_prepared a, temp_insert b
WITH NO DATA';
EXECUTE query_create;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION _where_clause_creator(VARIADIC keys_given text[])
RETURNS text AS
$BODY$
DECLARE
x text;
where_clause text;
BEGIN
FOREACH x IN ARRAY keys_given LOOP
IF ((SELECT _array_position(keys_given, x))%2) <> 0 THEN
where_clause := concat_ws(' AND ', where_clause, x);
ELSE
where_clause := concat_ws(' = ', where_clause, x);
END IF;
END LOOP;
RETURN where_clause;
END;
$BODY$
LANGUAGE plpgsql STABLE
COST 100;
CREATE OR REPLACE FUNCTION audit_gen_triggers()
RETURNS void AS
$BODY$
DECLARE
r record;
query_create text;
BEGIN
FOR r IN SELECT table_name
FROM information_schema.tables
WHERE table_schema = current_schema AND
table_type = 'BASE TABLE' LOOP
query_create := 'DROP TRIGGER IF EXISTS ' || r.table_name || '_delete_audit ON '
|| r.table_name || ' CASCADE;
CREATE TRIGGER ' || r.table_name || '_delete_audit
BEFORE DELETE
ON ' || r.table_name || '
FOR EACH ROW
EXECUTE PROCEDURE audit_delete();';
EXECUTE query_create;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;