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;