troubleshooting Question

Create a mysql trigger to update another table

Avatar of George Fendler
George FendlerFlag for United States of America asked on
DatabasesMySQL Server
2 Comments1 Solution140 ViewsLast Modified:
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
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros