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
Abd is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
7
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`;
8
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 ;
3