postgre简单存储过程+触发器demo

一个最简易的存储过程 + 触发器的demo

--创建一张test表，为了测试触发器
CREATE TABLE test(
    testcol	varchar(10)
);
--创建一张test_trigger_record表，为了测试触发器响应后执行的存储过程是否正常执行完毕
CREATE TABLE test_trigger_record(
    operation  text   NOT NULL,
    create_time  timestamp NOT NULL,
    userid text      NOT NULL,
    test	text
);

--创建trigger_audit函数(存储过程)，当表操作是‘insert’时，往test_trigger_record表中插入一条数据
--values( 'insert', now(), current_user, NEW.testcol) --》NEW.testcol等同于插入数据的testcol列值
CREATE OR REPLACE FUNCTION trigger_audit() RETURNS TRIGGER AS $$
    BEGIN   
		--RAISE NOTICE日志输出，可在控制台查看执行日志，也可在X:\PostgreSQL\8.3\data\pg_log下的当天日期文件中看到日志输出
		RAISE NOTICE  'trigger_audit() --> 存储过程执行中';  
        IF(TG_OP = 'INSERT') THEN
            INSERT INTO test_trigger_record SELECT 'insert', now(), current_user, NEW.testcol;
        END IF;
        RETURN NULL; 
    END;
$$ LANGUAGE plpgsql;

--创建一个名为test_trigger的触发器绑定到test表中，表数据每次新增都执行一次
CREATE TRIGGER test_trigger AFTER INSERT ON test FOR EACH ROW EXECUTE PROCEDURE trigger_audit();
  
--往test表中做插入1条数据
INSERT INTO test (testcol)VALUES('测试触发器');

--查询触发器是否生效（插入一条test表的操作记录）
select * from test_trigger_record;

存储过程开发小知识：

1.在存储过程里面可以输出日志进行跟踪

RAISE NOTICE  ' 存储过程执行中';  

--在存储过程中定义变量
declare sql text;
-- 输出变量，会自动替换占位符% 
RAISE NOTICE  'trigger_audit() --> 存储过程中 sql ： % ' , sql; 

2. 存储过程中拼接sql稍微麻烦点：

 拼接varchar类型变量时需要在变量左右机上 单引号 '， 在字符串内拼接就需要转义单引号，也就是使用三个 ‘ ，最后一个单引号还需要拼接E'\''进行转义

declare shift text;
 
 IF(TG_OP = 'INSERT') THEN
    strsql := 'UPDATE ' || TG_TABLE_NAME 
					    || E' set shift = '''|| shift 
					    ||''' where id= ''' || NEW.id
					    ||''' AND lot =''' || NEW.lot 
				    	||''' AND creat_dt = ''' || NEW.creat_dt   ||E'\'';
	EXECUTE (strsql); ---执行拼接后的sql

3. 当存储过程因为长时间未使用或没有备注导致需要在N多个存储过程中快速锁定需要修改的那个存储过程：

先把原有存储过程的触发效果触发一次（原本做了什么动作会触发就再做一次，记录执行时间）可在postgre安装目录下的\data\pg_log下的当天日期文件中看到刚刚执行过的存储过程的日志输出，不过是否输出日志取决于存储过程代码中是否有日志输出语句，或者存储过程中有EXECUTE (sql)语句执行也会被日志记录下来（存储过程的函数名会被日志输出），这样就能快速锁定需要修改的存储过程