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
