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;