Fixing SQL trigger syntax-CodePudding

I'm new here still a student really hope you can help.

I have tried million different ways to execute this trigger really don't know what I am doing wrong:

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;

I am getting a syntax error at or near "if"

Any help, tips, trick would be appreciated

CodePudding user response：

I have not sure if this is the correct syntax, but you need to save the select result in a variable to use in a IF clause. And you do it, when you use into VARIABLE_NAME in the select statment.

Something like that:

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;

CodePudding user response：

thanks to vinicius and a lot of crying for some reason this solution works:

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!