触发和功能故障

Question

我目前正在经历学习函数和触发器的成长过程。我正试着从我正在读的一本书中做一道题，但我不知道如何做某些部分。

使用此表

create table movies (
    id      integer primary key,
    title   varchar(255) not null,
    year    integer
);
insert into movies values (1, 'The Croods', 2013);
insert into movies values (2, 'Now You See Me', 2013);
insert into movies values (3, 'Argo', 2012);
insert into movies values (4, 'Jurassic World', 2015);
create table discs (
    id          integer primary key,
    movie_id    integer not null references movies(id),
    type_id     integer references disc_types(id),
    price       decimal(10,2),
    available   boolean
);
insert into discs values (1, 1, 1, 1.59, 't');
insert into discs values (2, 1, 1, 1.59, 'f');
insert into discs values (3, 1, 2, 2.99, 'f');
insert into discs values (4, 2, 1, 1.29, 't');
insert into discs values (5, 2, 1, 1.29, 't');
insert into discs values (6, 2, 2, 2.99, 't');
insert into discs values (7, 3, 2, 2.59, 't');
insert into discs values (8, 3, 2, 2.59, 't');
create table customers (
    id      integer primary key,
    name    varchar(255),
    email   varchar(255)
);
insert into customers values (1, 'John', '[e-mail]');
insert into customers values (2, 'Jane', '[e-mail]');
create table rentals (
    id              integer primary key,
    customer_id     integer not null references customers(id),
    disc_id         integer not null references discs(id),
    date_rented     date,
    date_returned   date
);
insert into rentals values (1, 1, 7, '2013-10-01', '2013-10-03');
insert into rentals values (2, 2, 5, '2013-10-05', '2013-10-06');
insert into rentals values (3, 2, 2, '2013-11-02', null);
insert into rentals values (4, 2, 3, '2013-11-02', null);
create table ratings (
    customer_id integer not null references customers(id),
    movie_id    integer not null references movies(id),
    rating      integer,
  primary key (customer_id, movie_id)
);
insert into ratings values (1, 1, 1);
insert into ratings values (1, 2, 4);
insert into ratings values (1, 3, 5);
insert into ratings values (2, 1, 4);

我的逻辑是，我会插入或更新评分表的新值，并使用这些值与出租表中的值进行比较，看看客户是否已经租了那部电影，如果他们已经租了，那么他们可以输入评分。但我不能在这个lol中转移这种逻辑。除非有更简单的方法。

Answer 1

函数内部的循环使事情变得有点复杂，让我们看看是否可以摆脱它。您的评分表引用了客户和电影，所以我们需要加入。

SELECT COUNT(*) INTO rented FROM rentals WHERE disc_id IN
  (SELECT id from discs INNER JOIN 
   rentals ON disc_id = discs.id where movie_id = new.movie_id)
  AND customer_id = new.customer_id 

这应该会使存储过程的逻辑更加简单。我现在让你们完成它，因为这毕竟是一个学习练习。

您需要这种联接，因为它比循环更高效、更简单。分级表引用了movie_id，但出租表只有disc_id，因此要了解用户是否租用了特定的电影，您需要通过disc表加入。

您需要更改返回值。参考：http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html

BEFORE触发的行级触发器可以返回null以向触发器发出信号管理器跳过该行的其余操作（即。，后续触发器不会被激发，而INSERT/UPDATE/DELETE会对于此行不发生）。如果返回非null值，则操作继续使用该行值

还要注意，您没有在触发器函数中执行INSERT操作。您只需返回一个非null值即可继续插入。

Answer 2

这是EXISTS()版本。（顺便说一句：电影的定义不见了）

CREATE OR REPLACE FUNCTION rate_only_rented()
RETURNS TRIGGER AS $func$
BEGIN
IF ( NOT EXISTS (
        SELECT *
        FROM rentals r
        JOIN discs d ON r.disc_id = d.id
        WHERE d.movie_id = NEW.movie_id
        AND r.customer_id = NEW.customer_id
        ) )  THEN
        RAISE EXCEPTION 'you(%) have not rented this movie(%) before'
                             , NEW.customer_id             ,NEW.movie_id;
        RETURN NULL;
ELSE
        RETURN NEW;
END IF;
END;
$func$ language plpgsql;

---

触发因素：

CREATE TRIGGER rate_only_rented
AFTER INSERT OR UPDATE
ON ratings
FOR EACH ROW
EXECUTE PROCEDURE  rate_only_rented()
        ;