SQL After Update Trigger
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
Im having some trouble with sql triggers in MySQL. The tables below are just used for testing.
First table:
CREATE TABLE `test`.`t1` (
`c1` INT NOT NULL,
`c2` VARCHAR(45) NULL,
`c3` VARCHAR(45) NULL,
PRIMARY KEY (`c1`));
Second Table:
CREATE TABLE `test`.`t2` (
`cc1` INT NOT NULL,
`cc2` VARCHAR(45) NULL,
`cc3` VARCHAR(45) NULL,
PRIMARY KEY (`cc1`));
cc1 references c1
ALl i want to do is, if the values in c2 are updated, then the corresponding field should be updated in cc2.
The trigger i came up with is:
DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;
DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`t1_AFTER_UPDATE` AFTER UPDATE ON `t1` FOR EACH ROW
BEGIN
update t2
set cc2=c2
where t2.cc1=t1.c1;
END$$
DELIMITER ;
This executes with no errors but if i try to update the values of c2,
UPDATE `test`.`t1` SET `c2` = '23' WHERE (`c1` = '11');
Operation failed: There was an error while applying the SQL script to
the database. ERROR 1054: 1054: Unknown column 't1.c1' in 'where
clause'
The update goes through if i remove the trigger.
mysql
edited Nov 16 '18 at 13:47
Madhur Bhaiya
19.7k62336
asked Nov 16 '18 at 13:29
The SageThe Sage
102
add a comment |
Im having some trouble with sql triggers in MySQL. The tables below are just used for testing.
First table:
CREATE TABLE `test`.`t1` (
`c1` INT NOT NULL,
`c2` VARCHAR(45) NULL,
`c3` VARCHAR(45) NULL,
PRIMARY KEY (`c1`));
Second Table:
CREATE TABLE `test`.`t2` (
`cc1` INT NOT NULL,
`cc2` VARCHAR(45) NULL,
`cc3` VARCHAR(45) NULL,
PRIMARY KEY (`cc1`));
cc1 references c1
ALl i want to do is, if the values in c2 are updated, then the corresponding field should be updated in cc2.
The trigger i came up with is:
DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;
DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`t1_AFTER_UPDATE` AFTER UPDATE ON `t1` FOR EACH ROW
BEGIN
update t2
set cc2=c2
where t2.cc1=t1.c1;
END$$
DELIMITER ;
This executes with no errors but if i try to update the values of c2,
UPDATE `test`.`t1` SET `c2` = '23' WHERE (`c1` = '11');
Operation failed: There was an error while applying the SQL script to
the database. ERROR 1054: 1054: Unknown column 't1.c1' in 'where
clause'
The update goes through if i remove the trigger.
mysql
edited Nov 16 '18 at 13:47
Madhur Bhaiya
19.7k62336
asked Nov 16 '18 at 13:29
The SageThe Sage
102
1
in triggers you can use theoldandnewkeywords to get access to the triggers table data in this caset1table depening on trigger event type like (ALTER UPDATE, or AFTER INSERT).. i believe it should bewhere t2.cc1=old.t1.c1;instead to make it make it work try it..
– Raymond Nijland
Nov 16 '18 at 13:44
add a comment |
Im having some trouble with sql triggers in MySQL. The tables below are just used for testing.
First table:
CREATE TABLE `test`.`t1` (
`c1` INT NOT NULL,
`c2` VARCHAR(45) NULL,
`c3` VARCHAR(45) NULL,
PRIMARY KEY (`c1`));
Second Table:
CREATE TABLE `test`.`t2` (
`cc1` INT NOT NULL,
`cc2` VARCHAR(45) NULL,
`cc3` VARCHAR(45) NULL,
PRIMARY KEY (`cc1`));
cc1 references c1
ALl i want to do is, if the values in c2 are updated, then the corresponding field should be updated in cc2.
The trigger i came up with is:
DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;
DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`t1_AFTER_UPDATE` AFTER UPDATE ON `t1` FOR EACH ROW
BEGIN
update t2
set cc2=c2
where t2.cc1=t1.c1;
END$$
DELIMITER ;
This executes with no errors but if i try to update the values of c2,
UPDATE `test`.`t1` SET `c2` = '23' WHERE (`c1` = '11');
Operation failed: There was an error while applying the SQL script to
the database. ERROR 1054: 1054: Unknown column 't1.c1' in 'where
clause'
The update goes through if i remove the trigger.
mysql
edited Nov 16 '18 at 13:47
Madhur Bhaiya
19.7k62336
asked Nov 16 '18 at 13:29
The SageThe Sage
102
Im having some trouble with sql triggers in MySQL. The tables below are just used for testing.
First table:
CREATE TABLE `test`.`t1` (
`c1` INT NOT NULL,
`c2` VARCHAR(45) NULL,
`c3` VARCHAR(45) NULL,
PRIMARY KEY (`c1`));
Second Table:
CREATE TABLE `test`.`t2` (
`cc1` INT NOT NULL,
`cc2` VARCHAR(45) NULL,
`cc3` VARCHAR(45) NULL,
PRIMARY KEY (`cc1`));
cc1 references c1
ALl i want to do is, if the values in c2 are updated, then the corresponding field should be updated in cc2.
The trigger i came up with is:
DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;
DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`t1_AFTER_UPDATE` AFTER UPDATE ON `t1` FOR EACH ROW
BEGIN
update t2
set cc2=c2
where t2.cc1=t1.c1;
END$$
DELIMITER ;
This executes with no errors but if i try to update the values of c2,
UPDATE `test`.`t1` SET `c2` = '23' WHERE (`c1` = '11');
Operation failed: There was an error while applying the SQL script to
the database. ERROR 1054: 1054: Unknown column 't1.c1' in 'where
clause'
The update goes through if i remove the trigger.
mysql
mysql
edited Nov 16 '18 at 13:47
Madhur Bhaiya
19.7k62336
asked Nov 16 '18 at 13:29
The SageThe Sage
102
edited Nov 16 '18 at 13:47
Madhur Bhaiya
19.7k62336
asked Nov 16 '18 at 13:29
The SageThe Sage
102
edited Nov 16 '18 at 13:47
Madhur Bhaiya
19.7k62336
edited Nov 16 '18 at 13:47
Madhur Bhaiya
19.7k62336
edited Nov 16 '18 at 13:47
Madhur Bhaiya
19.7k62336
19.7k62336
asked Nov 16 '18 at 13:29
The SageThe Sage
102
asked Nov 16 '18 at 13:29
The SageThe Sage
102
asked Nov 16 '18 at 13:29
The SageThe Sage
102
102
1
in triggers you can use theoldandnewkeywords to get access to the triggers table data in this caset1table depening on trigger event type like (ALTER UPDATE, or AFTER INSERT).. i believe it should bewhere t2.cc1=old.t1.c1;instead to make it make it work try it..
– Raymond Nijland
Nov 16 '18 at 13:44
add a comment |
1
in triggers you can use theoldandnewkeywords to get access to the triggers table data in this caset1table depening on trigger event type like (ALTER UPDATE, or AFTER INSERT).. i believe it should bewhere t2.cc1=old.t1.c1;instead to make it make it work try it..
– Raymond Nijland
Nov 16 '18 at 13:44
1
1
in triggers you can use the
old and new keywords to get access to the triggers table data in this case t1 table depening on trigger event type like (ALTER UPDATE, or AFTER INSERT).. i believe it should be where t2.cc1=old.t1.c1; instead to make it make it work try it..– Raymond Nijland
Nov 16 '18 at 13:44
in triggers you can use the
old and new keywords to get access to the triggers table data in this case t1 table depening on trigger event type like (ALTER UPDATE, or AFTER INSERT).. i believe it should be where t2.cc1=old.t1.c1; instead to make it make it work try it..– Raymond Nijland
Nov 16 '18 at 13:44
add a comment |
1 Answer
1
active
oldest
votes
In a Trigger, we refer to columns in the rows being updated/inserted/deleted by NEW (after operation), and OLD (before operation) keywords.
You will need to use these keywords in order to be able to update the other table.
Also, we can optimize the Trigger to initiate UPDATE operation only when there has been a change observed in the c2 value.
DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;
DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`t1_AFTER_UPDATE`
AFTER UPDATE ON `t1` FOR EACH ROW
BEGIN
-- Check if there has been any change in the c2 value or not
IF (NEW.c2 <> OLD.c2) THEN
-- Update only when there is some change
UPDATE t2
SET cc2 = NEW.c2 -- access the recent updated value of c2 using NEW keyword
WHERE cc1 = NEW.c1; -- access the c1 value of updated row using NEW keyword
END IF;
END$$
DELIMITER ;
answered Nov 16 '18 at 15:03
Madhur BhaiyaMadhur Bhaiya
19.7k62336
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%2f53338863%2fsql-after-update-trigger%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
In a Trigger, we refer to columns in the rows being updated/inserted/deleted by NEW (after operation), and OLD (before operation) keywords.
You will need to use these keywords in order to be able to update the other table.
Also, we can optimize the Trigger to initiate UPDATE operation only when there has been a change observed in the c2 value.
DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;
DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`t1_AFTER_UPDATE`
AFTER UPDATE ON `t1` FOR EACH ROW
BEGIN
-- Check if there has been any change in the c2 value or not
IF (NEW.c2 <> OLD.c2) THEN
-- Update only when there is some change
UPDATE t2
SET cc2 = NEW.c2 -- access the recent updated value of c2 using NEW keyword
WHERE cc1 = NEW.c1; -- access the c1 value of updated row using NEW keyword
END IF;
END$$
DELIMITER ;
answered Nov 16 '18 at 15:03
Madhur BhaiyaMadhur Bhaiya
19.7k62336
add a comment |
In a Trigger, we refer to columns in the rows being updated/inserted/deleted by NEW (after operation), and OLD (before operation) keywords.
You will need to use these keywords in order to be able to update the other table.
Also, we can optimize the Trigger to initiate UPDATE operation only when there has been a change observed in the c2 value.
DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;
DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`t1_AFTER_UPDATE`
AFTER UPDATE ON `t1` FOR EACH ROW
BEGIN
-- Check if there has been any change in the c2 value or not
IF (NEW.c2 <> OLD.c2) THEN
-- Update only when there is some change
UPDATE t2
SET cc2 = NEW.c2 -- access the recent updated value of c2 using NEW keyword
WHERE cc1 = NEW.c1; -- access the c1 value of updated row using NEW keyword
END IF;
END$$
DELIMITER ;
answered Nov 16 '18 at 15:03
Madhur BhaiyaMadhur Bhaiya
19.7k62336
add a comment |
In a Trigger, we refer to columns in the rows being updated/inserted/deleted by NEW (after operation), and OLD (before operation) keywords.
You will need to use these keywords in order to be able to update the other table.
Also, we can optimize the Trigger to initiate UPDATE operation only when there has been a change observed in the c2 value.
DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;
DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`t1_AFTER_UPDATE`
AFTER UPDATE ON `t1` FOR EACH ROW
BEGIN
-- Check if there has been any change in the c2 value or not
IF (NEW.c2 <> OLD.c2) THEN
-- Update only when there is some change
UPDATE t2
SET cc2 = NEW.c2 -- access the recent updated value of c2 using NEW keyword
WHERE cc1 = NEW.c1; -- access the c1 value of updated row using NEW keyword
END IF;
END$$
DELIMITER ;
answered Nov 16 '18 at 15:03
Madhur BhaiyaMadhur Bhaiya
19.7k62336
In a Trigger, we refer to columns in the rows being updated/inserted/deleted by NEW (after operation), and OLD (before operation) keywords.
You will need to use these keywords in order to be able to update the other table.
Also, we can optimize the Trigger to initiate UPDATE operation only when there has been a change observed in the c2 value.
DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;
DELIMITER $$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`t1_AFTER_UPDATE`
AFTER UPDATE ON `t1` FOR EACH ROW
BEGIN
-- Check if there has been any change in the c2 value or not
IF (NEW.c2 <> OLD.c2) THEN
-- Update only when there is some change
UPDATE t2
SET cc2 = NEW.c2 -- access the recent updated value of c2 using NEW keyword
WHERE cc1 = NEW.c1; -- access the c1 value of updated row using NEW keyword
END IF;
END$$
DELIMITER ;
answered Nov 16 '18 at 15:03
Madhur BhaiyaMadhur Bhaiya
19.7k62336
answered Nov 16 '18 at 15:03
Madhur BhaiyaMadhur Bhaiya
19.7k62336
answered Nov 16 '18 at 15:03
Madhur BhaiyaMadhur Bhaiya
19.7k62336
answered Nov 16 '18 at 15:03
Madhur BhaiyaMadhur Bhaiya
19.7k62336
19.7k62336
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.
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%2f53338863%2fsql-after-update-trigger%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
1
in triggers you can use the
oldandnewkeywords to get access to the triggers table data in this caset1table depening on trigger event type like (ALTER UPDATE, or AFTER INSERT).. i believe it should bewhere t2.cc1=old.t1.c1;instead to make it make it work try it..– Raymond Nijland
Nov 16 '18 at 13:44