Member Avatar for axes2t2

First the table

create table customer(
no int NOT NULL auto_increment,
cust_id varchar(15) NOT NULL,
cust_name varchar(50),
cust_address text(150),
cust_phone int,
cust_email varchar(30),
PRIMARY KEY(no));

Then the trigger

DELIMITER |

CREATE TRIGGER trig BEFORE insert on customer
FOR EACH ROW BEGIN 
set new.cust_id=concat(new.cust_name,new.no);
END;

DELIMITER |

Then the insert query

insert into customer (cust_id,cust_name,cust_address,cust_phone,cust_email) values (' ','S','G(W)','222222','a@c.com');

Then the output

1	S0	S	G(W)	222222	a@c.com

Now the cust_id i.e column two should be S1 but it isn't.

Anyone knows why?

  • 2 Contributors
  • 1 Reply
  • 122 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
smantscheff commented: Started with a complete test case right away. +1

Dani AI

Generated

Short diagnosis and practical fixes (what happened and how to fix it)

The observed result (cust_id ending up with a zero suffix) happens because the auto-increment value is not available when the BEFORE INSERT trigger runs, so concatenating it there yields the default/zero value. was right to suspect timing, but a straight AFTER INSERT trigger is not a drop-in fix: AFTER triggers see the assigned auto-inc, but you cannot change NEW.* in AFTER triggers, and attempting to run an UPDATE on the same table from inside the trigger will raise MySQL’s restriction against modifying the triggering table.

Recommended approaches (pick one that fits your environment)

  • Best modern solution: use a generated column so the DB computes the value automatically and safely. Example (MySQL 5.7.6+):
ALTER TABLE customer
  DROP COLUMN cust_id,
  ADD COLUMN cust_id VARCHAR(64) GENERATED ALWAYS AS (CONCAT(cust_name, no)) STORED;
  • If you cannot use generated columns: set the field from the application after INSERT. Insert the row, read the assigned id with LAST_INSERT_ID(), then run an UPDATE using that id. This avoids trigger restrictions and is safe per-connection.
INSERT INTO customer (cust_name, ...) VALUES (...);
SET @id = LAST_INSERT_ID();
UPDATE customer SET cust_id = CONCAT(cust_name, @id) WHERE no = @id;
  • Lightweight alternative: don’t store the derived value at all. Compute it on SELECT:
SELECT no, cust_name, CONCAT(cust_name, no) AS cust_id FROM customer;

Notes and cautions

Avoid hacks that read next AUTO_INCREMENT from INFORMATION_SCHEMA unless you also lock the table; that approach has race conditions. Also reconsider storing derived data (duplicate storage) unless you need it for indexing or performance—generated columns let you index the derived value without risking inconsistency.

Member Avatar for smantscheff

I assume that the auto_increment field is filled by the system AFTER the insert.
You could instead have an update trigger and bracket the insertion of a new customer with an update statement on the row just inserted into a transaction.
Also I'd like to ask if it really makes sense to duplicate the contents of two fields in a third field?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.