批量新增触发器

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
/**
 * http://www.open-open.com/lib/view/open1427082615949.html
 * 利用触发器设置PostgreSQL表监控:监控表的增,删,改
 * @author jade
 */
public class BatchGenTriger {

    public static void main(String[] args) {
        String filePath = "C:\\Users\\jade\\Desktop\\trigers.txt"; //给我你要读取的文件夹路径
        try {
            try (BufferedReader bufReader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8")))//数据流读取文件
            {
                String temp = null;
                while ((temp = bufReader.readLine()) != null) {
                    System.out.println("CREATE TRIGGER \"tri_" + temp + "\" BEFORE INSERT OR UPDATE OR DELETE ON " + temp + " FOR EACH ROW EXECUTE PROCEDURE change_trigger();");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

//CREATE FUNCTION change_trigger()
//    RETURNS trigger AS $$
//    BEGIN
//        IF TG_OP = 'INSERT' THEN
//            INSERT  INTO logging.user_history(tabname,schemaname,operation,new_val)  values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP, (NEW));
//        RETURN NEW;
//        ELSIF TG_OP = 'UPDATE'  THEN
//                INSERT INTO logging.user_history(tabname,schemaname,operation,new_val,old_val) values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP, (NEW), (OLD));
//        RETURN NEW;
//        ELSIF TG_OP = 'DELETE'  THEN
//              INSERT INTO logging.user_history(tabname,schemaname,operation, old_val) values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP, (OLD));
//    RETURN OLD; --返回值要与ELSIF平齐,因为先插入后最好才执行返回
//    END IF;
//END;
//$$ LANGUAGE 'plpgsql' SECURITY DEFINER;--security definer是指定创建该函数用户的权限执行,security invoker是指以调用该函数用户发权限执行

 

CREATE SCHEMA logging;
CREATE TABLE logging.user_history (
    id serial,
    tstamp timestamp DEFAULT now(),
    schemaname text,
    tabname text,
    operation text,
    who text DEFAULT current_user,
    new_val json,
    old_val json
);