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
