NULL value when insering row with trigger SQL and CONCAT function

Question

I put 10,000 avatar photos on a server and I would like for each row inserted into the 'studenttable' table, the 'photo' column to be the concatenation of the url of the folder of my photos + the id of the inserted student.

However, the CONCAT function returns a NULL value with the basic trigger used.

First, here is the above mentioned table :

CREATE TABLE `studenttable` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `gender` enum('Male','Female','Other') NOT NULL,
 `email` varchar(100) DEFAULT NULL,
 `birthDate` date DEFAULT NULL,
 `photo` varchar(535) DEFAULT NULL,
 `mark` double DEFAULT NULL,
 `comment` varchar(535) DEFAULT NULL,
 PRIMARY KEY (`id`)
)

and here is the basic trigger I created:

DELIMITER $$
create trigger IMAGE_LienApi
before insert on studenttable
for each row 
begin
  set NEW.photo = CONCAT('https://url-of-folder-with-my-images/',NEW.id,'.png');
end$$
DELIMITER ;

For information, the images are referenced in this way: number.png

So when I insert a new student with this trigger, the photo column is always set to NULL. The problem must come from NEW.id, because when I replace this value with a string, it works.

I also tried with

NEW.photo = 'https://url-of-folder-with-my-images/' + CONVERT(VARCHAR(5),NEW.id),'.png';

but it did not work

Thank you in advance for your help and if someone could explain to me especially why the CONCAT does not work, that would be great !

new.id is not available in a before insert trigger

P.Salmon
– P.Salmon

2022-11-10 16:08:44 +00:00
Commented Nov 10, 2022 at 16:08 — P.Salmon
– P.Salmon, Commented Nov 10, 2022 at 16:08

Bill Karwin · Accepted Answer · 2022-11-10 18:27:49Z

1

CONCAT() returns NULL if any of its arguments are NULL.

In a BEFORE INSERT trigger, the NEW.id value is NULL. It hasn't been generated yet.

But in an AFTER INSERT trigger, it's too late to change the NEW.photo column of your row. You can't change columns in an AFTER trigger.

You cannot make a BEFORE INSERT trigger to concatenate an auto-increment value with other columns. The best you can do is let the INSERT complete, and then subsequently do an UPDATE to change your photo column.

The alternative is to forget about using the builtin AUTO_INCREMENT to generate id values, instead generate them some other way and specify the value in your INSERT statement.

answered Nov 10, 2022 at 18:27

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cowbra Over a year ago

Oh ok thanks i understood.... I'm going to try to do that directly in the code then by retrieving each time the last row entered and performing an update

Collectives™ on Stack Overflow

NULL value when insering row with trigger SQL and CONCAT function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related