On 02/26/2015 05:31 PM, Martin J. Evans wrote:
On 26/02/15 14:42, david wrote:
MySql Gurus
This may be the wrong mailing list for this question, so I apologize
in advance for breaking some rule.
I'm trying to get a trigger to work. My environment is a Perl DBI
interface to a MySql database on Centos 6. I use the InnoDB database.
I have two tables involved in this operation:
table: House, with fields:
HouseID INT AUTO_INCREMENT
PeopleCount INT
<other fields not relevant to this discussion>
Table: People, with fields
PeopleID INT AUTO_INCREMENT
HouseID INT (shows which house the person belongs to)
Name VARCHAR(30)
<other fields not relevant to this discussion
Whenever I insert (or delete) a row to the People table, I want the
PeopleCount in the House (identified by the HouseID in the people
table) to be incremented or decremented.
My latest attempt is a trigger:
CREATE TRIGGER after_insert_people AFTER INSERT ON People FOR EACH ROW
BEGIN
UPDATE House SET PeopleCount = PeopleCount + 1 WHERE HouseID =
People.HouseID;
END
I don't use mysql these days but in Oracle that would be something
like "where houseid = :new.houseid".
The trigger gets "triggered", but a diagnostic says that
"People.HouseID" is an undefined column.
Any clues would help.
Thanks
David
I would actually suggest you don't use a trigger for this and also
that you don't store the count of people in a house in a column in the
house table. You can always calculate how many people there are in a
house but if you use triggers or some other mechanism to keep the
count you run the risk of the count not actually agreeing with the
rows with that house id. In other words, I think this is generally bad
design.
Martin
UPDATE House SET PeopleCount = PeopleCount + 1 WHERE HouseID =
(select HouseID from people where .....)
