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
@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

LAS LAS · Accepted Answer · 2017-08-09T00:06:28

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();

Trigger error (create trigger)

1 Answers