如何修复PostgreSQL中的这个触发器错误

Question

我的代码出现语法错误，无法理解原因我是不是错过了什么？此外，我读到这篇文章时没有得到答案当我尝试创建触发器时，PostgreSQL中的语法错误

CREATE TRIGGER MyExampleName AFTER INSERT ON baskets
FOR EACH ROW BEGIN
UPDATE customers 
SET customers.credit=customers.credit - NEW.amount 
WHERE customers.id = NEW.customer_id;
END;

并且也这样尝试过：

CREATE TRIGGER MyExampleName AFTER INSERT ON baskets
FOR EACH ROW AS $$ BEGIN
UPDATE customers 
SET customers.credit=customers.credit - NEW.amount 
WHERE customers.id = NEW.customer_id;
END;
$$ LANGUAGE plpgsql;

错误：

ERROR:  syntax error at or near "BEGIN"
LINE 2: FOR EACH ROW BEGIN
^
SQL state: 42601
Character: 67

Answer 1

我想说，关于你的问题的第一条评论几乎涵盖了所有内容。不能将触发器代码放在触发器主体中，必须首先创建一个单独的函数，并将函数调用包含在触发器主体内。

这个例子直接来自Postgres文档：

-- 1. Create the function that does what you need
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Who works for us when they must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
-- 2. Create the trigger with the 'EXECUTE FUNCTION function_name' part
--    replacing the actual function name from step 1.
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE FUNCTION emp_stamp();