Postgresql错误当我使用触发器时，子查询返回多个行

问题描述：

我正在使用触发器和函数将值插入到另一个表中。Postgresql错误当我使用触发器时，子查询返回多个行

我有此表 1）展

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

和第三具有它Temp_works_exhibitions

alphid numberid  exhid

相同的属性Works_Exhibitions和名字空表

我确实创建了此功能并触发所以一旦我更新展览中任何展览的状态为“结束”，我想将该展览的所有作品复制到新表格Temp_works_exhibitions中，然后从Works_exhibition表中删除它

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();

当我测试它第一次做工作，但是当我试图更新一个新的展览我得到这个错误

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

任何想法如何解决这个问题

琐碎的方法是添加'LIMIT 1'但更大的问题是：您为什么认为子查询会返回一个答案？和/或如果没有完全一个答案，你想要做什么。 – 2014-10-11 23:05:20

简而言之：使用'EXISTS（...）' – wildplasser 2014-10-11 23:20:15

我确实使用了EXISTS（...），但我得到的函数exisit（boolean）不存在 – Alex 2014-10-12 00:29:43

答

我想我没有解决这个问题

我没有在函数的末尾添加此命令

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

我想这不是一个真正的好设计，但我没有其他的解决方案

Postgresql错误当我使用触发器时，子查询返回多个行

相关推荐