postgresql触发器详解_2.在数据库spdg中创建一个触发器:当向spdgb插入记录时,检查该记录的客户编号在khb

简介

        触发器（Trigger）是由事件自动触发执行的一种特殊的存储过程，触发事件可以是对一个表进行INSERT、UPDATE、DELETE等操作。触发器经常用于加强数据的完整性约束和业务规则上的约束等在数据库中，为了提高自动化效率，往往会配置一些触发器，由某一事件的发生而触发运行指定的函数，增加数据库运行的自动化效率。例如在postgresql数据库分区表应用中，早期还未出声明式分区的时候，使用的继承式分区就大量使用了触发器前文中提到的

在postgresql数据库中分为普通触发器和事件触发器在用法上有区别。

普通触发器

普通触发器主要针对单表、单视图的INSERT，UPDATE，DELETE，TRUNCATE语句引起的数据变化前后而触发触发器函数的运行。

语法

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
 
where event can be one of:
 
    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

创建触发器，相关参数解析

name

触发器的名称，创建触发器，其schema会同作用表一致，同一作用表上的触发器不能同名。

BEFORE
AFTER
INSTEAD OF
决定该函数是在事件之前、之后还是取代事件时调用。 约束触发器只能被声明为AFTER。对于UPDATE事件，可以指定表中某一列发生变化而触发。

UPDATE OF column_name1 [, column_name2 ... ]
INSTEAD OF UPDATE事件不支持字段的列表。

table_name
触发器作用的表或视图的名称(表名在未指定schema时，会从search_path中检索)

referenced_table_name：约束引用的另外一个表的名字（可以有模式修饰）。这个选项用于外键约束， 不推荐用于一般用途。只能为约束触发器指定。

DEFERRABLE：指示触发器是否可以延迟执行。如果指定了 DEFERRABLE，那么触发器可以通过使用事务延迟执行触发操作。这意味着触发器的约束可以稍后在事务结束时进行检查。如果在触发器中使用了 DEFERRABLE 参数，还可以使用 INITIALLY IMMEDIATE 或 INITIALLY DEFERRED 来指定触发器的初始延迟状态。

NOT DEFERRABLE：指示触发器不可延迟执行。如果指定了 NOT DEFERRABLE，那么触发器会立即执行，而不管事务的延迟状态如何。

INITIALLY IMMEDIATE：指示触发器在事务开始时立即执行。这是默认设置，如果触发器没有显式指定 DEFERRABLE 和 INITIALLY 参数时的默认行为。

INITIALLY DEFERRED：指示触发器在事务开始时不立即执行，而是等待显式或隐式地将事务切换到延迟模式后再执行。在设置为 INITIALLY DEFERRED 的触发器上，可以使用 SET CONSTRAINTS 命令将事务切换到延迟模式。

FOR EACH ROW：对于根据表中的每一行触发的操作，触发器函数将针对每一行执行。

FOR EACH STATEMENT：对于被触发的每个 SQL 语句，触发器函数将只执行一次。如果都没有声明， 那么FOR EACH STATEMENT将是默认设置。 约束触发器只能声明为FOR EACH ROW。

condition
一个决定触发器函数实际上是否执行的布尔表达式。如果声明了WHEN， 那么该函数只有condition 返回true时被调用。在FOR EACH ROW触发器中， WHEN条件可以通过分别写OLD.column_name或NEW.column_name参考字段的旧的和/或新的行值。

INSTEAD OF触发器不支持WHEN条件。

目前，WHEN表达式不能包含子查询。

请注意，对于约束触发器，WHEN条件的计算是不延迟的， 只是在行更新操作执行之后立即发生。如果该条件计算不为真， 那么触发器就不排队延迟执行。

function_name
一个用户提供的函数，它声明为不接受参数并且返回trigger类型， 该函数将在触发器被触发时调用。

arguments
一个可选的用逗号分隔的参数列表，它将在触发器执行的时候提供给函数。 这些参数是文本字符串常量。也可以在这里写简单的名字和数值常量， 但是它们会被转换成字符串。请检查该触发器函数的实现语言的描述， 找出如何在该函数中访问这些参数；这些参数可能和普通的函数参数不同。

注意
要在表上创建一个触发器，用户必需在该表上有TRIGGER权限。 用户也必须在触发器函数上有EXECUTE权限。

示例

 orders 的表，包含以下列：order_id：订单 ID（主键），customer_id：客户 ID，order_date：下单日期，total_amount：订单总金额

一个名为 customers 的表，包含以下列：customer_id：客户 ID（外键），customer_email：客户电子邮件地址
您希望实现以下业务逻辑要求：

在每次向 orders 表中插入新的订单记录时，需要检查对应 customer_id 是否存在于 customers 表中，如果不存在，则自动向 customers 表中插入一个新的客户记录，并使用一个默认的电子邮件地址。

 
 
-- 创建 customers 表
CREATE TABLE customers (
  customer_id integer PRIMARY KEY,
  customer_email VARCHAR(255) not null
);
 
-- 创建 orders 表，并添加外键约束
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL
);
 
 
/*ON DELETE CASCADE 定义了级联删除，意味着当 customers 表中的对应记录被删除时，与之相关的 orders 表中的记录也将被自动删除。
在每次向 orders 表中插入新的订单记录时，需要检查对应 customer_id 是否存在于 customers 表中，如果不存在，则自动向 customers 表中插入一个新的客户记录，并使用一个默认的电子邮件地址。*/
 
-- 创建触发器函数
CREATE OR REPLACE FUNCTION insert_customer_if_not_exists()
RETURNS TRIGGER AS $$
BEGIN
  -- 检查 customers 表中是否存在对应的 customer_id
  IF NOT EXISTS (
    SELECT 1 FROM customers WHERE customer_id = NEW.customer_id
  ) THEN
    -- 在 customers 表中插入新的客户记录，使用默认电子邮件地址
    INSERT INTO customers (customer_id, customer_email)
    VALUES (NEW.customer_id, 'default@example.com');
  END IF;
 
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- 创建触发器
CREATE TRIGGER insert_customer_trigger
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION insert_customer_if_not_exists();
 
--插入测数据
insert into orders
(
 customer_id ,
 order_date,
 total_amount)
  select generate_series(100001,100051) as n , current_date - cast((random()*365) as integer),generate_series(50.00, 100.00)

向orders插入测试数据后,customers表也相应的增加了数据。

事件触发器

事件触发器会监控全库的ddl_command_start, ddl_command_end ，table_rewrite和 sql_drop事件，有相应事件而触发指定的函数。主要用于弥补PostgreSQL以前版本不支持DDL触发器的不足。目前，事件触发器支持以下3种DDL
事件。
·ddl_command_start：DDL开始执行前被触发。
·ddl_command_end：一个DDL执行完成后被触发。
·sql_drop：删除数据库对象前被触发。
·table_rewrite：事件在表被命令ALTER TABLE和 ALTER TYPE的某些动作重写之前发生
由于事件触发器涉及的权限较大，比如能禁止DDL操作等，所以只有超级用户才能
创建和修改事件触发器。

使用语法

CREATE EVENT TRIGGER name
    ON event
    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
    EXECUTE { FUNCTION | PROCEDURE } function_name()

name：一个新的触发器的名字．这个名字必须在数据库内是唯一的.

event：触发调用一个给定函数的事件名子(值只=ddl_command_start, ddl_command_end ，table_rewrite和 sql_drop)

filter_variable：过滤事件的变量名称.这将限制它所支持的事件的一个子集去触发该触发器.现在仅支持filter_variable值为TAG.

filter_value：可以触发该触发器的 filter_variable相关的值.对于TAG,这意味着一个tags的命令列表.(例如.'DROP FUNCTION')，以下列出filter_value可选值。未选WHEN时，默认对应even trigger所有语句均会触发。

function_name：触发器函数名称.

TAG命令列表

   注：sql_drop事件为任何删除数据库对象的操作在 ddl_command_end事件触发器之前发生。table_rewrite事件在表被命令ALTER TABLE和 ALTER TYPE的某些动作重写之前发生。虽然其他控制语句（例如 CLUSTER和VACUUM）也可以用来重 写表，但是它们不会触发table_rewrite事件。 

示例

禁用全局alter table 的操作

CREATE OR REPLACE FUNCTION abort_alter_table()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION 'command % is disabled', tg_tag;
END;
$$;
 
CREATE EVENT TRIGGER abort_alter_table 
ON ddl_command_start when TAG in ('ALTER TABLE','alter view')
   EXECUTE FUNCTION abort_alter_table();

这里的tg_tag变量，返回tag的命令标签对应的语句。下面会继续例举触发器中特殊变量的种类。
设置 RAISE EXCEPTION时，当触发器函数遇到RAISE EXCEPTION语句时，它会立即停止执行，并将异常信息返回给调用者。

测试语句

create table text(id int);
   alter table text add column flag varchar(20)
   create view v_text as select *  from text;
   alter view v_text add column flag varchar(20)

当执行到对应的约束语句的时候都会被限制 

 注：truncate table语句还是可以执行的。因为在PostgreSQL中truncate事件是使用普通触发器触发的，事件触发器是不会触发truncate table的。

return trigger涉及的特殊变量

在创建函数时，设置返回值为trigger 时，在事务段这种可以调用关于trigger的特殊变量

- NEW：在行级触发器中，表示INSERT或UPDATE操作的新行数据。
- OLD：在行级触发器中，表示UPDATE或DELETE操作的旧行数据。
- TG_NAME：触发器的名称。
- TG_WHEN：触发器的执行时机，可以是BEFORE、AFTER或INSTEAD OF。
- TG_LEVEL：触发器的级别，可以是ROW或STATEMENT。
- TG_OP：触发器所针对的操作类型，可以是INSERT、UPDATE、DELETE或TRUNCATE。
- TG_RELID：触发器所针对的表的对象ID。
- TG_RELNAME：触发器所针对的表的名称。
- TG_TABLE_NAME：触发器所针对的表的名称。
- TG_TABLE_SCHEMA：触发器所针对的表的模式名称。
- TG_NARGS：触发器函数在CREATE TRIGGER语句中接收的参数数量。
- TG_ARGV[]：触发器函数在CREATE TRIGGER语句中接收的参数数组。

示例

创建测试语句

--创建触发器函数
CREATE OR REPLACE FUNCTION print_trigger_variables() RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'NEW: %', NEW;
    RAISE NOTICE 'OLD: %', OLD;
    RAISE NOTICE 'TG_NAME: %', TG_NAME;
    RAISE NOTICE 'TG_WHEN: %', TG_WHEN;
    RAISE NOTICE 'TG_LEVEL: %', TG_LEVEL;
    RAISE NOTICE 'TG_OP: %', TG_OP;
    RAISE NOTICE 'TG_RELID: %', TG_RELID;
    RAISE NOTICE 'TG_RELNAME: %', TG_RELNAME;
    RAISE NOTICE 'TG_TABLE_NAME: %', TG_TABLE_NAME;
    RAISE NOTICE 'TG_TABLE_SCHEMA: %', TG_TABLE_SCHEMA;
    RAISE NOTICE 'TG_NARGS: %', TG_NARGS;
    RAISE NOTICE 'TG_ARGV: %', TG_ARGV;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
--创建测试表
create table text(id int,text varcahr(20));
 
--插入测试数据
 insert into text (id,text) SELECT n,chr(cast(TRUNC(33 + RANDOM() * (126 - 33 + 1)) as int)) AS random_character FROM generate_series(1000, 2001) as n;
 
--创建触发器
CREATE TRIGGER print_variables_trigger
    BEFORE UPDATE ON TEXT
    FOR EACH ROW
    EXECUTE FUNCTION print_trigger_variables();
	
--执行触发语句
UPDATE  text SET TEXT='XG' where id = 1085;

查看测试结果，相应的特殊变量的值都打印出来了，根据情况一一对应了解。