MySQL BEFORE INSERT trigger with IF conditions

MySQLmysql-5.6trigger

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.

Best Answer

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;
Related Question