关于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的大套子，二三问和在一起写个触发器：

create or replace function chk_car_rentals()
  returns trigger as $$
begin
if TG_OP='INSERT' then
  if (select count(*) from car_rentals
  where new.plate=plate and new.start_date <= end_date and new.end_date >= start_date
  group by plate) >0 then
    raise exception 'aaaaa';
    return null;
  end if;
elsif TG_OP='UPDATE' then
  if new.plate <> old.plate then
    if (select count(*) from car_rentals
    where new.plate=plate and new.start_date <= end_date and new.end_date >= start_date
    group by plate) >0 then
      raise exception 'aaaaa';
      return null;
    end if;
  elsif new.start_date < old.start_date or new.end_date > old.end_date then
    if (select count(*)
        from (select * from car_rentals
              except
              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
        where new.plate=plate and new.start_date <= end_date and new.end_date >= start_date
        group by plate) >0 then
      raise exception 'bbbb';
      return null;
    end if;
  end if;
end if;
return new;
end;
$$ language plpgsql;

create trigger trg_car_rental
  before insert or update on car_rentals
  for each row execute procedure chk_car_rentals();

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

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


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