关于postgresql触发器的总结（lab作业系列）

上题：

　　In this tutorial you will create a stored procedure and triggers to check a complex constraint. Consider the table definition below:

While the unique constraints defined here are sensible, they are not sufficient to express the constraint that a car (identified by its plate) cannot be rented more than once on any particular day.

1. Explain briefly why this constraint cannot be expressed using a CHECK constraint.

2. Write a stored procedure (function in postgres) which takes as input a plate, start date and end date and throws an error if table car rental contains any row with the same plate where the rental period is different from but overlaps with the given period. Note: Throwing errors in postgres is documented here: https://www.postgresql.org/docs/9.6/static/plpgsql-errors-and-messages.html Note: By checking only for different rental periods we don’t need to worry about the newly inserted or updated tuple using the procedure in a trigger (see next question). Duplicate periods are already prevented by the uniqueness constraints specified.

3.Create triggers which invoke the stored procedure created to enforce the “no duplicate rentals” constraint. Note: In postgres, trigger functions have no arguments and return type trigger: https://www.postgresql.org/docs/9.6/static/plpgsql-trigger.html Create a separate trigger function that meets these requirements and invokes the function defined earlier.

Note: You can test that your triggers work by inserting and updating tuples in car rentals:

-- should work
INSERT INTO car_rentals VALUES ('2-F4ST', '2015-02-02', '2015-02-11', 'DI123');
UPDATE car_rentals SET start_date = '2015-02-01', end_date = '2015-02-10';
INSERT INTO car_rentals VALUES ('SP33DY', '2015-01-20', '2015-02-05', 'DI234');
-- should fail (test individually)
UPDATE car_rentals SET plate = '2-F4ST' WHERE plate = 'SP33DY';
INSERT INTO car_rentals VALUES ('2-F4ST', '2015-02-10', '2015-02-15', 'DI234');
INSERT INTO car_rentals VALUES ('2-F4ST', '2015-01-20', '2015-02-15', 'DI234');
INSERT INTO car_rentals VALUES ('2-F4ST', '2015-02-02', '2015-02-09', 'DI234');
-- should work
INSERT INTO car_rentals VALUES ('2-F4ST', '2015-03-01', '2015-03-10', 'DI234');

-- additional test
update car_rentals set end_date = '2015-03-02' where plate = '2-F4ST' and license_nr='DI234'

第一问：check只能检测固定数值（给出来的值）进行约束，所以布恩能够用check，用触发器

首先是触发器，基本概念和用法：https://www.yiibai.com/postgresql/postgresql-trigger.html

建立一个触发器，首先先创造出对应函数，即你想让触发器完成什么样的工作，里面必须要有对条件的正确判断，begin开始，end结尾，外层套create的大套子，二三问和在一起写个触发器：

 1 create or replace function chk_car_rentals()
 2   returns trigger as $$
 3 begin
 4 if TG_OP='INSERT' then
 5   if (select count(*) from car_rentals
 6   where new.plate=plate and new.start_date <= end_date and new.end_date >= start_date
 7   group by plate) >0 then
 8     raise exception 'aaaaa';
 9     return null;
10   end if;
11 elsif TG_OP='UPDATE' then
12   if new.plate <> old.plate then
13     if (select count(*) from car_rentals
14     where new.plate=plate and new.start_date <= end_date and new.end_date >= start_date
15     group by plate) >0 then
16       raise exception 'aaaaa';
17       return null;
18     end if;
19   elsif new.start_date < old.start_date or new.end_date > old.end_date then
20     if (select count(*)
21         from (select * from car_rentals
22               except
23               select *from car_rentals where old.plate=plate and old.start_date=start_date and old.end_date=end_date and old.license_nr=license_nr) as new_car_rentals
24         where new.plate=plate and new.start_date <= end_date and new.end_date >= start_date
25         group by plate) >0 then
26       raise exception 'bbbb';
27       return null;
28     end if;
29   end if;
30 end if;
31 return new;
32 end;
33 $$ language plpgsql;
34 
35 create trigger trg_car_rental
36   before insert or update on car_rentals
37   for each row execute procedure chk_car_rentals();

--如果是Insert 语句，语句应该是插入的新日期在原日期之内且车牌号相等，导致插入的时间段重复，引发异常

--如果是update 语句，当车牌号发生改变后，触发触发器的条件和上面 insert的条件相同，重复将异常　
　　　　　　　　　　　当车牌号不变，利用except除去表中旧记录的部分，select新的在所剩的表里面看是否还存在着“撞车”的情况，把异常条件写出来就好了


接下来就是测试这几条insert，update语句，应和预期结果相同

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有