Problem with triggers

MySQL database
#1

I am trying to set up an audit table for changes to a table. I found what I thought was a good tutorial but it gives an error when creating the triggers in phpmyadmin.

The error is

#1235 - This version of MySQL doesn’t yet support ‘multiple triggers with the same action time and event for one table’

I have Googled this and am none the wiser as to how to resolve it as I am a newbie at triggers.

This is the sql that I have

CREATE TRIGGER ai_data AFTER INSERT ON interest
	FOR EACH ROW
	BEGIN
	  INSERT INTO interest_log (action,id,timestamp,member,surname,town,county,country,period)
	  VALUES('insert',NEW.id,NOW(),NEW.member,NEW.surname,NEW.town,NEW.county,NEW.country,NEW.period);
	END$$

	CREATE TRIGGER au_data AFTER INSERT ON interest
	FOR EACH ROW
	BEGIN
	  INSERT INTO interest_log (action,id,timestamp,member,surname,town,county,country,period)
	  VALUES('insert',NEW.id,NOW(),NEW.member,NEW.surname,NEW.town,NEW.county,NEW.country,NEW.period);
	END$$

	CREATE TRIGGER ad_data AFTER INSERT ON interest
	FOR EACH ROW
	BEGIN
	  INSERT INTO interest_log (action,id,timestamp,member,surname,town,county,country,period)
	  VALUES('insert',OLD.id,NOW(),OLD.member,OLD.surname,OLD.town,OLD.county,OLD.country,OLD.period);
	END$$

This creates the first trigger properly and then aborts with the error message above.

Can anyone help in simple language please?

#2

See if this helps

http://www.lemiffe.com/5-solutions-for-mysql-error-1235/

#3

Kevin

Thanks for that link I had found that but it does not give a fix just says this is why you get it and then it isn’t very detailed. Any other suggestions?

#4

Typical lousy tutorial code.

On re-reading everything I could find on triggers and looking at the code again there is a major error in it. All three are trying to do triggers on AFTER INSERT where as they should have been AFTER INSERT, AFTER UPDATE and AFTER DELETE respectively and thinking about it should be BEFORE UPDATE and it should be the OLD values.

So the code should be

CREATE TRIGGER ai_data AFTER INSERT ON interest
  	FOR EACH ROW
  	BEGIN
  	  INSERT INTO interest_log (action,id,timestamp,member,surname,town,county,country,period)
  	  VALUES('insert',NEW.id,NOW(),NEW.member,NEW.surname,NEW.town,NEW.county,NEW.country,NEW.period);
  	END$$
  
  	CREATE TRIGGER au_data BEFORE UPDATE ON interest
  	FOR EACH ROW
 	BEGIN
 	  INSERT INTO interest_log (action,id,timestamp,member,surname,town,county,country,period)

  VALUES('insert',OLD.id,NOW(),OLD.member,OLD.surname,OLD.town,OLD.county,OLD.country,OLD.period);
 	END$$
 
 	CREATE TRIGGER ad_data AFTER DELETE ON interest
 	FOR EACH ROW
 	BEGIN
 	  INSERT INTO interest_log (action,id,timestamp,member,surname,town,county,country,period)
 	  VALUES('insert',OLD.id,NOW(),OLD.member,OLD.surname,OLD.town,OLD.county,OLD.country,OLD.period);
 	END$$

Hope this helps someone else.

Sponsor our Newsletter | Privacy Policy | Terms of Service