Avatar of George Fendler
George Fendler
Flag 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.
DatabasesMySQL Server

Avatar of undefined
Last Comment
George Fendler

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck