Postgresql数据库创建自动化任务-触发器

大家好，欢迎来到IT知识分享网。

1. 触发器（Trigger）

触发器是一种特殊的存储过程，它在特定事件（如INSERT、UPDATE、DELETE）发生时自动执行。触发器通常用于实现数据完整性约束、审计日志、自动计算等。

1.1 创建触发器

触发器的创建分为两步：

• 创建一个触发器函数。
• 将触发器函数绑定到表的事件上。

1.1.1 创建触发器函数

触发器函数是一个返回TRIGGER类型的函数，可以使用PL/pgSQL编写。

CREATE FUNCTION update_employee_audit() RETURNS TRIGGER AS $ BEGIN IF (TG_OP = 'INSERT') THEN INSERT INTO employee_audit (employee_id, action, action_time) VALUES (NEW.id, 'INSERT', NOW()); ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO employee_audit (employee_id, action, action_time) VALUES (NEW.id, 'UPDATE', NOW()); ELSIF (TG_OP = 'DELETE') THEN INSERT INTO employee_audit (employee_id, action, action_time) VALUES (OLD.id, 'DELETE', NOW()); END IF; RETURN NEW; END; $ LANGUAGE plpgsql;

1.1.2 绑定触发器

使用CREATE TRIGGER语句将触发器函数绑定到表的事件上。

CREATE TRIGGER employee_audit_trigger AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW EXECUTE FUNCTION update_employee_audit();

1.2 触发器的类型

• 行级触发器（FOR EACH ROW）：针对每一行触发。
• 语句级触发器（FOR EACH STATEMENT）：针对整个SQL语句触发。

1.3 触发器的时机

• BEFORE：在事件发生之前触发。
• AFTER：在事件发生之后触发。
• INSTEAD OF：用于视图，替代事件本身。

1.4 删除触发器

使用DROP TRIGGER语句删除触发器。

DROP TRIGGER employee_audit_trigger ON employees;

2. 规则（Rule）

规则是一种机制，用于重写SQL语句的行为。它可以将对表的操作（如SELECT、INSERT、UPDATE、DELETE）重定向到其他操作。

2.1 创建规则

使用CREATE RULE语句创建规则。

CREATE RULE update_employee_rule AS ON UPDATE TO employees DO INSTEAD INSERT INTO employee_audit (employee_id, action, action_time) VALUES (NEW.id, 'UPDATE', NOW());

2.2 规则的用途

• 视图更新：通过规则实现视图的更新操作。
• 数据重定向：将对表的操作重定向到其他表或操作。

2.3 删除规则

使用DROP RULE语句删除规则。

DROP RULE update_employee_rule ON employees;

3. 触发器与规则的区别