關於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語句，應和預期結果相同