How to create a MySQL trigger for updating a sum of a table with data from a field in another table after an on Insert or Update

Question

I have the following code:

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

on SQL Server and i like to create the same trigger on a MySQL Database, i can not seem to declare the variables right or to find the right fields on NEW or OLD table

My attempt is:

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

You post is unclear, are you trying to run this on SQL Server or MySQL? They are completely different products. — Thom A, Dec 05 '18 at 11:57
I try to have the same results i get from the above code which runs well on SQL Server on a MySQL database — Tasos Karatzoglou, Dec 05 '18 at 12:02
it's not that unclear @Larnu his current code is SQL Server (MSSQL).. MySQL does not support `AFTER UPDATE, INSERT` in MySQL you need to made separated UPDATE and INSERT triggers instead. — Raymond Nijland, Dec 05 '18 at 12:03
i tried to separate them but i get error message about having not right syntax — Tasos Karatzoglou, Dec 05 '18 at 12:04
"i can not seem to declare the variables right or to find the right fields on NEW or OLD table" "i tried to separate them but i get error message about having not right syntax " Show us your MySQL attempt code and the error message? — Raymond Nijland, Dec 05 '18 at 12:05
You are missing delimiters on your statements... MySQL requires all statements to end with a delimiter — Nick, Dec 05 '18 at 12:15
Have you considered using a VIEW and getting rid of your trigger entirely? `CREATE VIEW PoliticalParties SELECT PoliticalPartyId, SUM(Votes) Total FROM Voting GROUP BY PoliticalPartyId;` . — O. Jones, Dec 05 '18 at 12:19
Your start point (sql server trigger) is poor. There are a number of things wrong with you mysql attempt - you need to set delimiters see https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html, User defined varables are set not declared https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql , You Set or Select..into variables, Every statement must be terminates (usually with ;) OLD. And NEW. values usage can be found here https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html. — P.Salmon, Dec 05 '18 at 12:54
But I suspect much of your code is redundant and you need a multi-table update https://dev.mysql.com/doc/refman/8.0/en/update.html. If you would like to add sample data and expected ouput to the question as text that would help together with some update statements. — P.Salmon, Dec 05 '18 at 12:56

shushu304 · Accepted Answer · 2018-12-06T12:59:02.747

1

1) you don't need to select from NEW, you can access all fields in NEW like that: NEW.field_name

2) you can SELECT INTO the DECLARE variable

try this:

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

edited Dec 06 '18 at 12:59

answered Dec 05 '18 at 12:59

shushu304

1,506
1
7
15

Getting the following error: [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 'SELECT SUM(Votes) FROM Voting WHERE PoliticalPartyID = NEW.PoliticalPartyI' at line 9 – Tasos Karatzoglou Dec 05 '18 at 14:25
Fixed the above code we need to add a ';' on `DECLARE SUM_VOTES INT;` and before END `UPDATE PoliticalParties SET Total = SUM_VOTES WHERE PoliticalPartyID = NEW.VOTING_PARTY_ID` must be: `UPDATE PoliticalParties SET Total = SUM_VOTES WHERE PoliticalPartyID = PoliticalPartyID` thank you very much for the solution – Tasos Karatzoglou Dec 05 '18 at 14:37
thanks for your reply, i updated the code as you said. and thanks for the vote :) – shushu304 Dec 06 '18 at 12:59

How to create a MySQL trigger for updating a sum of a table with data from a field in another table after an on Insert or Update

1 Answers1