On Wed, 9 Dec 2009 16:39:33 Jeetendra Ranjan wrote: > Hi, > > I just would like to know which version of MySQL support the creation of > database triggers with the same action and event for one table.
I'm not sure there is any plan to implement support for multiple triggers with the same action and timing. In your case, the solution is simply to combine the two: CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); INSERT INTO DELETED_AUDIT_REPOSITORY(AUDIT_ENTRY_ID,AUDIT_ATTRIBUTE_ID,AFFECTED_CONSUMER_ID,AFFECTED_INTEREST_ID,ACTION_ID,OLD_VALUE_ID,OLD_VALUE,NEW_VALUE_ID,NEW_VALUE,IS_ACTIVE,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.AUDIT_ENTRY_ID,old.AUDIT_ATTRIBUTE_ID,old.AFFECTED_CONSUMER_ID,old.AFFECTED_INTEREST_ID,old.ACTION_ID,old.OLD_VALUE_ID,old.OLD_VALUE,old.NEW_VALUE_ID,old.NEW_VALUE,old.IS_ACTIVE,old.LAST_MOD_TIME,old.LAST_MOD_BY ); END// - Jesper > > Because if i create the the triggers as below i am getting error. > > First trigger (Succesfull) > ++++++++++ > CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON > CONSUMER_PROFILE FOR EACH ROW begin > INSERT INTO > DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CON >SUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GE >NDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP >,LAST_MOD_TIME,LAST_MOD_BY ) > VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSU >MER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_O >F_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFI >LE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); > END > > Second trigger (generating error) > +++++++++++++++++++++++ > CREATE TRIGGER AUDIT_REPOSITORY_before_delete BEFORE DELETE ON > CONSUMER_PROFILE FOR EACH ROW begin > INSERT INTO > DELETED_AUDIT_REPOSITORY(AUDIT_ENTRY_ID,AUDIT_ATTRIBUTE_ID,AFFECTED_CONSUME >R_ID,AFFECTED_INTEREST_ID,ACTION_ID,OLD_VALUE_ID,OLD_VALUE,NEW_VALUE_ID,NEW_ >VALUE,IS_ACTIVE,LAST_MOD_TIME,LAST_MOD_BY ) > VALUES(old.AUDIT_ENTRY_ID,old.AUDIT_ATTRIBUTE_ID,old.AFFECTED_CONSUMER_ID,o >ld.AFFECTED_INTEREST_ID,old.ACTION_ID,old.OLD_VALUE_ID,old.OLD_VALUE,old.NEW >_VALUE_ID,old.NEW_VALUE,old.IS_ACTIVE,old.LAST_MOD_TIME,old.LAST_MOD_BY > );END -> // > ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple > triggers with the same action time and event for one table' > > Or any other alternate to do the same ? > > > Thanks & Regards > Jeetendra Ranjan -- Jesper Wisborg Krogh Team Lead Noggin IT • Level 8, 28 Foveaux St, Surry Hills NSW Australia 2010 T: +61 2 9219 8800 • F: +61 2 9280 4669 • E: jes...@noggin.com.au http://www.noggin.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
