2

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?

Votes + Comments
Started with a complete test case right away.
2
Contributors
1
Reply
3
Views
6 Years
Discussion Span
Last Post by smantscheff
0

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?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.