MySQL Trigger to Increment / Decrement Value in Other Table
I have two separate tables keeping track of teams in a league and games that the teams play. I have a field in the table 'team' for the wins for each team and am trying to create a trigger in MySQL to automatically update this field whenever a game is inserted/updated with a winning team, and conversely decrement it whenever a game is updated/deleted. These are my tables:
CREATE TABLE team (
teamName varchar(32) NOT NULL,
teamManager varchar(32) NOT NULL,
teamPoints decimal(6,2) DEFAULT 0,
teamWins tinyint(255) unsigned DEFAULT 0,
leagueID tinyint(255) unsigned NOT NULL,
FOREIGN KEY (leagueID) REFERENCES league (leagueID),
PRIMARY KEY (teamName, leagueID));
CREATE TABLE game (
gameID int NOT NULL AUTO_INCREMENT,
gameDate date NOT NULL,
winningTeam varchar(32) NOT NULL,
losingTeam varchar(32) DEFAULT NULL,
leagueID tinyint(255) unsigned NOT NULL,
FOREIGN KEY (leagueID) REFERENCES league (leagueID),
FOREIGN KEY (winningTeam) REFERENCES team (teamName),
FOREIGN KEY (losingTeam) REFERENCES team (teamName),
PRIMARY KEY (gameID));
and my attempted trigger for adding:
CREATE TRIGGER addWin
AFTER INSERT, UPDATE ON game
AS
BEGIN
UPDATE team
SET team.teamWins = team.teamWins + 1
WHERE team.teamName IN (SELECT inserted.winningTeam FROM inserted)
END;
mysql sql triggers increment create-table
edited Nov 15 '18 at 11:04
Brian Tompsett - 汤莱恩
4,1831337101
asked Nov 15 '18 at 0:35
user3080886user3080886
11
add a comment |
I have two separate tables keeping track of teams in a league and games that the teams play. I have a field in the table 'team' for the wins for each team and am trying to create a trigger in MySQL to automatically update this field whenever a game is inserted/updated with a winning team, and conversely decrement it whenever a game is updated/deleted. These are my tables:
CREATE TABLE team (
teamName varchar(32) NOT NULL,
teamManager varchar(32) NOT NULL,
teamPoints decimal(6,2) DEFAULT 0,
teamWins tinyint(255) unsigned DEFAULT 0,
leagueID tinyint(255) unsigned NOT NULL,
FOREIGN KEY (leagueID) REFERENCES league (leagueID),
PRIMARY KEY (teamName, leagueID));
CREATE TABLE game (
gameID int NOT NULL AUTO_INCREMENT,
gameDate date NOT NULL,
winningTeam varchar(32) NOT NULL,
losingTeam varchar(32) DEFAULT NULL,
leagueID tinyint(255) unsigned NOT NULL,
FOREIGN KEY (leagueID) REFERENCES league (leagueID),
FOREIGN KEY (winningTeam) REFERENCES team (teamName),
FOREIGN KEY (losingTeam) REFERENCES team (teamName),
PRIMARY KEY (gameID));
and my attempted trigger for adding:
CREATE TRIGGER addWin
AFTER INSERT, UPDATE ON game
AS
BEGIN
UPDATE team
SET team.teamWins = team.teamWins + 1
WHERE team.teamName IN (SELECT inserted.winningTeam FROM inserted)
END;
mysql sql triggers increment create-table
edited Nov 15 '18 at 11:04
Brian Tompsett - 汤莱恩
4,1831337101
asked Nov 15 '18 at 0:35
user3080886user3080886
11
add a comment |
I have two separate tables keeping track of teams in a league and games that the teams play. I have a field in the table 'team' for the wins for each team and am trying to create a trigger in MySQL to automatically update this field whenever a game is inserted/updated with a winning team, and conversely decrement it whenever a game is updated/deleted. These are my tables:
CREATE TABLE team (
teamName varchar(32) NOT NULL,
teamManager varchar(32) NOT NULL,
teamPoints decimal(6,2) DEFAULT 0,
teamWins tinyint(255) unsigned DEFAULT 0,
leagueID tinyint(255) unsigned NOT NULL,
FOREIGN KEY (leagueID) REFERENCES league (leagueID),
PRIMARY KEY (teamName, leagueID));
CREATE TABLE game (
gameID int NOT NULL AUTO_INCREMENT,
gameDate date NOT NULL,
winningTeam varchar(32) NOT NULL,
losingTeam varchar(32) DEFAULT NULL,
leagueID tinyint(255) unsigned NOT NULL,
FOREIGN KEY (leagueID) REFERENCES league (leagueID),
FOREIGN KEY (winningTeam) REFERENCES team (teamName),
FOREIGN KEY (losingTeam) REFERENCES team (teamName),
PRIMARY KEY (gameID));
and my attempted trigger for adding:
CREATE TRIGGER addWin
AFTER INSERT, UPDATE ON game
AS
BEGIN
UPDATE team
SET team.teamWins = team.teamWins + 1
WHERE team.teamName IN (SELECT inserted.winningTeam FROM inserted)
END;
mysql sql triggers increment create-table
edited Nov 15 '18 at 11:04
Brian Tompsett - 汤莱恩
4,1831337101
asked Nov 15 '18 at 0:35
user3080886user3080886
11
I have two separate tables keeping track of teams in a league and games that the teams play. I have a field in the table 'team' for the wins for each team and am trying to create a trigger in MySQL to automatically update this field whenever a game is inserted/updated with a winning team, and conversely decrement it whenever a game is updated/deleted. These are my tables:
CREATE TABLE team (
teamName varchar(32) NOT NULL,
teamManager varchar(32) NOT NULL,
teamPoints decimal(6,2) DEFAULT 0,
teamWins tinyint(255) unsigned DEFAULT 0,
leagueID tinyint(255) unsigned NOT NULL,
FOREIGN KEY (leagueID) REFERENCES league (leagueID),
PRIMARY KEY (teamName, leagueID));
CREATE TABLE game (
gameID int NOT NULL AUTO_INCREMENT,
gameDate date NOT NULL,
winningTeam varchar(32) NOT NULL,
losingTeam varchar(32) DEFAULT NULL,
leagueID tinyint(255) unsigned NOT NULL,
FOREIGN KEY (leagueID) REFERENCES league (leagueID),
FOREIGN KEY (winningTeam) REFERENCES team (teamName),
FOREIGN KEY (losingTeam) REFERENCES team (teamName),
PRIMARY KEY (gameID));
and my attempted trigger for adding:
CREATE TRIGGER addWin
AFTER INSERT, UPDATE ON game
AS
BEGIN
UPDATE team
SET team.teamWins = team.teamWins + 1
WHERE team.teamName IN (SELECT inserted.winningTeam FROM inserted)
END;
mysql sql triggers increment create-table
mysql sql triggers increment create-table
edited Nov 15 '18 at 11:04
Brian Tompsett - 汤莱恩
4,1831337101
asked Nov 15 '18 at 0:35
user3080886user3080886
11
edited Nov 15 '18 at 11:04
Brian Tompsett - 汤莱恩
4,1831337101
asked Nov 15 '18 at 0:35
user3080886user3080886
11
edited Nov 15 '18 at 11:04
Brian Tompsett - 汤莱恩
4,1831337101
edited Nov 15 '18 at 11:04
Brian Tompsett - 汤莱恩
4,1831337101
edited Nov 15 '18 at 11:04
Brian Tompsett - 汤莱恩
4,1831337101
4,1831337101
asked Nov 15 '18 at 0:35
user3080886user3080886
11
asked Nov 15 '18 at 0:35
user3080886user3080886
11
asked Nov 15 '18 at 0:35
user3080886user3080886
11
11
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Your trigger looks like a SQL Server trigger. In MySQL, this would look more like:
DELIMITER $$
CREATE TRIGGER addWin AFTER INSERT ON game
AS
BEGIN
UPDATE team
SET teamWins = teamWins + 1
WHERE teamName = new.winningTeam;
END;$$
DELIMITER ;
An update trigger would be more complicated, because you have to undo the previous increment. I'll leave that to you.
answered Nov 15 '18 at 0:41
Gordon LinoffGordon Linoff
760k35294399
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53310832%2fmysql-trigger-to-increment-decrement-value-in-other-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Your trigger looks like a SQL Server trigger. In MySQL, this would look more like:
DELIMITER $$
CREATE TRIGGER addWin AFTER INSERT ON game
AS
BEGIN
UPDATE team
SET teamWins = teamWins + 1
WHERE teamName = new.winningTeam;
END;$$
DELIMITER ;
An update trigger would be more complicated, because you have to undo the previous increment. I'll leave that to you.
answered Nov 15 '18 at 0:41
Gordon LinoffGordon Linoff
760k35294399
add a comment |
Your trigger looks like a SQL Server trigger. In MySQL, this would look more like:
DELIMITER $$
CREATE TRIGGER addWin AFTER INSERT ON game
AS
BEGIN
UPDATE team
SET teamWins = teamWins + 1
WHERE teamName = new.winningTeam;
END;$$
DELIMITER ;
An update trigger would be more complicated, because you have to undo the previous increment. I'll leave that to you.
answered Nov 15 '18 at 0:41
Gordon LinoffGordon Linoff
760k35294399
add a comment |
Your trigger looks like a SQL Server trigger. In MySQL, this would look more like:
DELIMITER $$
CREATE TRIGGER addWin AFTER INSERT ON game
AS
BEGIN
UPDATE team
SET teamWins = teamWins + 1
WHERE teamName = new.winningTeam;
END;$$
DELIMITER ;
An update trigger would be more complicated, because you have to undo the previous increment. I'll leave that to you.
answered Nov 15 '18 at 0:41
Gordon LinoffGordon Linoff
760k35294399
Your trigger looks like a SQL Server trigger. In MySQL, this would look more like:
DELIMITER $$
CREATE TRIGGER addWin AFTER INSERT ON game
AS
BEGIN
UPDATE team
SET teamWins = teamWins + 1
WHERE teamName = new.winningTeam;
END;$$
DELIMITER ;
An update trigger would be more complicated, because you have to undo the previous increment. I'll leave that to you.
answered Nov 15 '18 at 0:41
Gordon LinoffGordon Linoff
760k35294399
answered Nov 15 '18 at 0:41
Gordon LinoffGordon Linoff
760k35294399
answered Nov 15 '18 at 0:41
Gordon LinoffGordon Linoff
760k35294399
answered Nov 15 '18 at 0:41
Gordon LinoffGordon Linoff
760k35294399
760k35294399
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53310832%2fmysql-trigger-to-increment-decrement-value-in-other-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
