我是新來的還是學生，真的希望你能幫忙。
我已經嘗試了數百萬種不同的方式來執行這個觸發器真的不知道我做錯了什么：
create trigger T1
before insert or update on points
for each row
if (select player.tid from player
where new.pid = player.pid) not in (select game.htid from game
where new.gdate = game.gdate and new.htid = game.htid)
or (select player.tid from player
where new.pid = player.pid) not in (select game.vtid from game
where new.gdate = game.gdate and new.htid = game.htid)
then begin
raise exception 'Error: Player was not found in the game';
end;
else
begin
execute procedure trigf1();
end;
end if;
我在“if”處或附近收到語法錯誤
任何幫助，提示，技巧將不勝感激
uj5u.com熱心網友回復：
我不確定這是否是正確的語法，但您需要將選擇結果保存在一個變數中以在 IF 子句中使用。當您into VARIABLE_NAME在 select 陳述句中使用時，您就這樣做了。
類似的東西：
CREATE FUNCTION T1()
RETURNS TRIGGER AS $$
DECLARE
playerId INTEGER;
gameId INTEGER;
gameVId INTEGER;
BEGIN
END;
before insert or update on points
for each row
select player.tid
into playerId
from player
where new.pid = player.pid;
select game.htid
into gameId
from game
where new.gdate = game.gdate
and new.htid = game.htid;
select game.vtid
into gameVId
from game
where new.gdate = game.gdate and new.htid = game.htid;
if (playerId) not in (gameId) or (playerId) not in (gameVId) then
raise exception 'Error: Player was not found in the game';
end;
END;
uj5u.com熱心網友回復：
感謝 vinicius 和出于某種原因的很多哭泣這個解決方案有效：
declare
teamid int;
gamehid int;
gamevid int;
begin
select tid
into teamid
from player
where new.pid = pid;
select htid
into gamehid
from game
where new.gdate = gdate
and new.htid = htid;
select vtid
into gamevid
from game
where new.gdate = gdate and new.htid = htid;
if (teamid) not in (gamehid) and (teamid) not in (gamevid) then
begin
raise exception 'Error: Player was not found in the game';
return null;
end;
elseif (teamid) in (gamehid)
then update game
set hscore = hscore new.pscore
where htid = teamid and vtid = gamevid and gdate = new.gdate;
return null;
elseif (teamid) in (gamevid)
then update game
set vscore = vscore new.pscore
where vtid = teamid and htid = gamehid and gdate = new.gdate;
return null;
end if;
end;
$$ language plpgsql;
create trigger T1
before insert or update on points
for each row
execute procedure trigf1();```
thank you!
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/343261.html
標籤：sql PostgreSQL