之前介绍过事件触发器，主要是用来捕捉DDL,这边文章介绍一下普通的触发器,主要是可以用来捕捉数据变更的,支持行级别和语句级别的触发器，支持insert,update,delete,truncate操作触发。
内部定义的相关变量参数：

NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.

OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations.

TG_NAME
Data type name; variable that contains the name of the trigger actually fired.

TG_WHEN
Data type text; a string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition.

TG_LEVEL
Data type text; a string of either ROW or STATEMENT depending on the trigger's definition.

TG_OP
Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.

TG_RELID
Data type oid; the object ID of the table that caused the trigger invocation.

TG_RELNAME
Data type name; the name of the table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME instead.

TG_TABLE_NAME
Data type name; the name of the table that caused the trigger invocation.

TG_TABLE_SCHEMA
Data type name; the name of the schema of the table that caused the trigger invocation.

TG_NARGS
Data type integer; the number of arguments given to the trigger function in the CREATE TRIGGER statement.

TG_ARGV[]
Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

举个例子可以查看对应变量的值：

CREATE OR REPLACE FUNCTION f_defined_arg ()
RETURNS trigger AS
$$
     DECLARE

     BEGIN
          RAISE NOTICE 'NEW: %', NEW;
          RAISE NOTICE 'TG_RELID: %', TG_RELID;
          RAISE NOTICE 'TG_TABLE_SCHEMA: %', TG_TABLE_SCHEMA;
          RAISE NOTICE 'TG_TABLE_NAME: %', TG_TABLE_NAME;
          RAISE NOTICE 'TG_RELNAME: %', TG_RELNAME;
          RAISE NOTICE 'TG_OP: %', TG_OP;
          RAISE NOTICE 'TG_WHEN: %', TG_WHEN;
          RAISE NOTICE 'TG_LEVEL: %', TG_LEVEL;
          RAISE NOTICE 'TG_NARGS: %', TG_NARGS;
          RAISE NOTICE 'TG_ARGV: %', TG_ARGV;
          RAISE NOTICE ' TG_ARGV[0]: %', TG_ARGV[0];

          RETURN NEW;
     END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER trig_print_arg
     BEFORE INSERT ON tbl_hank_trigger
     FOR EACH ROW EXECUTE PROCEDURE f_defined_arg('test');
    
\d tbl_hank_trigger
          Table "hank.tbl_hank_trigger"
 Column |  Type  | Collation | Nullable | Default 
--------+--------+-----------+----------+---------
 a      | bigint |           | not null | 
 b      | text   |           |          | 
Indexes:
    "tbl_hank_trigger_pkey" PRIMARY KEY, btree (a)
Triggers:
    trig_print_arg BEFORE INSERT ON tbl_hank_trigger FOR EACH ROW EXECUTE FUNCTION f_defined_arg('test')

insert into tbl_hank_trigger values (2,'hank');
NOTICE:  NEW: (2,hank)
NOTICE:  TG_RELID: 24592
NOTICE:  TG_TABLE_SCHEMA: hank
NOTICE:  TG_TABLE_NAME: tbl_hank_trigger
NOTICE:  TG_RELNAME: tbl_hank_trigger
NOTICE:  TG_OP: INSERT
NOTICE:  TG_WHEN: BEFORE
NOTICE:  TG_LEVEL: ROW
NOTICE:  TG_NARGS: 1
NOTICE:  TG_ARGV: [0:0]={test}
NOTICE:   TG_ARGV[0]: test
INSERT 0 1

捕捉DML(insert,update,delete)操作：

#创建存放捕捉动作的表
CREATE TABLE tbl_trigger_record(
    operation         text   NOT NULL,
    create_time             timestamp NOT NULL,
    userid            text      NOT NULL,
    a           bigint     NOT NULL,
    b 			text
);



#创建函数，这里由于update的原理是先delete再update,为了记录新旧数据，写下两条
CREATE OR REPLACE FUNCTION f_trigger_audit() RETURNS TRIGGER AS $$
    BEGIN
       
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO tbl_trigger_record SELECT 'delete', now(), current_user, OLD.*;
        ELSIF (TG_OP = 'UPDATE') THEN
        	INSERT INTO tbl_trigger_record SELECT 'Update_delete', now(), current_user, OLD.*;
            INSERT INTO tbl_trigger_record SELECT 'Update_insert', now(), current_user, NEW.*;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO tbl_trigger_record SELECT 'insert', now(), current_user, NEW.*;
        END IF;
        RETURN NULL; 
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_oper
AFTER INSERT OR UPDATE OR DELETE  ON tbl_hank_trigger
    FOR EACH ROW EXECUTE FUNCTION f_trigger_audit();


hank=> insert into tbl_hank_trigger values (1,'hank');
hank=> update tbl_hank_trigger set b='banana' where a=1;
hank=> delete from tbl_hank_trigger ;

#查看捕捉操作的表
hank=> select * from tbl_trigger_record ;
   operation   |        create_time         | userid | a |   b    
---------------+----------------------------+--------+---+--------
 insert        | 2021-04-19 11:31:26.783165 | hank   | 1 | hank
 Update_delete | 2021-04-19 11:31:50.328758 | hank   | 1 | hank
 Update_insert | 2021-04-19 11:31:50.328758 | hank   | 1 | banana
 delete        | 2021-04-19 11:32:11.447055 | hank   | 1 | banana

捕捉truncate例子

#新建一个捕捉truncate的表和触发器函数,TRUNCATE只能定义STATEMENT级别的触发器，也就是说只会捕捉该条语句，不会捕捉表的数据变更，而且不支持row级别的触发器，所以表就算写了要插入OLD.*,也不会有数据写入。


CREATE TABLE tbl_trigger_truncate_record(
    operation         text   NOT NULL,
    create_time       timestamp NOT NULL,
    userid            text      NOT NULL,
    table_name   text,
    a           bigint,
    b 			text
);


CREATE OR REPLACE FUNCTION f_trigger_audit_truncate()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN
        IF  (TG_OP = 'TRUNCATE') THEN
        INSERT INTO tbl_trigger_truncate_record SELECT 'truncate', now(), current_user,TG_TABLE_NAME,OLD.*;
        END IF;
        RETURN NULL;
    END;
$function$;

create trigger t_truncate  AFTER truncate  ON tbl_hank_trigger 
FOR EACH STATEMENT EXECUTE FUNCTION f_trigger_audit_truncate();

hank=> truncate table tbl_hank_trigger;
TRUNCATE TABLE
hank=> select * from tbl_trigger_truncate_record;
 operation |        create_time         | userid |    table_name    | a | b 
-----------+----------------------------+--------+------------------+---+---
 truncate  | 2021-04-19 11:44:28.029128 | hank   | tbl_hank_trigger |   | 
(1 row)

#可见无法捕捉行记录，只能捕捉到动作。