CODEWITHSUNDEEP
mysql
Shashank
Shashank

Reputation: 107

combining two columns in to one using multiple line triggers in mysql

The following is my table schema:

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| LicenceID    | int(11)      | NO   | PRI | NULL    |       | 
| PassingRTO   | varchar(4)   | NO   |     | NULL    |       | 
| DLNO         | int(15)      | YES  |     | NULL    |       | 
| DateOfIssue  | date         | NO   |     | NULL    |       | 
| DateOfExpiry | date         | NO   |     | NULL    |       | 
| COV          | varchar(6)   | NO   |     | NULL    |       | 
| DateOfBirth  | date         | NO   |     | NULL    |       | 
| BloodGroup   | varchar(3)   | YES  |     | NULL    |       | 
| FullName     | varchar(50)  | NO   |     | NULL    |       | 
| FathersName  | varchar(50)  | YES  |     | NULL    |       | 
| Address      | varchar(150) | NO   |     | NULL    |       | 
| PinCode      | int(6)       | NO   |     | NULL    |       | 
| IssuingAuth  | int(7)       | NO   |     | NULL    |       | 
| IDIA         | int(11)      | YES  |     | NULL    |       | 
| Valid        | tinyint(4)   | NO   |     | NULL    |       | 
+--------------+--------------+------+-----+---------+-------+

What i want to do is When i insert a new row, i want my DLNO as PassingRTO+LicenceID and IDIA as PassingRTO+IssuingAuth.

I tried the same using - 

create trigger insert_combined 
after insert on LicencesDB 
for each row 
BEGIN 
set new.IDIA = concat(new.PassingRTO, new.IssuingAuth);
set new.DLNO = concat(new.PassingRTO,new.LicenceID); 
END;

but gives me an error as -

ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger ERROR 1193 (HY000): Unknown system variable 'DLNO' ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1

Now I have two questions can we do this using multiple lines in triggers? and can't we combine two columns at the time of creating the table itself? like col1 = col2 + col3?

Thanks in advance!

Upvotes: 3

Views: 2530

Answers (2)

Lakshmi
Lakshmi

Reputation: 2294

Please check this link on mysql triggers. When you want to make an update on the current row being entered you can't use the after clause.

check http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html When you want to use

SET NEW.col_name = value

in your trigger, please note that you CANNOT use this with the AFTER the action, and must use it BEFORE the action.

Therefore, this will work:

CREATE TRIGGER sdata_insert BEFORE INSERT ON `sometable`
FOR EACH ROW
BEGIN
SET NEW.guid = UUID();
END
;

And this will NOT work:

CREATE TRIGGER sdata_insert AFTER INSERT ON `sometable`
FOR EACH ROW
BEGIN
SET NEW.guid = UUID();
END
;

Also you can probably do it in the code itself before even entering into the database.

Upvotes: 2

Pedigree
Pedigree

Reputation: 2604

don;t forget to change the DELIMITER

DELIMITER $$
create trigger insert_combined 
after insert on LicencesDB 
for each row 
BEGIN 
set new.IDIA = concat(new.PassingRTO, new.IssuingAuth);
set new.DLNO = concat(new.PassingRTO,new.LicenceID); 
END $$
DELIMITER ;

Upvotes: 4

Related Questions