sql server—如何创建mysql触发器，以便在插入或更新后使用另一个表中的字段中的数据更新表的总和

uqdfh47h 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(239)

我有以下代码：

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

在sql 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

mysql sql-server

来源：https://stackoverflow.com/questions/53631769/how-to-create-a-mysql-trigger-for-updating-a-sum-of-a-table-with-data-from-a-fie

1条答案

按热度按时间

ekqde3dh1#

1）您不需要从new中选择，您可以访问new中的所有字段，如下所示：new.field\u name
2）您可以在declare变量中选择
试试这个：

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

赞(0）回复(0）举报 2021-06-16

我来回答

sql server—如何创建mysql触发器，以便在插入或更新后使用另一个表中的字段中的数据更新表的总和

1条答案

相关问题

热门标签

最新问答