0

My aim is to execute a query such that if a row in table rental is added or updated, that change should be added in another table called history_rental.

I wrote the following function and trigger:

CREATE OR REPLACE FUNCTION process_rental_audit() RETURNS TRIGGER AS $rental_audit$
    BEGIN
        --
        -- Create a row in rental_audit to reflect the operation performed on emp,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'UPDATE') THEN
            INSERT INTO history_rental (
                rental_id, memberid, rentalrequestdate, rentalshippeddate, rentalreturneddate, dvdid, dvdtitle, genreid, genrename, ratingid, ratingname
            ) select NEW.rentalid, NEW.memberid, NEW.rentalrequestdate, NEW.rentalshippeddate,
                NEW.rentalreturneddate, dvd.dvdid, dvd.dvdtitle, dvd.genreid, genre.genrename, dvd.ratingid, rating.ratingname from
                rental inner join dvd_copy on NEW.dvd_copy_id=dvd_copy.dvd_copy_id inner join dvd on dvd_copy.dvdid=dvd.dvdid
                inner join rating on rating.ratingid=dvd.ratingid inner join genre on genre.genreid=dvd.genreid ;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO history_rental (
                rental_id, memberid, rentalrequestdate, rentalshippeddate, rentalreturneddate, dvdid, dvdtitle, genreid, genrename, ratingid, ratingname
            ) select NEW.rentalid, NEW.memberid, NEW.rentalrequestdate, NEW.rentalshippeddate,
                NEW.rentalreturneddate, dvd.dvdid, dvd.dvdtitle, dvd.genreid, genre.genrename, dvd.ratingid, rating.ratingname from
                rental inner join dvd_copy on NEW.dvd_copy_id=dvd_copy.dvd_copy_id inner join dvd on dvd_copy.dvdid=dvd.dvdid
                inner join rating on rating.ratingid=dvd.ratingid inner join genre on genre.genreid=dvd.genreid ;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$rental_audit$ LANGUAGE plpgsql;


CREATE TRIGGER rental_audit AFTER
INSERT
OR
UPDATE ON rental
FOR EACH ROW EXECUTE PROCEDURE process_rental_audit();

This is working incorrectly. It inserts multiple rows.

Before trigger:

netflix1=# select * from history_rental;
 id | rental_id | memberid | rentalrequestdate | rentalshippeddate | rentalreturneddate | dvdid |       dvdtitle        | genreid | genrename | ratingid | ratingname
----+-----------+----------+-------------------+-------------------+--------------------+-------+-----------------------+---------+-----------+----------+------------
  1 |         3 |        1 | 2019-02-02        | 2019-02-02        | 2019-02-09         |     3 | Catch Me If You Can   |       5 | Drama     |        3 | PG-13
  2 |         6 |       15 | 2019-02-12        | 2019-02-12        | 2019-02-21         |     1 | Groundhog Day         |       3 | Comedy    |        2 | PG
  3 |         2 |        1 | 2019-02-02        | 2019-02-02        | 2019-02-09         |     6 | Mystic River          |       5 | Drama     |        4 | R
  4 |        10 |       15 | 2019-02-28        |                   |                    |     7 | Never Say Never Again |       1 | Action    |        1 | G
  5 |         5 |        5 | 2019-02-15        | 2019-02-15        |                    |     5 | Pale Rider            |      10 | Western   |        4 | R
  6 |         7 |        9 | 2019-02-19        | 2019-02-19        |                    |     2 | The Terminal          |       5 | Drama     |        2 | PG
  7 |         8 |        8 | 2019-02-20        | 2019-02-21        |                    |     2 | The Terminal          |       5 | Drama     |        2 | PG
  8 |         1 |        1 | 2019-02-02        | 2019-02-02        | 2019-02-09         |     4 | The Sixth Sense       |      12 | Horror    |        4 | R
  9 |         4 |        5 | 2019-02-15        | 2019-02-15        |                    |     4 | The Sixth Sense       |      12 | Horror    |        4 | R
 10 |         9 |        1 | 2019-02-20        | 2019-02-20        |                    |     4 | The Sixth Sense       |      12 | Horror    |        4 | R
 11 |        11 |        2 | 2004-02-02        |                   |                    |     4 | The Sixth Sense       |      12 | Horror    |        4 | R
 12 |        12 |        2 | 2004-02-03        |                   |                    |     4 | The Sixth Sense       |      12 | Horror    |        4 | R

After the trigger, I test with a new dummy row:

netflix1=# insert into rental values(default, 1, date('2019-03-15'), date('2019-03-15'), date('2019-03-16'), 3);
INSERT 0 1

But with just one row's addition in rental, 13 rows ( all same ) were added:

netflix1=# select * from history_rental;
 id | rental_id | memberid | rentalrequestdate | rentalshippeddate | rentalreturneddate | dvdid |       dvdtitle        | genreid | genrename | ratingid | ratingname
----+-----------+----------+-------------------+-------------------+--------------------+-------+-----------------------+---------+-----------+----------+------------
  1 |         3 |        1 | 2019-02-02        | 2019-02-02        | 2019-02-09         |     3 | Catch Me If You Can   |       5 | Drama     |        3 | PG-13
  2 |         6 |       15 | 2019-02-12        | 2019-02-12        | 2019-02-21         |     1 | Groundhog Day         |       3 | Comedy    |        2 | PG
  3 |         2 |        1 | 2019-02-02        | 2019-02-02        | 2019-02-09         |     6 | Mystic River          |       5 | Drama     |        4 | R
  4 |        10 |       15 | 2019-02-28        |                   |                    |     7 | Never Say Never Again |       1 | Action    |        1 | G
  5 |         5 |        5 | 2019-02-15        | 2019-02-15        |                    |     5 | Pale Rider            |      10 | Western   |        4 | R
  6 |         7 |        9 | 2019-02-19        | 2019-02-19        |                    |     2 | The Terminal          |       5 | Drama     |        2 | PG
  7 |         8 |        8 | 2019-02-20        | 2019-02-21        |                    |     2 | The Terminal          |       5 | Drama     |        2 | PG
  8 |         1 |        1 | 2019-02-02        | 2019-02-02        | 2019-02-09         |     4 | The Sixth Sense       |      12 | Horror    |        4 | R
  9 |         4 |        5 | 2019-02-15        | 2019-02-15        |                    |     4 | The Sixth Sense       |      12 | Horror    |        4 | R
 10 |         9 |        1 | 2019-02-20        | 2019-02-20        |                    |     4 | The Sixth Sense       |      12 | Horror    |        4 | R
 11 |        11 |        2 | 2004-02-02        |                   |                    |     4 | The Sixth Sense       |      12 | Horror    |        4 | R
 12 |        12 |        2 | 2004-02-03        |                   |                    |     4 | The Sixth Sense       |      12 | Horror    |        4 | R
 38 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 39 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 40 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 41 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 42 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 43 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 44 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 45 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 46 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 47 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 48 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 49 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
 50 |        15 |        1 | 2019-03-15        | 2019-03-15        | 2019-03-16         |     6 | Mystic River          |       5 | Drama     |        4 | R
(25 rows)

My aim is to only get the row with id:15 above and that's it. I went through certain documentations and tutorials but in vain.

I read this answer but could not use it as I am using joins and I need the from keyword.

1 Answer 1

0

You may have a join problem with your INSERT's SELECT in trigger code :

select NEW.rentalid, NEW.memberid, NEW.rentalrequestdate, NEW.rentalshippeddate,
       NEW.rentalreturneddate, dvd.dvdid, dvd.dvdtitle, dvd.genreid, genre.genrename, dvd.ratingid, rating.ratingname 
from       rental 
inner join dvd_copy on NEW.dvd_copy_id=dvd_copy.dvd_copy_id 
inner join dvd on dvd_copy.dvdid=dvd.dvdid
inner join rating on rating.ratingid=dvd.ratingid 
inner join genre on genre.genreid=dvd.genreid ;

If your run your query as is it should return 13 rows :

select rental.rentalid, rental.memberid, rental.rentalrequestdate, rental.rentalshippeddate,
       rental.rentalreturneddate, dvd.dvdid, dvd.dvdtitle, dvd.genreid, genre.genrename, dvd.ratingid, rating.ratingname 
from       rental 
inner join dvd_copy on rental.dvd_copy_id=dvd_copy.dvd_copy_id 
inner join dvd on dvd_copy.dvdid=dvd.dvdid
inner join rating on rating.ratingid=dvd.ratingid 
inner join genre on genre.genreid=dvd.genreid 
-- where rental.dvd_copy_id = 15
;

If you run the query, does it returns 13 rows ?
If you uncomment the where part does it returns 1 row ?

I wonder if there's not a missing where clause to restrict your rental.dvd_copy_id to NEW.dvd_copy_id (just like in my SELECT example).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.