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.
Best Answer
You may have a join problem with your
INSERT'sSELECTin trigger code :If your run your query as is it should return 13 rows :
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_idtoNEW.dvd_copy_id(just like in my SELECT example).