Help with using Triggers MySql/Innodb

david <[email protected]>
Newsgroups gmane.comp.lang.perl.modules.dbi.general
Message-ID <[email protected]>
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
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.