George Fendler
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=`**********`@`loca lhost` 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.CustAccountNumbe r INTO vCustAccountNumber;
SELECT customers.custmac INTO vMac;
UPDATE test.cpes
SET cpes.WANMAC=vMac
WHERE cpes.CustAccountNumber=vCu stAccountN umber;
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.
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=`**********`@`loca
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.CustAccountNumbe
SELECT customers.custmac INTO vMac;
UPDATE test.cpes
SET cpes.WANMAC=vMac
WHERE cpes.CustAccountNumber=vCu
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I thought I tried something like that, but I mustn't have.