MYSQL Trigger help

[Canuckfan21]

Hey guys just had a question about MySQL triggers.. I'm a newbie at MySQL and I'm trying to create a trigger that would remove an entry from one table, after data has been changed in another. If that makes sense..

 

 

Table 1:

 

CREATE TABLE IF NOT EXISTS `patient` (

  `p_person_id` int(11) NOT NULL auto_increment,

  `contact_date` date NOT NULL,

  `patient_type` char(1) NOT NULL,

  PRIMARY KEY  (`p_person_id`),

  KEY `patient_type` (`patient_type`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

 

 

Table 2:

 

 

 

CREATE TABLE IF NOT EXISTS `resident` (

  `rp_person_id` int(11) NOT NULL auto_increment,

  `bed_number` int(11) NOT NULL,

  `room_number` int(11) NOT NULL,

  `care_center_id` int(11) NOT NULL,

  PRIMARY KEY  (`rp_person_id`),

  KEY `bed_number` (`bed_number`,`room_number`,`care_center_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

 

 

So patient type is either R or O (Resident or Outpatient).

 

Whenever a Resident is changed to an outpatient, I would want the entry

of Resident to be deleted from the Resident table.

 

 

 

Here is my trigger:

 

CREATE TRIGGER `patient_trig` AFTER UPDATE

on `patient`

FOR EACH ROW BEGIN

DELETE FROM `resident` WHERE rp_person_id = NEW.p_person_id;

 

 

That won't work, thanks in advance for any help or attempts to solve the problem!

[fenway]

What "doesn't work" about it?

[Mchl]

For start, there is no END to pair with BEGIN. Also before defining a trigger you most likely will have to change delimiter from ; to something else (this is covered in MySQL's manual AFAIR, but reminding you just in case)