Re: Help with using Triggers MySql/Innodb

On 2015-02-26 06:42:33 -0800, david wrote:
> 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).


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

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

-- 
   _  | Peter J. Holzer           | I think we need two definitions:
|_|_) | WSR - Softwaredevelopment | 1) The problem the *users* want us to solve
| |   | und Projektunterstützung  | 2) The problem our solution addresses.
__/   | [email protected]             |    -- Phillip Hallam-Baker on spam

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iD8DBQFU70IfMdFfQa64PCwRAvZ6AJ4+r+TNSM5j7R7miSJvqq/gz/cTMgCgnD0A
2Wi7gr+fMyqAX8j59aSJDZo=
=dqQ0
-----END PGP SIGNATURE-----