0

I have the table:

CREATE TABLE CUSTOMER (
 CUSTID              NUMERIC(6) NOT NULL,
 NAME                CHAR (45),
 ADDRESS             CHAR (40),
 CITY                CHAR (30),
 STATE               CHAR (2),
 ZIP                 CHAR (9),
 AREA                NUMERIC(3),
 PHONE               CHAR (9),
 REPID               NUMERIC(4) NOT NULL,
 CREDITLIMIT         NUMERIC(9,2),
 COMMENTS            TEXT,
 CONSTRAINT CUSTOMER_PRIMARY_KEY PRIMARY KEY (CUSTID))
 AUTO_INCREMENT = 109;

and the trigger:

DELIMITER |
CREATE TRIGGER trigger_check_custid 
BEFORE INSERT ON CUSTOMER
 FOR EACH ROW BEGIN
   IF new.CUSTID > 0 THEN
    INSERT INTO CUSTOMER
    VALUES (new.CUSTID);
    END IF;
  END; 
|
DELIMITER ;

as a replacement for a constraint check, but when values are inserted into the table further in my SQL code, I get the error #1442 (Can't update table 'CUSTOMER' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.)

I read that this is to do with MySQL preventing potential recursion, but I am fairly new to SQL and am not sure where to identify the error. What could be causing it?

Thanks in advance.

J. Daykin
  • 45
  • 5
  • 1
    You categorically cannot update a table in a trigger which caused that trigger to execute. Maybe you can achieve the logic you want from wherever you are calling MySQL. – Tim Biegeleisen Nov 08 '16 at 15:44
  • Seems the 1442 cascade of doom error is running rampant today. Please search your code and figure out how A that triggers B that triggers A is happening – Drew Nov 08 '16 at 15:48

0 Answers0