我該怎麼做這個觸發器？

我是 sql 世界的新手 :) 現在我正在嘗試創建我的第一個 mysql 觸發器，該觸發器將更新一行或插入一個新行。你能幫我嗎？

這是程式碼：

DELIMITER //
CREATE TRIGGER trigger_vote_question_after_insert AFTER INSERT ON vote_question
FOR EACH ROW
BEGIN
IF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) > 0) AND NEW.vote_question_type = '1')
THEN UPDATE vote_total_question
SET question_total_plus = question_total_plus + 1
SET question_total_mixed = question total_mixed + 1
WHERE question_id = NEW.question_id
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) > 0) AND NEW.vote_question_type = '-1')
THEN UPDATE vote_total_question
SET question_total_plus = question_total_plus - 1
SET question_total_mixed = question total_mixed + 1
WHERE question_id = NEW.question_id
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) = NULL) AND NEW.vote_question_type = '1')
THEN INSERT INTO vote_total_question (question_id, question_total_plus, question_total_minus, question_total_mixed) VALUES (NEW.question_id, 1, 0, 1)
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) = NULL) AND NEW.vote_question_type = '-1')
THEN INSERT INTO vote_total_question (question_id, question_total_plus, question_total_minus, question_total_mixed) VALUES (NEW.question_id, 0, 1, 1)
ENDIF
END//

我也可以用 postgresql 試試這個觸發器我應該改變什麼嗎？（如果我猜，ELSEIF 到 ELSE）

我真的建議不要有一個你必須維護的單獨的“總計”表。這樣做會有很多缺點：

• 在高容量下，您可能會獲得事務鎖
• 您的總數可能與源表“不同步”。如果/當這種情況發生時，您必須在嘗試使總數正確時中斷或阻止插入新選票。

無論如何，我相信您可以通過簡單的程式碼獲得高性能的實時投票總數，而無需單獨儲存任何總數資訊，因此不需要觸發器。您也不必擔心任何 INSERT 與 UPDATE 邏輯。

確保您的vote_question表在列上有一個索引（非唯一）question_id。這將確保實時性能。

這是一個總計查詢（所有問題，匯總）：

SELECT question_id
  , SUM((vote_question_type)='1') question_total_plus
  , SUM((vote_question_type)='-1') question_total_minus
  , count(*) question_total_mixed
FROM vote_question
GROUP BY question_id;

如果您不想將該程式碼放在應用程序端，那麼沒問題，只需將其創建為數據庫視圖：

CREATE VIEW vote_question_totals
 AS
SELECT question_id
  , SUM((vote_question_type)='1') question_total_plus
  , SUM((vote_question_type)='-1') question_total_minus
  , count(*) question_total_mixed
FROM vote_question
GROUP BY question_id;

然後從您的應用程序/客戶端，您可以像引用表格一樣引用該視圖（並提供一個question_id）。

SELECT question_id, question_total_plus, question_total_minus, question_total_mixed
 FROM vote_question_totals
 WHERE question_id = '???' ;

引用自：https://dba.stackexchange.com/questions/67962