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
