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

Instead of trigger you can use a generated column :

CREATE TABLE `concatcolumn`(
  `id` int(10) AUTO_INCREMENT PRIMARY KEY,
  `org` int(10),
  `orgid` TEXT AS (CONCAT(`org`, `id`))
);

INSERT INTO `concatcolumn` (`org`) VALUES (20), (21), (22);

SELECT *
FROM `concatcolumn`;

UPDATE `concatcolumn`
SET `org` = 200
WHERE `id` = 1;

SELECT *
FROM `concatcolumn`;

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 ;