ERROR 1235 (42000): This version of MySQL doesn"t yet support"multiple triggers with the same action
am 09.12.2009 06:39:33 von jeetendra.ranjan
------=_NextPart_000_006A_01CA78C0.16366D00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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.
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 =20
begin=20
INSERT INTO =
DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERP RISE_URL_ID,C=
ONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAM E,DATE_OF_BIR=
TH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_ PATH,STATUS,S=
KIP_STEP,LAST_MOD_TIME,LAST_MOD_BY )=20
VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_U RL_ID,old.CON=
SUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LA ST_NAME,old.D=
ATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,ol d.LOCALE_ID,o=
ld.PROFILE_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 =20
begin=20
INSERT INTO =
DELETED_AUDIT_REPOSITORY(AUDIT_ENTRY_ID,AUDIT_ATTRIBUTE_ID,A FFECTED_CONSU=
MER_ID,AFFECTED_INTEREST_ID,ACTION_ID,OLD_VALUE_ID,OLD_VALUE ,NEW_VALUE_ID=
,NEW_VALUE,IS_ACTIVE,LAST_MOD_TIME,LAST_MOD_BY )=20
VALUES(old.AUDIT_ENTRY_ID,old.AUDIT_ATTRIBUTE_ID,old.AFFECTE D_CONSUMER_ID=
,old.AFFECTED_INTEREST_ID,old.ACTION_ID,old.OLD_VALUE_ID,old .OLD_VALUE,ol=
d.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
------=_NextPart_000_006A_01CA78C0.16366D00--
Re: ERROR 1235 (42000): This version of MySQL doesn"t yet support "multiple triggers with the same a
am 09.12.2009 06:46:05 von Jesper Wisborg Krogh
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 w=
ith=20
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=20
CONSUMER_PROFILE FOR EACH ROW =20
begin=20
INSERT INTO=20
DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERP RISE_URL_ID,CON=
SUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME, DATE_OF_BIRTH,G=
ENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH ,STATUS,SKIP_ST=
EP,LAST_MOD_TIME,LAST_MOD_BY )=20
VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_U RL_ID,old.CONSU=
MER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST _NAME,old.DATE_=
OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LO CALE_ID,old.PRO=
=46ILE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME ,old.LAST_MOD_B=
Y );=20
INSERT INTO=20
DELETED_AUDIT_REPOSITORY(AUDIT_ENTRY_ID,AUDIT_ATTRIBUTE_ID,A FFECTED_CONSUME=
R_ID,AFFECTED_INTEREST_ID,ACTION_ID,OLD_VALUE_ID,OLD_VALUE,N EW_VALUE_ID,NEW=
_VALUE,IS_ACTIVE,LAST_MOD_TIME,LAST_MOD_BY )=20
VALUES(old.AUDIT_ENTRY_ID,old.AUDIT_ATTRIBUTE_ID,old.AFFECTE D_CONSUMER_ID,o=
ld.AFFECTED_INTEREST_ID,old.ACTION_ID,old.OLD_VALUE_ID,old.O LD_VALUE,old.NE=
W_VALUE_ID,old.NEW_VALUE,old.IS_ACTIVE,old.LAST_MOD_TIME,old .LAST_MOD_BY );
END//
=2D 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,ENTERP RISE_URL_ID,C=
ON
>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_ST=
EP
>,LAST_MOD_TIME,LAST_MOD_BY )
> VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_U RL_ID,old.CON=
SU
>MER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAS T_NAME,old.DATE=
_O
>F_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LO CALE_ID,old.PRO=
=46I
>LE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,ol d.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,A FFECTED_CONSU=
ME
>R_ID,AFFECTED_INTEREST_ID,ACTION_ID,OLD_VALUE_ID,OLD_VALUE, NEW_VALUE_ID,NE=
W_
>VALUE,IS_ACTIVE,LAST_MOD_TIME,LAST_MOD_BY )
> VALUES(old.AUDIT_ENTRY_ID,old.AUDIT_ATTRIBUTE_ID,old.AFFECTE D_CONSUMER_ID=
,o
>ld.AFFECTED_INTEREST_ID,old.ACTION_ID,old.OLD_VALUE_ID,old. OLD_VALUE,old.N=
EW
>_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
=2D-=20
Jesper Wisborg Krogh
Team Lead
Noggin IT â=A2 Level 8, 28 Foveaux St, Surry Hills NSW Australia 2010
T: +61 2 9219 8800 â=A2 F: +61 2 9280 4669 â=A2 E: jesper@nog=
gin.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=3Dgcdmg-mysql-2@m.gmane.o rg