concat two column in mysql , one of them is an auto-increments, using trigger

Dears, please below is my first trigger to make a column as a concat a two column .. but it is not working ?! can i know where is the error

create table `concatcolumn`(
    `id` int(10) AUTO_INCREMENT PRIMARY key,
    `org` int(10),
    `orgid` int(20) -- this will be a concat column `org` and `id`);

delimiter $$
create trigger concatcolumn_after_insert
 AFTER insert on `concatcolumn`
  for each row
   begin
    set new.orgid = concat(new.org,new.id)
   end $$
delimiter ;



delimiter $$
create trigger concatcolumn_after_update
 AFTER update on `concatcolumn`
  for each row
   begin
    set new.orgid = concat(new.org,new.id)
   end $$
delimiter ;

enter image description here

even when I replace AFTER with BEFORE to be

delimiter $$
 create trigger concatcolumn_after_insert
  before insert on `concatcolumn`
   for each row
    begin
     set new.orgid = concat(new.org,new.id);
    end $$;
delimiter ;

I always get the id = 0; enter image description here

any help please ?

filling the column orgid automatically by concatenate the org and id

regards

Solution

Another option would be using two triggers. The insert trigger would get the increment value from the information_schema.tables.

Trigger 1.

DELIMITER &&

CREATE TRIGGER before_insert_concatcolumn BEFORE INSERT ON concatcolumn
 FOR EACH ROW
  BEGIN

   DECLARE autoId int;

   SElECT  auto_increment into autoId
   FROM information_schema.tables
   WHERE table_schema = database() and table_name = 'concatcolumn';

   SET NEW.orgid = concat(autoId , NEW.org );
 END

DELIMITER ;

Since you cant update same table in a function/trigger you need to get the auto_increment value from information_schema.tables.

MariaDB [test]> SElECT  auto_increment  FROM information_schema.tables WHERE table_schema = database() and table_name = 'concatcolumn';
+----------------+
| auto_increment |
+----------------+
|              4 |
+----------------+
1 row in set (0.000 sec)

I have declared autoId to save the auto_increment value which will bne used later on the set statement.

Trigger 2.

DELIMITER &&

 CREATE TRIGGER `before_update_concatcolumn` BEFORE UPDATE ON concatcolumn
  FOR EACH ROW
    BEGIN
     SET NEW.orgid = ( SELECT concat( NEW.id, NEW.org) FROM concatcolumn WHERE id = NEW.id )  ;
    END&&

DELIMITER ;