CODEWITHSUNDEEP
postgresql
Alex
Alex

Reputation: 23

Postgresql error more than one row returned by a subquery when I used trigger

I am using trigger and function to insert values into another table.

I do have this table 1)EXHIBITION

exhid   exhname             description    strtdate       enddate   status
101     The Famous          BLAH BLAH      2013-07-15   2013-10-13  SOON
102     York Exhibition     BLAH BLAH      2013-08-07   2014-01-19  End
103     Fine ART            BLAH BLAH      2014-09-26   2015-03-21  SOON

2)Works_Exhibitions

   alphid   numberid      exhid
    SCFI    1007          101
    SCBU    1008          101
    PAHF    1002          103
    PAHE    1003          103
    PASP    1004          103

and third empty table which has same attributes as Works_Exhibitions and name it Temp_works_exhibitions

alphid  numberid      exhid

I did create this function and trigger so once I update the status of any exhibition in the Exhibition table to be 'End', I would like to copy all the works of that exhibition to be in the new table Temp_works_exhibitions and then remove it from the Works_exhibition table

CREATE OR REPLACE FUNCTION add2Temp_works_exhibitions() returns TRIGGER AS $updExhibStat$
BEGIN

IF(NEW.Status = 'End') THEN
INSERT INTO temp_works_exhibitions
select 

    Works_Exhibitions.alphID,
    Works_Exhibitions.numberID,
    Works_Exhibitions.exhID

from
    Works_Exhibitions
where
    Works_Exhibitions.exhID = (select Exhibitions.exhID from Exhibitions where  Exhibitions.Status = 'End');

delete from Works_Exhibitions where Works_Exhibitions.exhID = (select Exhibitions.exhID from Exhibitions where  Exhibitions.Status = 'End');

RETURN NEW;
END IF;
RETURN NULL;
END;

$updExhibStat$ LANGUAGE plpgsql;

--CREATE TRIGGER updExhibStat AFTER UPDATE ON Exhibitions FOR EACH ROW EXECUTE PROCEDURE add2Temp_works_exhibitions();

When I test it first time it did work but when I tried to update a new exhibition I got this ERROR 

ERROR:  more than one row returned by a subquery used as an expression
CONTEXT:  SQL statement "INSERT INTO temp_works_exhibitions
select 

    Works_Exhibitions.alphID,
    Works_Exhibitions.numberID,
    Works_Exhibitions.exhID

from
    Works_Exhibitions
where
    Works_Exhibitions.exhID = (select Exhibitions.exhID from Exhibitions where  Exhibitions.Status = 'End')"
PL/pgSQL function add2temp_works_exhibitions() line 5 at SQL statement

Any Idea how to fix this

Upvotes: 0

Views: 613

Answers (1)

Alex
Alex

Reputation: 23

I guess I did fix the problem

I did add this command at the end of the function 

UPDATE Exhibitions set Status = 'Expired' where Exhibitions.Status = 'End';

I guess it's not really a good design but I don't have another solution

Upvotes: 0

Related Questions