前言:
有时候我们向数据库表中插入数据时,需要在插入前保证数据的约束。有两种方法可以保证约束不被破坏,1、插入前手动检查数据,2、使用触发器。今天我们就用触发器来保证约束完整性。
假设有如下两个关系
课程编号(course_name) | 上课教室(room) | 课程时间(time) |
English | BO-101 | AM |
History | BO-102 | AM |
Physics | BO-103 | PM |
Biology | BO-104 | PM |
course关系
教师编号(id) | 教授课程(course_name) |
10001 | History |
10002 | English |
10003 | Physics |
10001 | Biology |
teaches关系
现在要求实现约束“同一个教师不可能在同一时间在两个不同的教室上课”
这就要求我们每次向teaches关系中插入的数据,必须保证教师不会在同一时间,出现在两个不同的教室。转换成SQL语句就是
NOT EXISTS ( SELECT id FROM teaches NATURAL JOIN course GROUP BY id, time HAVING COUNT(id) > 1);
创建触发器的SQL语句
CREATE TRIGGER example_trigger BEFORE INSERT ON teaches FOR EACH ROW EXECUTE PROCEDURE example_function();
example_function定义如下
CREATE FUNCTION example_function() RETURNS TRIGGER LANGUAGE plpgsql AS $fun_trigger$ BEGIN IF EXISTS ( SELECT id FROM (SELECT id, course_name FROM teaches UNION (SELECT NEW.id, NEW.course_name)) AS T NATURAL JOIN course GROUP BY id, time HAVING COUNT(id) > 1) THEN RAISE EXCEPTION 'CONSTRAINT ERROR: A teacher cannot be in two classrooms at the same time'; END IF; RETURN NEW; END; $fun_trigger$;
返回为TRIGGER(RETURNS TRIGGER)的存储过程中会自动创建一些变量
NEW:INSERT/UPDATE 操作新记录
OLD:DELETE/INSERT/UPDATE操作旧记录。更多的参数请参阅postgreSQL文档
course关系的数据:
SELECT course_name, room, time FROM course;
teaches关系的数据:
SELECT id, course_name FROM teaches;
插入一条有冲突的语句:
INSERT INTO teaches(id, course_name) VALUES('10003', 'Finance');
大功告成。
附注:
1.如果你需要在一个表(TABLE)更新(UPDATE)和插入(INSERT)时都触发触发器,SQL为
CREATE TRIGGER example_trigger BEFORE INSERT OR UPDATE ON teaches FOR EACH ROW EXECUTE PROCEDURE example_function();
2.如果你需要在一个表(TABLE)的指定列(COLUMN)更新时触发触发器,SQL为
CREATE TRIGGER example_trigger BEFORE UPDATE OF course_name ON teaches FOR EACH ROW EXECUTE PROCEDURE example_function();
3.如果你想用一个触发器(TRIGGER)作用在多个表上(TABLE),该怎么办呢?
CREATE TRIGGER example_trigger BEFORE INSERT ON teaches, course FOR EACH ROW EXECUTE PROCEDURE example_function();
不幸的是,这样的语法在postgreSQL中是不行的,你只能这样
CREATE TRIGGER example_trigger1 BEFORE INSERT ON teaches FOR EACH ROW EXECUTE PROCEDURE example_function(); CREATE TRIGGER example_trigger2 BEFORE INSERT ON course FOR EACH ROW EXECUTE PROCEDURE example_function();
参见点击查看详情
结束语:以上的SQL语句全部是在PostgreSQL(9.4.4)中运行通过,由于各种数据库的语法的差别,在其他数据库中并不能保证不会报错。由于本人水平所限,如有错误,欢迎批评指正。
参考文献:
1. 《数据库系统概念》第六版 5.3节 触发器
2. http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
3. http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html
4. http://www.tutorialspoint.com/postgresql/postgresql_triggers.htm
5. http://stackoverflow.com/questions/16102188/postgresql-insert-trigger-to-set-value
6. http://www.postgresql.org/message-id/17673.1129178350@sss.pgh.pa.us
版权声明:本文为博主原创文章,未经博主允许不得转载。
相关推荐
postgres-deno:Deno的PostgreSQL扩展:在PostgreSQL函数和触发器中运行Typescript
PostgreSql+PostGis 创建空间数据库
Go中的PostgreSQL触发器ptgo是如何在Go中编写PostgreSQL触发器的示例。 分叉它并使用它来实现自己的触发器! 查看,以获取有关ptgo内部的描述。 今天就在Twitter上关注我们!
PostgreSQL sql语句:创建用户,创建数据库,用户授权
它可以用于任何版本 7.5 或以上的 PostgreSQL 数据库服务器,并支持大部份 PostgreSQL最新版本的功能,包括触发器、函数、管理用户等。Navicat 的功能足以符合专业开发人员的所有需求,但是对 PostgreSQL 的新手来说...
在AIX环境下安装postgresql
PostgreSQL(postgresql-13.5.tar.bz2) PostgreSQL是一种特性非常齐全的自由软件的...PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。
从这里可以看出PostgreSQL表...通过作用于主表的触发器,把主表的修改重定向到适当的分区表等工作,分区的创建和管理都需要我们用语言来控制,增加了开发人员的工作量,PostgreSQL表分区的所有表结构和表分区及触发器
windows下编译postgresql源码编译环境搭建
服务器处于没有外网的情况下,如何安装PostgreSQL,并且通过PGADMIN访问
实现轻松创建PostgreSQL的分区表DDL
PostgreSQL数据库HA环境搭建详细步骤
PostgreSQL(postgresql-14.2-2-windows-x64.exe),适用于Windows系统:PostgreSQL是...PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。
PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性:复杂查询、外键、触发器、视图、事务完整性、MVCC。同样,PostgreSQL 可以用许多方法扩展,比如, 通过增加新的数据类型、函数、操作符、聚集函数、索引。...
PostgreSQL(postgresql-14.2.tar.gz),适用于Linux系统:PostgreSQL是一种特性非常...PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。
PostgreSQL(postgresql13-devel-13.5-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/...PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。
PostgreSQL(postgresql14-devel-14.1-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/...PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。
PostgreSQL(postgresql13-contrib-13.5-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/...PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。
易于使用的解决方案,可以从postgresql dump创建sqlite数据库。 默认脚本格式 尽可能快地 默默忽略不支持的Postgresql功能 gzip支持 在发行部分中,您可以下载pg2sqlite.jar的预构建版本。 如何建造 git clone ...
PostgreSQL(postgresql-14.1.tar.bz2) PostgreSQL是一种特性非常齐全的自由软件的...PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。