CODEWITHSUNDEEP
mysqlsqltriggersconcatenation
cowbra
cowbra

Reputation: 23

NULL value when insering row with trigger SQL and CONCAT function

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 !

Upvotes: 1

Views: 71

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562701

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.

Upvotes: 1

Related Questions