CODEWITHSUNDEEP
sqlpostgresqltriggerspsql
Sam
Sam

Reputation: 2331

Creating Trigger function is psql "syntax error at or near "CREATE""

Here is my postgreSQL code

CREATE FUNCTION connectedExhibitionFunction()
RETURNS trigger AS
$$
BEGIN
        IF (SELECT COUNT("exName") FROM Exhibitions WHERE NEW."exName" = "exName") > 0 THEN
                IF (SELECT Count(doorLoc1) FROM Doors, ExhibitionLocations WHERE (dorLoc1=NEW.elLocation AND dorLoc2=elLocations) OR (dorLoc2=NEW.elLocation AND dorLoc1=elLocations) > 0 THEN
                        RAISE EXCEPTION 'You can't have the exhibition there, the same exhibition is in an unconnected room';
                END IF;
        END IF;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER connectedExhibitionTrigger
        BEFORE INSERT
        ON ExhibitionsLocations
        EXECUTE PROCEDURE connectedExhibitionFunction();

And this is the error I'm getting

psql:file.txt:62: ERROR:  syntax error at or near "CREATE"
LINE 8: CREATE FUNCTION connectedExhibitionFunction()
        ^
psql:file.txt:67: ERROR:  current transaction is aborted, commands ignored until end of transaction block

I can't seem to figure out the error, can anybody spot anything here?

Upvotes: 2

Views: 1700

Answers (2)

Patrick
Patrick

Reputation: 32199

You should not use the count() function to test for the presence of data, use the PERFORM command instead. You should also RETURN NEW or RETURN NULL from the trigger function or your insert will fail by definition. With some other improvements you will end up with this:

CREATE FUNCTION connectedExhibitionFunction() RETURNS trigger AS $$
BEGIN
    PERFORM * FROM Exhibitions WHERE "exName" = NEW."exName";
    IF FOUND THEN
        PERFORM * FROM Doors, ExhibitionLocations
        WHERE (dorLoc1 = NEW.elLocation AND dorLoc2 = elLocations)
           OR (dorLoc2 = NEW.elLocation AND dorLoc1 = elLocations);
        IF FOUND THEN
            RAISE EXCEPTION 'You can''t have the exhibition there, the same exhibition is in an unconnected room';
            RETURN NULL; -- Make the INSERT fail
        END IF;
    END IF;
    RETURN NEW; -- Make the INSERT succeed
END;
$$ LANGUAGE plpgsql;

Upvotes: 2

fable
fable

Reputation: 161

I guess you missed Select ")" and raise exception clause couldnt be "can't" And you can use just END instead of END IF. As far as I know your problem is these.

Try this please.

 IF (SELECT Count(doorLoc1) FROM Doors, ExhibitionLocations 
  WHERE (dorLoc1=NEW.elLocation AND dorLoc2=elLocations) OR 
  (dorLoc2=NEW.elLocation AND dorLoc1=elLocations)) > 0 THEN
  RAISE EXCEPTION 'You cant have the exhibition there, the same exhibition is in an unconnected room';
 END

Upvotes: 3

Related Questions