Re: Help with using Triggers MySql/Innodb

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 .....)