Help with using Triggers MySql/Innodb

[david]

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


The trigger gets "triggered", but a diagnostic says that
"People.HouseID" is an undefined column.

Any clues would help.

Thanks
David

[Martin J. Evans]

Post by david
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.
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.
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".

Post by david
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

[Steven Lembark]

On Thu, 26 Feb 2015 15:31:56 +0000

Post by Martin J. Evans
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.

You could get a one-step summary using a view that does a select
with count(1) as "people" from a join of houses and people, grouped
by the house. That leaves the houses and people separate and still
gives you one-stop shopping for the houses with accurate counts.

[V&D]

Post by Martin J. Evans

Post by david
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.
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.
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".

Post by david
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 .....)

[Peter J. Holzer]

Post by david
This may be the wrong mailing list for this question, so I apologize in
advance for breaking some rule.

Strictly speaking yes. The trigger syntax is independent of the method
used to access the database (DBI in our case).

Post by david
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.
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.
CREATE TRIGGER after_insert_people AFTER INSERT ON People FOR EACH ROW
BEGIN
UPDATE House SET PeopleCount = PeopleCount + 1 WHERE HouseID =
People.HouseID;
END
The trigger gets "triggered", but a diagnostic says that "People.HouseID" is
an undefined column.

See http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html:

| Within the trigger body, the OLD and NEW keywords enable you to access
| columns in the rows affected by a trigger. OLD and NEW are MySQL
| extensions to triggers; they are not case sensitive.

So that should be

UPDATE House SET PeopleCount = PeopleCount + 1 WHERE HouseID = NEW.HouseID

hp