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.
Subject
Views
Written By
Posted
ERROR 1442 - Cannot update table which has trigger
28213
November 10, 2012 05:21AM
8917
November 13, 2012 01:30AM
4966
November 18, 2012 07:26AM
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.