create trigger to add up column data but not subtract
up vote
0
down vote
favorite
After several google searches, I have come here.
I have a MYSQL database table name users.
I have two columns, account_balance and earned_total.
I have several places in the PHP end where I update the account_balance when user does something. I would like to have a record of how much in total he has earned so far. So I have created a earned_total column.
Using trigger (or any other method) without modifying my PHP code how can I update the earned_total column too when account_balance column gets updated?
Remember When the user withdraws, the earned_total value should not be decreased.
mysql
asked Nov 22 at 13:51
Yaseen Hussain
356
|
show 1 more comment
up vote
0
down vote
favorite
After several google searches, I have come here.
I have a MYSQL database table name users.
I have two columns, account_balance and earned_total.
I have several places in the PHP end where I update the account_balance when user does something. I would like to have a record of how much in total he has earned so far. So I have created a earned_total column.
Using trigger (or any other method) without modifying my PHP code how can I update the earned_total column too when account_balance column gets updated?
Remember When the user withdraws, the earned_total value should not be decreased.
mysql
asked Nov 22 at 13:51
Yaseen Hussain
356
1
I don't think you can do this via a trigger, because the target table which contains theearned_totalcolumn is in the same table which would cause the trigger to fire. You may create a stored proc which does the insert, and then updates the row just inserted. See here for more information.
– Tim Biegeleisen
Nov 22 at 13:56
@bato3 That is not my understanding of triggers in MySQL, which can't be recursive. Maybe something has changed in MySQL 8 of which I am not aware.
– Tim Biegeleisen
Nov 22 at 14:06
my mistake Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
– bato3
Nov 22 at 14:16
@bato3 You cannot action the table which fired the trigger in a trigger but you can alter the NEW. values.in a before update trigger.
– P.Salmon
Nov 22 at 14:17
@P.Salmon Can you show the solution because I will learn something new with a desire for something new.
– bato3
Nov 22 at 14:23
|
show 1 more comment
up vote
0
down vote
favorite
up vote
0
down vote
favorite
After several google searches, I have come here.
I have a MYSQL database table name users.
I have two columns, account_balance and earned_total.
I have several places in the PHP end where I update the account_balance when user does something. I would like to have a record of how much in total he has earned so far. So I have created a earned_total column.
Using trigger (or any other method) without modifying my PHP code how can I update the earned_total column too when account_balance column gets updated?
Remember When the user withdraws, the earned_total value should not be decreased.
mysql
asked Nov 22 at 13:51
Yaseen Hussain
356
After several google searches, I have come here.
I have a MYSQL database table name users.
I have two columns, account_balance and earned_total.
I have several places in the PHP end where I update the account_balance when user does something. I would like to have a record of how much in total he has earned so far. So I have created a earned_total column.
Using trigger (or any other method) without modifying my PHP code how can I update the earned_total column too when account_balance column gets updated?
Remember When the user withdraws, the earned_total value should not be decreased.
mysql
mysql
asked Nov 22 at 13:51
Yaseen Hussain
356
asked Nov 22 at 13:51
Yaseen Hussain
356
asked Nov 22 at 13:51
Yaseen Hussain
356
asked Nov 22 at 13:51
Yaseen Hussain
356
asked Nov 22 at 13:51
Yaseen Hussain
356
356
1
I don't think you can do this via a trigger, because the target table which contains theearned_totalcolumn is in the same table which would cause the trigger to fire. You may create a stored proc which does the insert, and then updates the row just inserted. See here for more information.
– Tim Biegeleisen
Nov 22 at 13:56
@bato3 That is not my understanding of triggers in MySQL, which can't be recursive. Maybe something has changed in MySQL 8 of which I am not aware.
– Tim Biegeleisen
Nov 22 at 14:06
my mistake Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
– bato3
Nov 22 at 14:16
@bato3 You cannot action the table which fired the trigger in a trigger but you can alter the NEW. values.in a before update trigger.
– P.Salmon
Nov 22 at 14:17
@P.Salmon Can you show the solution because I will learn something new with a desire for something new.
– bato3
Nov 22 at 14:23
|
show 1 more comment
1
I don't think you can do this via a trigger, because the target table which contains theearned_totalcolumn is in the same table which would cause the trigger to fire. You may create a stored proc which does the insert, and then updates the row just inserted. See here for more information.
– Tim Biegeleisen
Nov 22 at 13:56
@bato3 That is not my understanding of triggers in MySQL, which can't be recursive. Maybe something has changed in MySQL 8 of which I am not aware.
– Tim Biegeleisen
Nov 22 at 14:06
my mistake Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
– bato3
Nov 22 at 14:16
@bato3 You cannot action the table which fired the trigger in a trigger but you can alter the NEW. values.in a before update trigger.
– P.Salmon
Nov 22 at 14:17
@P.Salmon Can you show the solution because I will learn something new with a desire for something new.
– bato3
Nov 22 at 14:23
1
1
I don't think you can do this via a trigger, because the target table which contains the
earned_total column is in the same table which would cause the trigger to fire. You may create a stored proc which does the insert, and then updates the row just inserted. See here for more information.– Tim Biegeleisen
Nov 22 at 13:56
I don't think you can do this via a trigger, because the target table which contains the
earned_total column is in the same table which would cause the trigger to fire. You may create a stored proc which does the insert, and then updates the row just inserted. See here for more information.– Tim Biegeleisen
Nov 22 at 13:56
@bato3 That is not my understanding of triggers in MySQL, which can't be recursive. Maybe something has changed in MySQL 8 of which I am not aware.
– Tim Biegeleisen
Nov 22 at 14:06
@bato3 That is not my understanding of triggers in MySQL, which can't be recursive. Maybe something has changed in MySQL 8 of which I am not aware.
– Tim Biegeleisen
Nov 22 at 14:06
my mistake Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
– bato3
Nov 22 at 14:16
my mistake Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
– bato3
Nov 22 at 14:16
@bato3 You cannot action the table which fired the trigger in a trigger but you can alter the NEW. values.in a before update trigger.
– P.Salmon
Nov 22 at 14:17
@bato3 You cannot action the table which fired the trigger in a trigger but you can alter the NEW. values.in a before update trigger.
– P.Salmon
Nov 22 at 14:17
@P.Salmon Can you show the solution because I will learn something new with a desire for something new.
– bato3
Nov 22 at 14:23
@P.Salmon Can you show the solution because I will learn something new with a desire for something new.
– bato3
Nov 22 at 14:23
|
show 1 more comment
1 Answer
1
active
oldest
votes
up vote
0
down vote
drop table if exists t;
create table t(account_balance int,earned_amount int);
drop trigger if exists t;
delimiter $$
create trigger t before update on t
for each row
begin
insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
if new.account_balance > old.account_balance then
set new.earned_amount = new.earned_amount + (new.account_balance - old.Account_balance);
end if;
insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
end $$
delimiter ;
truncate table t;
truncate table debug_table;
insert into t values (10,10);
update t set account_balance = Account_balance + 10 where 1 = 1;
update t set account_balance = Account_balance - 10 where 1 = 1;
select * from t;
+-----------------+---------------+
| account_balance | earned_amount |
+-----------------+---------------+
| 10 | 20 |
+-----------------+---------------+
1 row in set (0.00 sec)
select * from debug_table;
+----+------+------+
| id | msg | MSG2 |
+----+------+------+
| 1 | 10 | 10 |
| 2 | 10 | 20 |
| 3 | 20 | 20 |
| 4 | 20 | 20 |
+----+------+------+
4 rows in set (0.00 sec)
Note debug_table is not necessary to the solution , it's there to show that the first thing mysql does is write all OLD. values to NEW. values.
Personally I wouldn't store earned_amount, there's to much scope for error, and it can be easily calculated. Imagine for example a positive amount was contrad/reversed that should reduce the earned amount but there is no way of distinguishing this from a real withdrawal.
answered Nov 22 at 14:40
P.Salmon
7,4972415
@bato3 for you information also.
– P.Salmon
Nov 22 at 14:44
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',
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%2f53432476%2fcreate-trigger-to-add-up-column-data-but-not-subtract%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
up vote
0
down vote
drop table if exists t;
create table t(account_balance int,earned_amount int);
drop trigger if exists t;
delimiter $$
create trigger t before update on t
for each row
begin
insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
if new.account_balance > old.account_balance then
set new.earned_amount = new.earned_amount + (new.account_balance - old.Account_balance);
end if;
insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
end $$
delimiter ;
truncate table t;
truncate table debug_table;
insert into t values (10,10);
update t set account_balance = Account_balance + 10 where 1 = 1;
update t set account_balance = Account_balance - 10 where 1 = 1;
select * from t;
+-----------------+---------------+
| account_balance | earned_amount |
+-----------------+---------------+
| 10 | 20 |
+-----------------+---------------+
1 row in set (0.00 sec)
select * from debug_table;
+----+------+------+
| id | msg | MSG2 |
+----+------+------+
| 1 | 10 | 10 |
| 2 | 10 | 20 |
| 3 | 20 | 20 |
| 4 | 20 | 20 |
+----+------+------+
4 rows in set (0.00 sec)
Note debug_table is not necessary to the solution , it's there to show that the first thing mysql does is write all OLD. values to NEW. values.
Personally I wouldn't store earned_amount, there's to much scope for error, and it can be easily calculated. Imagine for example a positive amount was contrad/reversed that should reduce the earned amount but there is no way of distinguishing this from a real withdrawal.
answered Nov 22 at 14:40
P.Salmon
7,4972415
@bato3 for you information also.
– P.Salmon
Nov 22 at 14:44
add a comment |
up vote
0
down vote
drop table if exists t;
create table t(account_balance int,earned_amount int);
drop trigger if exists t;
delimiter $$
create trigger t before update on t
for each row
begin
insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
if new.account_balance > old.account_balance then
set new.earned_amount = new.earned_amount + (new.account_balance - old.Account_balance);
end if;
insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
end $$
delimiter ;
truncate table t;
truncate table debug_table;
insert into t values (10,10);
update t set account_balance = Account_balance + 10 where 1 = 1;
update t set account_balance = Account_balance - 10 where 1 = 1;
select * from t;
+-----------------+---------------+
| account_balance | earned_amount |
+-----------------+---------------+
| 10 | 20 |
+-----------------+---------------+
1 row in set (0.00 sec)
select * from debug_table;
+----+------+------+
| id | msg | MSG2 |
+----+------+------+
| 1 | 10 | 10 |
| 2 | 10 | 20 |
| 3 | 20 | 20 |
| 4 | 20 | 20 |
+----+------+------+
4 rows in set (0.00 sec)
Note debug_table is not necessary to the solution , it's there to show that the first thing mysql does is write all OLD. values to NEW. values.
Personally I wouldn't store earned_amount, there's to much scope for error, and it can be easily calculated. Imagine for example a positive amount was contrad/reversed that should reduce the earned amount but there is no way of distinguishing this from a real withdrawal.
answered Nov 22 at 14:40
P.Salmon
7,4972415
@bato3 for you information also.
– P.Salmon
Nov 22 at 14:44
add a comment |
up vote
0
down vote
up vote
0
down vote
drop table if exists t;
create table t(account_balance int,earned_amount int);
drop trigger if exists t;
delimiter $$
create trigger t before update on t
for each row
begin
insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
if new.account_balance > old.account_balance then
set new.earned_amount = new.earned_amount + (new.account_balance - old.Account_balance);
end if;
insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
end $$
delimiter ;
truncate table t;
truncate table debug_table;
insert into t values (10,10);
update t set account_balance = Account_balance + 10 where 1 = 1;
update t set account_balance = Account_balance - 10 where 1 = 1;
select * from t;
+-----------------+---------------+
| account_balance | earned_amount |
+-----------------+---------------+
| 10 | 20 |
+-----------------+---------------+
1 row in set (0.00 sec)
select * from debug_table;
+----+------+------+
| id | msg | MSG2 |
+----+------+------+
| 1 | 10 | 10 |
| 2 | 10 | 20 |
| 3 | 20 | 20 |
| 4 | 20 | 20 |
+----+------+------+
4 rows in set (0.00 sec)
Note debug_table is not necessary to the solution , it's there to show that the first thing mysql does is write all OLD. values to NEW. values.
Personally I wouldn't store earned_amount, there's to much scope for error, and it can be easily calculated. Imagine for example a positive amount was contrad/reversed that should reduce the earned amount but there is no way of distinguishing this from a real withdrawal.
answered Nov 22 at 14:40
P.Salmon
7,4972415
drop table if exists t;
create table t(account_balance int,earned_amount int);
drop trigger if exists t;
delimiter $$
create trigger t before update on t
for each row
begin
insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
if new.account_balance > old.account_balance then
set new.earned_amount = new.earned_amount + (new.account_balance - old.Account_balance);
end if;
insert into debug_table(msg,msg2) values (old.earned_amount,new.earned_amount);
end $$
delimiter ;
truncate table t;
truncate table debug_table;
insert into t values (10,10);
update t set account_balance = Account_balance + 10 where 1 = 1;
update t set account_balance = Account_balance - 10 where 1 = 1;
select * from t;
+-----------------+---------------+
| account_balance | earned_amount |
+-----------------+---------------+
| 10 | 20 |
+-----------------+---------------+
1 row in set (0.00 sec)
select * from debug_table;
+----+------+------+
| id | msg | MSG2 |
+----+------+------+
| 1 | 10 | 10 |
| 2 | 10 | 20 |
| 3 | 20 | 20 |
| 4 | 20 | 20 |
+----+------+------+
4 rows in set (0.00 sec)
Note debug_table is not necessary to the solution , it's there to show that the first thing mysql does is write all OLD. values to NEW. values.
Personally I wouldn't store earned_amount, there's to much scope for error, and it can be easily calculated. Imagine for example a positive amount was contrad/reversed that should reduce the earned amount but there is no way of distinguishing this from a real withdrawal.
answered Nov 22 at 14:40
P.Salmon
7,4972415
edited Nov 22 at 14:46
answered Nov 22 at 14:40
P.Salmon
7,4972415
answered Nov 22 at 14:40
P.Salmon
7,4972415
answered Nov 22 at 14:40
P.Salmon
7,4972415
7,4972415
@bato3 for you information also.
– P.Salmon
Nov 22 at 14:44
add a comment |
@bato3 for you information also.
– P.Salmon
Nov 22 at 14:44
@bato3 for you information also.
– P.Salmon
Nov 22 at 14:44
@bato3 for you information also.
– P.Salmon
Nov 22 at 14:44
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%2f53432476%2fcreate-trigger-to-add-up-column-data-but-not-subtract%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
I don't think you can do this via a trigger, because the target table which contains the
earned_totalcolumn is in the same table which would cause the trigger to fire. You may create a stored proc which does the insert, and then updates the row just inserted. See here for more information.– Tim Biegeleisen
Nov 22 at 13:56
@bato3 That is not my understanding of triggers in MySQL, which can't be recursive. Maybe something has changed in MySQL 8 of which I am not aware.
– Tim Biegeleisen
Nov 22 at 14:06
my mistake Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
– bato3
Nov 22 at 14:16
@bato3 You cannot action the table which fired the trigger in a trigger but you can alter the NEW. values.in a before update trigger.
– P.Salmon
Nov 22 at 14:17
@P.Salmon Can you show the solution because I will learn something new with a desire for something new.
– bato3
Nov 22 at 14:23