Link to home
Create AccountLog in
Avatar of George Fendler
George FendlerFlag for United States of America

asked on

Create a mysql trigger to update another table

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.
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of George Fendler

ASKER

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.