# IF statement: Comparision for NULL

**URL:** <https://forums.percona.com/t/if-statement-comparision-for-null/921>\
**Category:** Other MySQL® Questions\
**Created:** [September 11, 2008, 12:31pm UTC](https://forums.percona.com/t/if-statement-comparision-for-null/921 "2008-09-11T12:31:24Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![viswapsp](https://avatars.discourse-cdn.com/v4/letter/v/2acd7d/32.png) [@viswapsp](https://forums.percona.com/u/viswapsp)\
**Post date:** [September 11, 2008, 12:31pm UTC](https://forums.percona.com/t/if-statement-comparision-for-null/921/1 "2008-09-11T12:31:24Z")

</div>

I have a question on If statement while comparision with NULL value.

I have a trigger on a table which fires before insert for each row. In that trigger I am setting the default values for inserted date, inserted by etc. All that I am doing is to check if the passed value is null and if it is null then set it to a default value. However ELSE part of the statement is being executed. Can some body help me to identify what the issue is.

I am inserting a row thru PHPMyAdmin and below is the insert statement generated.

INSERT INTO `generic`.`sequence_generator` (  
`unq_id` ,  
`tbl_nm` ,  
`max_value_plus_one` ,  
`effctv_dttm` ,  
`trmntd_dttm` ,  
`insrtd_by` ,  
`insrtd_dttm` ,  
`last_updtd_by` ,  
`last_updtd_dttm`  
)  
VALUES (  
‘’, ‘mmm’, ‘1’, ‘’, NULL , ‘’, ‘’, ‘’, ‘’  
);

The part that is not working. This is extracted from the trigger.

if new.effctv\_dttm is null  
or new.effctv\_dttm = ‘’  
or length(new.effctv\_dttm) = 0  
OR length(new.effctv\_dttm) = NULL  
then  
set new.effctv\_dttm = current\_timestamp;  
else  
set new.effctv\_dttm = ‘2000-01-01 00:00:00’;  
end if;

---

<div class="post-metadata">

**Author:** ![istvan.podor](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/istvan.podor/32/16879_2.png) [@istvan.podor](https://forums.percona.com/u/istvan.podor)\
**Post date:** [May 8, 2010, 10:05am UTC](https://forums.percona.com/t/if-statement-comparision-for-null/921/2 "2010-05-08T10:05:55Z")

</div>

Hi,

Did you tried calling current\_timestamp with () at the end? )

Istvan

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [May 8, 2010, 12:07pm UTC](https://forums.percona.com/t/if-statement-comparision-for-null/921/3 "2010-05-08T12:07:32Z")

</div>

correct syntax is ‘IS NULL’.

---

<div class="post-metadata">

**Author:** ![Troy](https://avatars.discourse-cdn.com/v4/letter/t/85f322/32.png) [@Troy](https://forums.percona.com/u/Troy)\
**Post date:** [May 10, 2010, 9:00am UTC](https://forums.percona.com/t/if-statement-comparision-for-null/921/4 "2010-05-10T09:00:40Z")

</div>

| [B]istvan.podor wrote on Sat, 08 May 2010 11:35[/B] |
| Hi,

Did you tried calling current\_timestamp with () at the end? )

Istvan

 |

I agree - this part is incorrect: OR length(new.effctv\_dttm) = NULL

Troy