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