CODEWITHSUNDEEP
mysqlsqldatabasemysql-workbench
Arryyyy
Arryyyy

Reputation: 101

Error Code: 1109. Unknown table 'new' in field list

I'm creating a database, where I validate input data with triggers. I added them successfully, but there is an error when I try to input fine data.

creating db, tables and triggers

CREATE DATABASE IF NOT EXISTS DBWorkers;

CREATE TABLE IF NOT EXISTS Ludzie(
PESEL char(11) NOT NULL,
imie varchar(30) NOT NULL,
nazwisko varchar(30) NOT NULL,
data_urodzenia date NOT NULL,
wzrost float NOT NULL,
waga float NOT NULL,
rozmiar_buta int NOT NULL,

PRIMARY KEY(PESEL));

DELIMITER $$
CREATE FUNCTION check_pesel(PESEL char(11)) RETURNS bool
BEGIN
IF(
    CHAR_LENGTH(NEW.PESEL) <> 11 AND
    CONVERT(PESEL, SIGNED INT) > 0 AND
    CONVERT(LEFT(PESEL,2), SIGNED INT) BETWEEN 0 AND 99 AND
    CONVERT(SUBSTRING(PESEL,3,2), SIGNED INT) BETWEEN 1 AND 12 AND
    CONVERT(SUBSTRING(PESEL,3,2), SIGNED INT) BETWEEN 1 AND     DAY(LAST_DAY(CONCAT('19',LEFT(PESEL,2),'-',SUBSTRING(PESEL,3,2),'-01')))
    )
    THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
END IF;
END $$

CREATE TRIGGER `ludzie_data_check_before_insert` BEFORE INSERT ON `Ludzie`
FOR EACH ROW
BEGIN
IF NOT(
        check_pesel(NEW.PESEL) AND
        NEW.WZROST > 0.0 AND
        NEW.WAGA > 0.0 AND
        NEW.ROZMIAR_BUTA > 0
    )
THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'check on Ludzie failed during insert';
END IF;
END $$
DELIMITER ;

When I try to input data by

INSERT INTO Ludzie VALUES ('7810396705', 'Anders', 'Kennedy','1958-08-20', 1.70, 88.6, 43)

I get

Error Code: 1109. Unknown table 'new' in field list

I already tried turning mysql server on and off, checked if I'm doing it to the right database. Everything seems to look fine, but it isn't :/

Upvotes: 0

Views: 3994

Answers (2)

Eric F
Eric F

Reputation: 66

Yes, After doing some reading I as well am willing to bet that the "NEW" keyword is not needed in

CHAR_LENGTH(NEW.PESEL).

NEW keyword is used in update and insert commands.

Upvotes: 1

user9048861
user9048861

Reputation:

I think I found your problem:

CHAR_LENGTH(NEW.PESEL) <> 11 AND
CONVERT(PESEL, SIGNED INT) > 0 AND

That is inconsistent usage of NEW.PESEL and PESEL, you should not use the keyword new in a function that does not add anything to the database. In this context, the error recognized NEW as a table instead of a keyword.

Upvotes: 2

Related Questions