PostgreSql学习-触发器

一、触发器简介
触发器(trigger)：是一种特殊的存储过程，可以用来对表实施复杂的完整性约束，保持数据的一致性。
当触发器所保护的数据发生改变时，触发器会自动被激活，并执行触发器中所定义的相关操作，从而保证对数据的不完整性约束或不正确的修改。

1、基础知识
(1)触发器的定义：就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。（因此触发器不需要人为的去调用，也不能调用）
(2)触发器可以分为数据行级触发器和语句级触发器。
语句级触发器：指执行每个SQL时只执行一次。关键字 for each statement。
行级触发器：指每行都会执行一次。关键字for each row。
(3)触发器可以连接到表和视图。
(4)创建触发器以前，必须定义触发器使用的函数。这个函数不能有任何参数，它的返回值的类型必须是trigger。
函数定义好以后，用命令CREATE TRIGGER创建触发器。多个触发器可以使用同一个函数。

2、触发器可用于以下目的：
（1）验证输入数据
（2）执行业务规则
（3）为不同文件中新插入的行生成唯一值
（4）写入其他文件以进行审计跟踪
（5）从其他文件查询交叉引用目的
（6）访问系统函数
（7）将数据复制到不同的文件以实现数据一致性

3、使用触发器的优点：
（1）它提高了应用程序的开发速度。 因为数据库存储触发器，所以您不必将触发器操作编码到每个数据库应用程序中。
（2）全局执法业务规则。定义触发器一次，然后将其重用于使用数据库的任何应用程序。
（3）更容易维护 如果业务策略发生变化，则只需更改相应的触发程序，而不是每个应用程序。
（4）提高客户/服务器环境的性能。 所有规则在结果返回之前在服务器中运行。

二、触发器的变量
当一个PL/pgSQL 函数作为一个触发器被调用时，系统自动在最外层的块创建一些特殊的变量。这些变量分别是：

（1）NEW:数据类型是RECORD。对于行级触发器，它存有INSERT或UPDATE操作产生的新的数据行。对于语句级触发器，它的值是NULL。
（2）OLD:数据类型是RECORD。对于行级触发器，它存有被UPDATE或DELETE操作修改或删除的旧的数据行。对于语句级触发器，它的值是NULL。
（3）TG_NAME:数据类型是name，它保存实际被调用的触发器的名字。
（4）TG_WHEN:数据类型是text，根据触发器定义信息的不同，它的值是BEFORE 或AFTER。
（5）TG_LEVEL:数据类型是text，根据触发器定义信息的不同，它的值是ROW或STATEMENT。
（6）TG_OP:数据类型是text，它的值是INSERT、UPDATE或DELETE，表示触发触发器的操作类型。
（7）TG_RELID:数据类型是oid，表示触发器作用的表的oid。
（8）TG_RELNAME:数据类型是name，表示触发器作用的表的名字。它与下面的变量TG_TABLE_NAME的作用是一样的。
（9）TG_TABLE_NAME:数据类型是name，表示触发器作用的表的名字。
（10）TG_TABLE_SCHEMA:数据类型是name，表示触发器作用的表所在的模式。
（11）TG_NARGS:数据类型是integer，表示CREATE TRIGGER命令传给触发器过程的参数的个数。
（12）TG_ARGV[]:数据类型是text类型的数组。表示CREATE TRIGGER命令传给触发器过程的所有参数。下标从0开始。
TG_ARGV[0]表示第一个参数，TG_ARGV[1]表示第二个参数，以此类推。 如果下标小于0或大于等于tg_nargs，将会返回一个空值。

三、触发器语法
1、PostgreSQL创建触发器

CREATE [ CONSTRAINT ] TRIGGER 触发器名 { BEFORE | AFTER | INSTEAD OF } { 触发事件 [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

注：
触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称， 没有实质的用途
触发时间：指明触发器何时执行，该值可取（before，after，INSTEAD OF）
触发事件：指明哪些数据库动作会触发此触发器（insert，update，delete,truncate）
表名：数据库触发器所在的表
FOR EACH ROW触发器被标记的操作修改的每一行被称为一次。相比之下，FOR EACH STATEMENT触发器为只执行一次对于任何给定的操作，不管它有多少行修改。

2、列出触发器

SELECT * FROM pg_trigger; ---列出所有触发器
SELECT * FROM pg_class;
SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='表名'; ---列出特定表上的触发器

3、删除触发器

drop trigger 触发器名称

四、触发器实例
实例1、使用一个行级BEFORE触发器检查表emp的 被插入或更新操作完成以后的数据行在列salary上的值是否大于0，列name是否不是空值：

–创建测试表

CREATE TABLE test.emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

–创建触发器函数

   CREATE FUNCTION test.emp_stamp() RETURNS trigger AS
    $$
        BEGIN
            IF NEW.empname IS NULL THEN
                RAISE EXCEPTION 'empname cannot be null';
            END IF;
            IF NEW.salary IS NULL THEN
                RAISE EXCEPTION '% cannot have null salary', NEW.empname;
            END IF;
            IF NEW.salary < 0 THEN
                RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
            END IF;
            NEW.last_date := current_timestamp;
            NEW.last_user := current_user;
            RETURN NEW;
        END;
    $$ LANGUAGE plpgsql;

–创建触发器

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON test.emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

–测试触发器

insert into test.emp values ('John'); --salary为空，触发器报错
/*ERROR:  John cannot have null salary
CONTEXT:  PL/pgSQL function emp_stamp() line 7 at RAISE */

insert into test.emp values (null,1200);   --empname为空，触发器报错
/*ERROR:  empname cannot be null
CONTEXT:  PL/pgSQL function emp_stamp() line 4 at RAISE */

insert into test.emp values ('John',-200); --salary为负数，触发器报错
/*ERROR:  John cannot have a negative salary
CONTEXT:  PL/pgSQL function emp_stamp() line 10 at RAISE */

insert into test.emp values ('Bob',1200);  --成功插入正常数据，并记录了最后操作时间和操作用户

注：
(1)行级before触发器一般用于检查和修改将被插入和更新的数据。
行级after触发器一般用于将表中被更新的数据记录到其它的表中，或者检查与其它的表中的数据是否是一致的。
(2)before触发器的执行效率比after触发器高，在before触发器和after触发器都能被使用的情况下，应该选择before触发器。
(3)如果同一表上同对同一个事件定义了多个触发器，这些触发器将按它们的名字的字母顺序被触发。

实例2、用于审计的触发器过程
这个例子触发器保证了在emp表上的任何插入、更新或删除一行的动作都被记录（即审计）在emp_audit表中。当前时间和用户名以及在其上执行的操作类型都会被记录到行中。
–创建测试表

CREATE table test.emp1(
  empname text NOT NULL,
  salary integer
);

–创建审计表

CREATE TABLE test.emp_audit(
  operation char(1) NOT NULL ,
  stamp timestamp NOT NULL ,
  user1 text NOT NULL ,
  empname text NOT NULL ,
  salary integer NOT NULL
);

–创建触发器函数

CREATE OR REPLACE FUNCTION test.process_emp_audit() returns trigger AS
$$
BEGIN
  if (TG_OP='INSERT') THEN
    INSERT INTO test.emp_audit SELECT 'I',now(),user,new.*;
    RETURN new;
  ELSEIF (TG_OP='DELETE') THEN
    INSERT INTO test.emp_audit select 'D',now(),user,old.*;
    RETURN old;
  ELSEIF (TG_OP='UPDATE') THEN
    INSERT INTO test.emp_audit SELECT 'U',now(),user,new.*;
    RETURN new;
  END IF ;
  RETURN NULL ;
END;
$$
LANGUAGE plpgsql;

–创建触发器

CREATE TRIGGER emp_audit1 AFTER INSERT OR DELETE OR UPDATE
  ON test.emp1
  for each ROW
  execute procedure test.process_emp_audit();

–测试触发器

INSERT INTO test.emp1 values('Jack',5000);
SELECT * from test.emp_audit;
UPDATE test.emp1 SET salary=4500 where empname='Jack';
SELECT * from test.emp_audit;
DELETE FROM test.emp1 where empname='Jack';
SELECT * from test.emp_audit;