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
I thought I tried something like that, but I mustn't have.