MySQL Forums
Forum List  »  Triggers

ERROR 1442 - Cannot update table which has trigger
Posted by: Daniel Xyz
Date: November 10, 2012 05:21AM

The problem is like in a title, Ive read some solutions and used "NEW.colum_name" keyword to solve the problem. Trigger definition:

CREATE TRIGGER `trigger1` BEFORE UPDATE ON `driver`
FOR EACH ROW BEGIN
UPDATE driver
SET NEW.is_available=0
WHERE NEW.break_finish_time >= now() && NEW.break_start_time <= now();
END

And when I want to update table 'driver':

ERROR 1442: Can't update table 'driver' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
SQL Statement:
UPDATE `taxi`.`driver` SET `break_finish_time`='2012-11-09 12:13:00' WHERE `iddriver`='2'



'driver' create statement:

================================================================
delimiter $$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `taxi`.`trigger1`
BEFORE UPDATE ON `taxi`.`driver`
FOR EACH ROW
BEGIN
UPDATE driver
SET NEW.is_available=0
WHERE NEW.break_finish_time >= now() && NEW.break_start_time <= now();
END
$$

CREATE TABLE `driver` (
`iddriver` int(10) unsigned NOT NULL AUTO_INCREMENT,
`is_available` tinyint(1) NOT NULL,
`driver_name` varchar(45) COLLATE utf8_polish_ci NOT NULL,
`driver_surname` varchar(45) COLLATE utf8_polish_ci NOT NULL,
`driverpoint_horizontal` double NOT NULL,
`driverpoint_vertical` double NOT NULL,
`break_start_time` timestamp NULL DEFAULT NULL,
`break_finish_time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`iddriver`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci$$
================================================================

It doesnt have any foreign keys, however other tables does have foreign key which uses 'iddriver' from 'driver' table.

Is there any solution?



Edited 1 time(s). Last edit at 11/10/2012 05:22AM by Daniel Xyz.

Options: ReplyQuote


Subject
Views
Written By
Posted
ERROR 1442 - Cannot update table which has trigger
17546
November 10, 2012 05:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.