Link to home
Create AccountLog in
Avatar of cescentman
cescentmanFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of jimyX
jimyX

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of cescentman

ASKER

Perfect, many thanks I see now where my error was.
Quick and perfect