Java代码
- 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是指以调用该函数用户发权限执行
Sql代码
- 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
- );
