Trigger error (create trigger)

Question

I've got the error :

ERROR: syntax error at or near "trigger" LINE 18: CREATE OR REPLACE trigger T1
ERROR: syntax error at or near "trigger" SQL state: 42601 Character: 391

for this code:

CREATE OR REPLACE FUNCTION trigf1() RETURNS trigger AS
$$
BEGIN
IF EXISTS ( SELECT*
        FROM Receipt JOIN Sells ON Receipt.license = Sells.license
        WHERE iname = NEW.iname AND cid = NEW.cid AND rno = NEW.rno
    )THEN
    RETURN NEW; 
ELSE
    RAISE NOTICE 'This cafe does not sell the item: %s',new.iname;
    RETURN NULL;
END IF;
END;
$$
LANGUAGE 'plpgsql';
CREATE OR REPLACE TRIGGER T1  
BEFORE INSERT OR UPDATE  
ON Buys
FOR EACH ROW
EXECUTE PROCEDURE trigf1();

Any idea what is the problem?

I removed the OR REPLACE from both CREATE TRIGGER and CREATE FUNCTION but I still got the error. — Shira Asulin
– Shira Asulin, Commented Aug 9, 2017 at 0:07
@ShiraAsulin if you stilll get error, please paste new error message - syntax in 2nd command is invalid, after removing 'OR REPLACE' part it should work — filiprem
– filiprem, Commented Aug 9, 2017 at 1:52

LAS · Accepted Answer · 2017-08-09 03:09:14Z

0

I changed the bottom call back to a procedure based on comments. I noticed that there was no space between select and *.

CREATE OR REPLACE FUNCTION trigf1() RETURNS trigger AS
$$
BEGIN
IF EXISTS ( SELECT * -- added a space after the select
        FROM Receipt JOIN Sells ON Receipt.license = Sells.license
        WHERE iname = NEW.iname AND cid = NEW.cid AND rno = NEW.rno
    )THEN
    RETURN NEW; 
ELSE
    RAISE NOTICE 'This cafe does not sell the item: %s',new.iname;
    RETURN NULL;
END IF;
END;
$$
LANGUAGE 'plpgsql';
CREATE OR REPLACE TRIGGER T1  
BEFORE INSERT OR UPDATE  
ON Buys
FOR EACH ROW
EXECUTE PROCEDURE trigf1();

edited Aug 9, 2017 at 3:09

answered Aug 9, 2017 at 0:06

LAS

8195 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shira Asulin Over a year ago

I've changed to function but still getting the error.

filiprem Over a year ago

The only allowed trigger syntax is EXECUTE PROCEDURE - and there is no distinction between functions and procedures in PostgreSQL.

LAS Over a year ago

@ShiraAsulin If tis doesn't work try running the begin ... end; statements outside of the trigger. Just try running them in a console so you can make sure the syntax of the trigger body is correct.

Collectives™ on Stack Overflow

Trigger error (create trigger)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related