More On MySQL Triggers

I have a table `cl35-deal`.`je_PGID`:-

CREATE TABLE `je_parties` (
  `PID` int(4) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `PGID` varchar(256) DEFAULT NULL COMMENT 'Global ID',
  `PFullName` varchar(256) DEFAULT NULL COMMENT 'Full Name',
  `PFriendlName` varchar(256) DEFAULT NULL COMMENT 'Name to use for this item',
  PRIMARY KEY (`PID`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

Open in new window


On inserting a record I want to trigger an update so that PGID is set to the PID prefixed with the text 'je_'.

I have tried a number of variations on a trigger to do this, eg:-

DELIMITER $$
	CREATE TRIGGER `cl35-deal`.`je_PGID` 
	AFTER INSERT ON `cl35-deal`.`je_parties`
	FOR EACH ROW BEGIN
		UPDATE `cl35-deal`.`je_parties` SET PGID = CONCAT('je_',PID);
	END$$
DELIMITER ;

Open in new window


I can't get any of them to work most give me an error to do with the SQL syntax all of which I understand. The trigger above (the one I expected to work) gives me an error:-

"Error Code: 1442
Can't update table 'je_parties' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."

I would appreciate help in understanding this message and in getting the trigger to work.
LVL 1
cescentmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jimyXCommented:
You can not update the table that you are inserting to. And if you thought about "Before Insert" you hit the Auto_Increment not assigned.
Instead, work on the data from the INFORMATION_SCHEMA:
DELIMITER $$
	CREATE TRIGGER `cl35-deal`.`je_PGID` 
 	Before INSERT ON `cl35-deal`.`je_parties`
	FOR EACH ROW BEGIN
		SET New.PGID = CONCAT('je_', (SELECT Auto_Increment FROM INFORMATION_SCHEMA.`TABLES` where TABLE_SCHEMA = 'cl35-deal' and table_Name='je_parties'));
	END$$
DELIMITER ;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cescentmanAuthor Commented:
Perfect, many thanks I see now where my error was.
cescentmanAuthor Commented:
Quick and perfect
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.