postgresql触发器使用实例
发布时间:2020-12-13 17:28:29 所属栏目:百科 来源:网络整理
导读:创建测试表: CREATE TABLE emp ( empname text NOT NULL, salary integer ); CREATE TABLE emp_audit( operation text NOT NULL, stamp timestamp NOT NULL, userid text NOT NULL, empname text NOT NULL, salary integer ); 创建存储过程: CREATE OR REPL
创建测试表:
CREATE TABLE emp ( empname text NOT NULL, salary integer ); CREATE TABLE emp_audit( operation text NOT NULL, stamp timestamp NOT NULL, userid text NOT NULL, empname text NOT NULL, salary integer ); 创建存储过程: CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO emp_audit SELECT 'DELETE',now(),user,OLD.*; ELSIF (TG_OP = 'UPDATE') THEN -- save old and new values INSERT INTO emp_audit SELECT 'OLD',OLD.*; INSERT INTO emp_audit SELECT 'NEW',NEW.*; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO emp_audit SELECT 'INSERT',NEW.*; ELSEIF (TG_OP = 'TRUNCATE') THEN INSERT INTO emp_audit SELECT 'TRUNCATE','-',-1; END IF; RETURN NULL; -- result is ignored bacause this is an AFTER trigger END; $emp_audit$ LANGUAGE plpgsql;
创建针对insert和update每行的触发器: AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
创建针对update的语句级的触发器: CREATE TRIGGER emp_audit_truncate AFTER TRUNCATE ON emp FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |