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. __/ | h...@wsr.ac.at | -- Phillip Hallam-Baker on spam
signature.asc
Description: Digital signature
