MySQL BEFORE INSERT trigger with IF conditions

Question

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.

• András Váczi: NumberHP = null will be never true as nothing is equal to NULL
• Andriy M: 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.

Answer 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;

• Tri Susanti: 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'
• oNare: 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
• Tri Susanti: I still got that warning
• oNare: Can you edit your question and add what you're running? Your complete syntax please.
• Tri Susanti: 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