如何创建MySQL触发器，以便在on插入或更新后用另一个表中字段中的数据更新表和

Question

我有以下代码：

CREATE TRIGGER INSERT_TO_VOTE_UPDATE_SUM_VOTES ON Voting
AFTER UPDATE, INSERT
AS
BEGIN
DECLARE @VOTING_PARTY_ID INT, @SUM_VOTES INT

SELECT @VOTING_PARTY_ID = PoliticalPartyID FROM INSERTED

SELECT @SUM_VOTES = SUM(Votes) FROM Voting WHERE PoliticalPartyID = 
@VOTING_PARTY_ID

UPDATE PoliticalParties SET Total=@SUM_VOTES WHERE PoliticalPartyID = 
@VOTING_PARTY_ID
END

在Server上和我喜欢在MySQL数据库上创建相同的触发器，我似乎无法正确声明变量或在新表或旧表上找到正确的字段

我的尝试是：

CREATE TRIGGER INSERT_TO_VOTE_UPDATE_SUM_VOTES
    AFTER INSERT
    ON Voting
    FOR EACH ROW

BEGIN

DECLARE VOTING_PARTY_ID INT, @SUM_VOTES INT

SELECT @VOTING_PARTY_ID = PoliticalPartyID FROM NEW

SELECT @SUM_VOTES = SUM(Votes) FROM Voting WHERE PoliticalPartyID = 
@VOTING_PARTY_ID

UPDATE PoliticalParties SET Total=@SUM_VOTES WHERE PoliticalPartyID = 
@VOTING_PARTY_ID
END

[42000][1064] You have an error in your SQL syntax; check the manual that 
corresponds to your MariaDB server version for the right syntax to use near 
' @SUM_VOTES INT  SELECT @VOTING_PARTY_ID = PoliticalPartyID FROM NEW  
SELECT' at line 8

Answer 1

1)您不需要从NEW中选择，您可以访问NEW中的所有字段，如: NEW.field_name

2)可以在声明变量中进行选择

试试这个：

CREATE TRIGGER INSERT_TO_VOTE_UPDATE_SUM_VOTES
    AFTER INSERT
    ON Voting
    FOR EACH ROW
BEGIN

    DECLARE SUM_VOTES INT;

    SELECT SUM(Votes) FROM Voting 
    WHERE PoliticalPartyID = NEW.PoliticalPartyID 
    INTO SUM_VOTES;

    UPDATE PoliticalParties SET Total = SUM_VOTES WHERE PoliticalPartyID = 
    NEW.PoliticalPartyID
END