Mysql Trigger Syntax

[mjsulliv]

I’m trying to write a trigger that will cause an increment in a count value in one table when a row is inserted in another table that has a matching id value. The syntax I’ve tried is:

CREATE TRIGGER  inc_m_cnt  AFTER INSERT ON m UPDATE c SET c.m_cnt = c.m_cnt + 1 WHERE m.c_id = c.c_id.

Myphpadmin give the generic error message that there is a problem in the SQL. Any thoughts will appreciated. Test DB:

-- phpMyAdmin SQL Dump-- version 3.3.9-- [url="http://www.phpmyadmin.net"]http://www.phpmyadmin.net[/url]---- Host: localhost-- Generation Time: Oct 28, 2011 at 09:43 PM-- Server version: 5.5.8-- PHP Version: 5.3.5SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";---- Database: `test`---- ------------------------------------------------------------ Table structure for table `c`--CREATE TABLE IF NOT EXISTS `c` (  `c_id` int(10) NOT NULL,  `m_cnt` int(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;---- Dumping data for table `c`--INSERT INTO `c` (`c_id`, `m_cnt`) VALUES(1, 0),(2, 0),(3, 0),(4, 0),(1, 0),(2, 0),(3, 0),(4, 0);-- ------------------------------------------------------------ Table structure for table `m`--CREATE TABLE IF NOT EXISTS `m` (  `m_id` int(10) NOT NULL,  `c_id` int(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;---- Dumping data for table `m`--INSERT INTO `m` (`m_id`, `c_id`) VALUES(1, 1),(2, 2),(3, 3),(4, 4);