How can I set a mysql Trigger to update a column with the sum others?

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 1939 Views - Last Post: 06 July 2015 - 05:05 AM

#1 Ty Meador   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 166
  • Joined: 23-May 11

How can I set a mysql Trigger to update a column with the sum others?

Posted 12 June 2015 - 07:15 AM

Hello World! I'm having trouble with a MYSQL trigger using phpmyadmin. I have a table for donations (stores the amount donated, the date, and contributors I.D.), And a table for contributors (the people who made the donations). I need a trigger that will update a contributors total contributions every time an insert is done on that user. Table structure is as follows

[Donations]
-Date
-Amount
-Contributor I.D.

[Contributors]
-Name
-Address
-Phone
-I.D. (Primary Key)
-TotalContributions

So I (think) I need something like the following
FOR EACH ROW
BEGIN
SET @total := (SELECT SUM(Amount) FROM crm.contributions WHERE `Contributor`= NEW.Contributor)

UPDATE crm.contributors SET `TotalConstributions`= @total WHERE `PrimaryKey`= NEW.Contributor
END


But phpmyadmin is giving me the crazy errors that are near illegible

This post has been edited by Ty Meador: 12 June 2015 - 07:28 AM


Is This A Good Question/Topic? 1
  • +

Replies To: How can I set a mysql Trigger to update a column with the sum others?

#2 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6552
  • View blog
  • Posts: 26,563
  • Joined: 12-December 12

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 12 June 2015 - 07:44 AM

This is not a good idea. The contributors total is a derived quantity and can always be determined by querying and totalling their donations.

What happens if someone submits the wrong donation amount, how will you ensure that the total is corrected? What if the trigger fails (or is disabled) at some point, even if briefly?

It is also just adding an unnecessary task to the database.

(You also wouldn't need to iterate rows in the trigger, you already would know who the contributor is.)
Was This Post Helpful? 2
  • +
  • -

#3 Ty Meador   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 166
  • Joined: 23-May 11

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 12 June 2015 - 07:45 AM

View Postandrewsw, on 12 June 2015 - 08:42 AM, said:

This is not a good idea. The contributors total is a derived quantity and can always be determined by querying and totalling their donations.

What happens if someone submits the wrong donation amount, how will you ensure that the total is corrected? What if the trigger fails (or is disabled) at some point, even if briefly?

It is also just adding an unnecessary task to the database.

The database is going to have to do the work either way since I'm doing the exact same thing VIA php right now. I'm just trying to simplify my code. We could also say "what if any one of my queries fail?" I mean, Is it really that much more likely that a trigger fails than a query? Plus, I'm going to do the same thing on update queries (so if they enter the wrong donation amount, they can just edit it later, and it still works :) ) :bananaman:
I got a trigger to (i'm not gonna say work, since I can't insert into the table that the trigger is on now, but PHPMYADMIN and SQL accepted it). It looks like this
Begin
SET @total := (SELECT SUM(Amount) FROM crm.contributions WHERE `Contributor`=NEW.Contributor);

UPDATE crm.contributors SET `TotalConstributions`= @total WHERE `PrimaryKey`=NEW.Contributor;
END

This post has been edited by Ty Meador: 12 June 2015 - 07:50 AM

Was This Post Helpful? 1
  • +
  • -

#4 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6552
  • View blog
  • Posts: 26,563
  • Joined: 12-December 12

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 12 June 2015 - 07:55 AM

If a donation fails then a transaction can be rolled back, and the most recent donation's date and amount can be compared. It is much harder to handle failures with an unattended trigger, AFAIK.

Anyway, as I say, you shouldn't need to loop through rows in your trigger. I would start there ;) Added: just seen your edited posted above, and you've done this.

Don't forget a DELETE trigger ;) Possibly BEFORE DELETE (I haven't checked).

This post has been edited by andrewsw: 12 June 2015 - 08:00 AM

Was This Post Helpful? 0
  • +
  • -

#5 Ty Meador   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 166
  • Joined: 23-May 11

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 12 June 2015 - 08:00 AM

View Postandrewsw, on 12 June 2015 - 08:55 AM, said:

If a donation fails then a transaction can be rolled back, and the most recent donation's date and amount can be compared. It is much harder to handle failures with an unattended trigger, AFAIK.


So if the initial insert (the one that fires the trigger) fails, then the previous value is never changed anyway, since the trigger won't get fired. Is a trigger not less accident prone than using PHP to update the total on every form that allows you to add a contribution? I'm asking because I have no idea. I just saw that sql had trigger support, looked into it, and realized this would make things quite a bit simpler.

(And yes, I intend to use pretty much the exact same trigger "code" for insert, update, and delete, so that no matter what the end user does with the contributions, the total stays current)

This post has been edited by Ty Meador: 12 June 2015 - 08:02 AM

Was This Post Helpful? 1
  • +
  • -

#6 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6552
  • View blog
  • Posts: 26,563
  • Joined: 12-December 12

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 12 June 2015 - 08:10 AM

Quote

Is a trigger not less accident prone than using PHP to update the total on every form that allows you to add a contribution?

Yes, but you wouldn't do that. The total can be derived at any point, it does not need to be stored in the database. A form could display a total of the current contributor's donations. This would be as a convenience to the user, but the figure would not be trusted. Such subtotals are rarely sent back with a form submission, they are just (sensibly) discarded; if the total/subtotal needs to be recalculated for any reason, this would happen server-side.
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2542
  • View blog
  • Posts: 10,183
  • Joined: 03-December 12

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 12 June 2015 - 09:00 AM

You are going about this partially the way I would.

I would change the database design a bit, as you could have people donate for various thing at different points.

So, I would have a contributors table, a contributing_projects (linking table), and a table that listed current projects to donate to.
The contributing_projects would hold the foreign keys from the contributors and the current_projects and the amount they donated ( after verification of payment) and possibly a timestamp to show when the donation was made.

To get the total amount, you would do a sum aggregate on the linking table. Thereby giving you the total amount donated.
Was This Post Helpful? 2
  • +
  • -

#8 Ty Meador   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 166
  • Joined: 23-May 11

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 14 June 2015 - 10:35 AM

View Postastonecipher, on 12 June 2015 - 09:00 AM, said:

You are going about this partially the way I would.

I would change the database design a bit, as you could have people donate for various thing at different points.

So, I would have a contributors table, a contributing_projects (linking table), and a table that listed current projects to donate to.
The contributing_projects would hold the foreign keys from the contributors and the current_projects and the amount they donated ( after verification of payment) and possibly a timestamp to show when the donation was made.

To get the total amount, you would do a sum aggregate on the linking table. Thereby giving you the total amount donated.

...But how do I do that? :helpsmilie:
Was This Post Helpful? 0
  • +
  • -

#9 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2542
  • View blog
  • Posts: 10,183
  • Joined: 03-December 12

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 14 June 2015 - 11:04 AM

Which part do you need help with?

First thing to do is figure out the best database structure. So, for the contributors table, what fields do you need to save?

Create table Contributors 
(
    id int(11) not null auto_increment,
    firstName varchar(45) not null,
    lastName varchar(45) not null,
    emaill varchar(45) not null unique,
    #other needed fields for identity
    PRIMARY KEY(id)
)   engine=InnoDB



I don't know how complex you want to go, but events here could be tied to other groups such as other non-profit organizations.

Create table DonationEvents 
(
    id int(11) not null auto_increment,
    event varchar(45) not null,
    eventDescription varchar(45) not null
    PRIMARY KEY(id)
)   engine=InnoDB


Linking table

Create table Contributors_DonationEvents 
(
    id int(11) not null auto_increment,
    contributorID int(11) not null,
    donationEventID int(11) not null,
    PRIMARY KEY(id)
)   engine=InnoDB

Was This Post Helpful? 2
  • +
  • -

#10 Ty Meador   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 166
  • Joined: 23-May 11

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 14 June 2015 - 11:10 AM

View Postastonecipher, on 14 June 2015 - 11:04 AM, said:

Which part do you need help with?
[/code]

I actually have all of that done. lol. What I initially meant was "how would I set a trigger to do that". But I now realize that what you are saying is I should be calculating the SUM on the fly with php when it is needed. I could do this. My thought was that by storing the value when the table is altered and doing the math only one time, the server overhead would be reduced since the table will be queried more often than updated.
Was This Post Helpful? 0
  • +
  • -

#11 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2542
  • View blog
  • Posts: 10,183
  • Joined: 03-December 12

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 14 June 2015 - 11:26 AM

SQL queries are not resource intensive, generally. But Storing something like that can cause data corruption. What you could do in place of what you are thinking is to create a view. The view would keep the current amounts without the need to update anything or create a trigger to do it for you. Example,

create view contributors_donations as
SELECT `contributors`.`id`, SUM(`contributors_donationevents`.`amount`) as 'Total Donated'
FROM
	contributors
Left join
	`contributors_donationevents`
on	`contributors`.`id` = `contributors_donationevents`.`contributorID`
 GROUP BY `donations`.`contributors`.`id`;
    



This in effect creates a 'table' (not actually) and gets the total amount that each contributor has donated over the lifetime. You can refine it further by making it per event or anything else.

A better version:

create view Total_Donated as
SELECT 
	CONCAT( contributors.lastName, ', ', contributors.firstName) as Name,
    SUM(contributors_donationevents.amount) as 'Total Given'
FROM
	donations.contributors
left join
	donations.contributors_donationevents
ON
	donations.contributors.id = donations.contributors_donationevents.contributorID
group by
	Name asc;


This post has been edited by astonecipher: 14 June 2015 - 11:48 AM

Was This Post Helpful? 1
  • +
  • -

#12 Ty Meador   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 166
  • Joined: 23-May 11

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 15 June 2015 - 02:35 PM

You Mean to tell me that if I create a view, It's essentially a "virtual table" that I can call at any point? Do I query views the same way I do tables?
Was This Post Helpful? 0
  • +
  • -

#13 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2542
  • View blog
  • Posts: 10,183
  • Joined: 03-December 12

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 15 June 2015 - 03:07 PM

Yes and yes. A view is sort of like a pre-built SELECT statement.


Create a sample database with the schema I posted and see what I mean.
Was This Post Helpful? 1
  • +
  • -

#14 Ty Meador   User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 166
  • Joined: 23-May 11

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 16 June 2015 - 09:24 AM

Fantastic. A view is EXACTLY what I need. But here's the hard part now. My database looks like this right now

Tables
contributions
PrimaryKey
Date
Contributor*
Amount

contributors
PrimaryKey*
FirstName
Lastname
Email
StreetAddress
City
State
Zip
PhoneNumber
Notes

I have a one to many relationship here, One contributor can make multiple contributions. We know who made the contribution because in the `contribution` table, the "Contributor" column is linked to the "PrimaryKey" column in the `contributors` table. So if i contribute, and my PrimaryKey is 5, a row is added to the contribution table, and `Contributor` is set to 5. Does this make sense? So im' not sure where to go from here
Was This Post Helpful? 0
  • +
  • -

#15 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2542
  • View blog
  • Posts: 10,183
  • Joined: 03-December 12

Re: How can I set a mysql Trigger to update a column with the sum others?

Posted 16 June 2015 - 09:56 AM

It isn't far from my design. The only difference I see is, every contribution is going to the same fundraiser. Which may be what you want now, but if you start having different ones, you can't say who donated to what.

A few more queries could fix it.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2