我的社交媒體專案有一個 postgreSQL 觸發器，一旦插入評論，觸發器就會在 commentData 表和訂閱表中插入一個新行，如下所示。
create or replace function public.initialize_comment_data_subscribe()
returns trigger as $$
begin
INSERT INTO "commentData" (comment_id, post_id, vote_count, children_count)
VALUES (new.id, new.post_id, 0, 0);
INSERT INTO subscriptions (comment_id, post_id, user_id, email)
VALUES (new.id, new.post_id, new.author_id, (SELECT email from users WHERE user_id = new.author_id));
RETURN NEW;
end;
$$ language plpgsql;
create trigger on_create_comment
after INSERT on comments
for each row execute procedure public.initialize_comment_data_subscribe();
當特定 user_id 的用戶表中的電子郵件為空時，由于以下行，整個事情都會失敗
... (SELECT email from users WHERE user_id = new.author_id));
但相反，我希望第一個插入繼續作業，如果電子郵件為空，則跳過第二個插入。
我該怎么做呢？或者最好拆分插入查詢并改為使用 2 個觸發器。
uj5u.com熱心網友回復：
改為執行 SELECT INSERT：
INSERT INTO subscriptions (comment_id, post_id, user_id, email)
SELECT new.id, new.post_id, new.author_id, email
from users WHERE user_id = new.author_id AND email is not null
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/411060.html
標籤：