So, I have a table, described via the SQL given below
CREATE TABLE IF NOT EXISTS `employees` (
`e_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`e_username` varchar(100) NOT NULL,
`left_id` int(10) unsigned NOT NULL,
`right_id` int(10) unsigned NOT NULL,
`e_ssn` int(5) unsigned NOT NULL DEFAULT '0',
`e_first_name` varchar(100) NOT NULL,
`e_last_name` varchar(100) NOT NULL,
`e_address` text NOT NULL,
`e_location` int(10) unsigned DEFAULT NULL,
UNIQUE KEY `e_id` (`e_id`),
UNIQUE KEY `e_username` (`e_username`)
)
And it has the given data
INSERT INTO `employees` (`e_id`, `e_username`, `left_id`, `right_id`, `e_ssn`, `e_first_name`, `e_last_name`, `e_address`, `e_location`) VALUES
(1, '100884', 1, 8, 444444444, 'James', 'Burnbridge', '1123 fdkjfjdkfjdfkjd', 76),
(2, '100885', 2, 7, 0, 'Frank', 'Grimsley', '123 Fayke St', 76),
(5, '100886', 3, 4, 0, 'Mark', 'Hill', 'kfjdkfjdf\r\nkofjdkfjdkfdj', 76),
(6, '100887', 5, 6, 0, 'fdfdfd', 'fdfdfd', 'fdfdfdf', 76);
I want to create a trigger that makes it so that when I input a new row, like so
INSERT INTO employees(e_username,left_id,right_id,e_ssn, e_first_name, e_last_name, e_address, e_location)
VALUES('100888', 0, 0, 0, 'New','User', '100 NotReal st city, state zip', 76)
I want for the left_id and right_id columns to be updated based on the right_id of the first user in their location that has it's right_id differ from it's left_id by 1.
Here is what I have so far, but it gives me an error
DROP TRIGGER IF EXISTS `after_insert_employees` ;
CREATE DEFINER = `root`@`localhost` TRIGGER `after_insert_employees` AFTER INSERT ON `employees`
FOR EACH
ROW BEGIN
DECLARE r_id INT;
SELECT max(right_id) INTO r_id FROM employees WHERE e_location=new.e_location AND right_id = left_id+1;
UPDATE employees SET left_id = r_id+1 AND right_id = r_id+2 WHERE e_id = new.e_id;
END
I get this error when I try to insert something
#1442 - Can't update table 'employees' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
I'm lost on this one. Any help?
