英文:
Trying to create simple trigger on Supabase/PosgreSQL
问题
非常非常新手，并且在使用FlutterFlow创建应用程序时也很陌生PGSQL。我正在尝试创建一个触发器函数，当向scorecard表添加新行时，它将使用另一个名为courses的表的子查询来更新列holes。
我可能完全做错了，但是任何想法都将不胜感激，我创建的函数如下：
create or replace function holes2()
returns trigger
language plpgsql
as $$
begin
Update scorecards
SET holes =
(Select holes from courses
Where courses.name = new.course_name);
return new;
end;
$$;
我创建的触发器如下：
CREATE TRIGGER scorecards2
AFTER INSERT ON scorecards
FOR EACH ROW EXECUTE PROCEDURE holes2();
目前它在supabase表中创建了行，但没有触发函数来更新holes字段。我尝试过在更新之前和之后尝试过这个，但似乎无法使其工作。
如我上面所说，任何帮助都将不胜感激！
英文:
Very very new here and to PGSQL creating an app using flutterflow. I am trying to create a trigger function that will update a column holes when a new row is added to the scorecard table using a subquery of another table called courses.
I could be doing this completely wrong but any ideas would be appreciated, the function I created is as below:
create or replace function holes2()
returns trigger
language plpgsql
as $$
begin
Update scorecards
SET holes=
(Select holes from courses
Where courses.name=scorecards.course_name);
return new;
end;
$$;
The trigger I created to call this is this:
CREATE TRIGGER scorecards2
AFTER UPDATE ON scorecards
FOR EACH ROW EXECUTE PROCEDURE holes2();
At the moment it is creating the row in the supabase table but it is not triggering the function to update the holes field. I have tried this as a before and after update but I cannot seem to get this to work.
As I said above, any help would be really appreciated!
答案1
得分: 0
首先，你的触发器应该是AFTER INSERT触发器，因为你希望在插入新行后更新列。
创建一个触发器函数，当向scorecard表中添加新行时，它将更新一个名为holes的列。
还要使用NEW关键字来引用新行：
CREATE OR REPLACE FUNCTION holes2()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE scorecards
SET holes = (
SELECT holes
FROM courses
WHERE courses.name = NEW.course_name
);
RETURN NEW;
END;
$$;
现在，创建一个触发器：
CREATE TRIGGER scorecards2
AFTER INSERT ON scorecards
FOR EACH ROW
EXECUTE PROCEDURE holes2();
更多信息请参考这里。
我没有进行测试，这是基于我之前的知识。
英文:
First your trigger should be AFTER INSERT trigger, because you want to update column after new row is inserted.
> Create a trigger function that will update a column holes when a new row is added to the scorecard table
Also, use NEW keyword for referring to the new row:
CREATE OR REPLACE FUNCTION holes2()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE scorecards
SET holes = (
SELECT holes
FROM courses
WHERE courses.name = NEW.course_name
);
RETURN NEW;
END;
$$;
Now, create a trigger.
CREATE TRIGGER scorecards2
AFTER INSERT ON scorecards
FOR EACH ROW
EXECUTE PROCEDURE holes2();
More here.
I have not tested it, its based on my previous knowledge.
答案2
得分: -1
作为半个答案，它概述了我认为你想要实现的内容:
create or replace function holes2()
returns trigger
language plpgsql
as $$
begin
NEW.holes = (Select holes from courses
Where courses.name=NEW.course_name LIMIT 1);
return new;
end;
$$;
--UPDATE to answer
CREATE TRIGGER scorecards2
BEFORE INSERT ON scorecards
FOR EACH ROW EXECUTE PROCEDURE holes2();
请注意查询中的 `LIMIT 1`。您只能使用一个值。因此，要么使用 `LIMIT`，要么构建一个你知道只返回一个值的查询。
英文:
A semi-answer, as it outlines what I think you want to achieve.:
create or replace function holes2()
returns trigger
language plpgsql
as $$
begin
NEW.holes = (Select holes from courses
Where courses.name=NEW.course_name LIMIT 1);
return new;
end;
$$;
--UPDATE to answer
CREATE TRIGGER scorecards2
BEFORE INSERT ON scorecards
FOR EACH ROW EXECUTE PROCEDURE holes2();
Note the LIMIT 1 in the query. You can only use one value. So either use a LIMIT or construct a query you know only returns one value.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论