Create a mysql trigger to update another table

George Fendler
George Fendler used Ask the Experts™
on
I am having a problem with a trigger in mysql.
This is the first time that I have tried it, but it should be simple. I'm just having trouble with the syntax.
I know that this is a good example of non-normalized tables. I have a good reason for the duplicate columns. Please don't waste time lecturing.

I have a database called test. it has two tables customers and cpes.

      
CREATE TABLE `customers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `custname` varchar(25) NOT NULL,
 `clientMAC` varchar(18) NOT NULL DEFAULT '00:00:00:00:00:00',
 `CustAccountNumber` varchar(8) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1


CREATE TABLE `cpes` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `CustAccountNumber` varchar(8) NOT NULL,
 `WANMAC` varchar(18) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

mysql> select * from customers;
+----+----------+-------------------+-------------------+
| id + custname | clientMAC         | CustAccountNumber |
+----+----------+-------------------+-------------------+
|  1 | andy     | 00:00:00:00:00:00 | NUM0001           |
|  2 | billy    | 00:00:00:00:00:00 | NUM0002           |
+----+----------+-------------------+-------------------+
2 rows in set (0.00 sec)

mysql> select * from cpes;
+-----+-------------------+-----------+
| id  | CustAccountNumber | WANMAC    |
+-----+-------------------+-----------+
|  1  | NUM0001           | unknown   |
|  2  | NUM0002           | 000000000 |
+-----+-------------------++----------+
2 rows in set (0.01 sec)

CREATE DEFINER=`**********`@`localhost` TRIGGER customers_after_update
AFTER UPDATE
  ON customers FOR EACH ROW

BEGIN

  DECLARE vCustAccountNumber varchar(8);
  DECLARE vMac varchar(18);

  -- Find MAC of radio that got updated
  SELECT customers.CustAccountNumber INTO vCustAccountNumber;
  SELECT customers.custmac INTO vMac;
 
  UPDATE test.cpes
  SET cpes.WANMAC=vMac
  WHERE cpes.CustAccountNumber=vCustAccountNumber;

END

---------------------------------------------------------------
When I attempt to update theclientMAC field in the customers table in phpmyadmin, I get an error:
      #1109 - Unknown table 'customers' in field list
      UPDATE `test`.`customers` SET `clientMAC` = '00:00:00:00:00:88' WHERE `customers`.`id` = 1
      
The query that is being generated by the trigger doesn't work, its trying to update the wrong table.
I don't understand what's happening.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
E.g.

CREATE TRIGGER customers_after_update
AFTER UPDATE
ON customers FOR EACH ROW
BEGIN
	INSERT INTO cpes ( WANMAC, CustAccountNumber)
	VALUES ( NEW.WANMAC, NEW.CustAccountNumber );
END;

Open in new window

George Fendlerprogrammer

Author

Commented:
Thank you ste5an. That works perfectly. I struggled with this half of yesterday.
I thought I tried something like that, but I mustn't have.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial