2

In my BEFORE INSERT trigger, I select data, then I check the value if it's null then I do insert data into table A then else into table B. I select data from a table in another database called ebsms.inbox then insert to dbsmsra_pit2.smsra_sms and dbsmsra_pit2.smsra_trash

Here's my complete code :

DROP TRIGGER IF EXIST dbsmsra_pit2.smsra_sms.filterNoHP;
DELIMITER $$
CREATE TRIGGER `filterNoHP`
BEFORE INSERT ON smsra_sms FOR EACH ROW

BEGIN

DECLARE IDx int(11);
DECLARE NumberHP varchar(20);
DECLARE WaktuKirim timestamp;
DECLARE IsiSMS text;

SELECT ID, SenderNumber, ReceivingDateTime,
TextDecoded into IDx, NumberHP, WaktuKirim, IsiSMS FROM ebsms.inbox
WHERE NumberHP in (SELECT NoHP FROM smsra_blokir_number)
and DATE(ReceivingDateTime) = curdate();

IF NumberHP = null then
insert into smsra_sms
set idsms = IDx, SendingDateTime = WaktuKirim,
isi = IsiSMS, pengirim = NumberHP
ON duplicate key update idsms = idsms + IDx;

ELSE
insert into smsra_trash
set Nohp = NumberHP
ON duplicate key update Nohp = Nohp + NumberHP;

END IF;

END$$

DELIMITER ;

here's the error message :

Error Code: 1235. This version of MySQL doesn't yet support
'multiple triggers with the same action time and event for one table'

Please I need help.

  • NumberHP = null will be never true as nothing is equal to NULL – dezso Aug 31 '15 at 10:40
  • Your trigger is BEFORE INSERT ON smsra_sms. So it fires when an INSERT INTO smsra_sms statement is run. Then why does the trigger also insert into smsra_sms? Also, it appears your trigger doesn't really depend on what is being inserted by the triggering statement – that doesn't make much sense either. Normally I would expect to find references to NEW.whatever in such triggers. – Andriy M Sep 1 '15 at 6:54
1

As @dezso says, you can't compare NULL with arithmetic comparison operators as described in the MySQL's documentation.

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL. To demonstrate this for yourself, try the following query:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

Edit this part:

IF NumberHP IS null then # <-- IS replaced =
    insert into smsra_sms
    set idsms = IDx, SendingDateTime = WaktuKirim,
    isi = IsiSMS, pengirim = NumberHP
    ON duplicate key update idsms = idsms + IDx;
ELSE
    insert into smsra_trash
    set Nohp = NumberHP
    ON duplicate key update Nohp = Nohp + NumberHP;
END IF;
  • I got warning, it says : Error Code: 1235. This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' – Tri Susanti Sep 1 '15 at 1:50
  • That is because you're trying to add one trigger that already has this action in that table, add before the DELIMITER $$ of the trigger: DROP TRIGGER IF EXISTS database.filterNoHP;. dev.mysql.com/doc/refman/5.6/en/drop-trigger.html – oNare Sep 1 '15 at 1:52
  • I still got that warning – Tri Susanti Sep 1 '15 at 1:56
  • Can you edit your question and add what you're running? Your complete syntax please. – oNare Sep 1 '15 at 1:57
  • I already get what's wrong with my trigger, I had a trigger with same action but with different name. But, my trigger still didn't insert any data into the tables I need – Tri Susanti Sep 1 '15 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.