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?

https://stackoverflow.com/questions/35927365/create-or-replace-trigger-postgres — Alam, Aug 08 '17 at 23:59
I removed the `OR REPLACE` from both `CREATE TRIGGER` and `CREATE FUNCTION` but I still got the error. — Shira Asulin, Aug 09 '17 at 00: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, Aug 09 '17 at 01:52

LAS · Answer 1 · 2017-08-09T03:09:14.933

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 09 '17 at 03:09

answered Aug 09 '17 at 00:06

LAS

829
5
7

I've changed to function but still getting the error. – Shira Asulin Aug 09 '17 at 00:12
The only allowed trigger syntax is `EXECUTE PROCEDURE` - and there is no distinction between functions and procedures in PostgreSQL. – filiprem Aug 09 '17 at 01:54
@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. – LAS Aug 09 '17 at 03:15

Trigger error (create trigger)

1 Answers1