CODEWITHSUNDEEP
mysqltriggers
Deepak
Deepak

Reputation: 2905

After update trigger not working accordingly if null values in column

I am creating a trigger like this

CREATE TRIGGER trigger_academictermregistration AFTER UPDATE ON academictermregistration FOR EACH ROW
BEGIN
    DECLARE summary TEXT(65535);
    SET summary = '';
    IF(NEW.version <> OLD.version) THEN
        SET summary = concat(summary, '    @VER: ', OLD.version, '-->', NEW.version);
    END IF;
    IF(NEW.isDeleted <> OLD.isDeleted) THEN
        SET summary = concat(summary, '    @IS_DELETED: ', OLD.isDeleted, '-->', NEW.isDeleted);
    END IF;

    INSERT INTO auditinfo (entityId, entityName, importance, details, modifiedOn, modifiedBy)
        VALUE (NEW.id, 'AcademicTermRegistration', 1, summary, NOW(), NEW.modifiedBy);

    IF(NEW.id <> OLD.id) THEN
        INSERT INTO auditinfo (entityId, entityName, importance, details, timestamp, user_id)
        VALUE (OLD.id, 'AcademicTermRegistration', 5, concat('ID: ', OLD.id, '-->', NEW.id), NOW(), 'backend');
    END IF;
END

I have a trigger like this and this is tracking the changes made in column values after updating the table but if any of the values are null (say isDeleted ) then afte updating that value to not null No details are there in auditinfo table

Upvotes: 1

Views: 646

Answers (1)

Devart
Devart

Reputation: 122002

From the reference - CONCAT() returns NULL if any argument is NULL.

Use a COALESCE function, e.g. - 

SET summary = CONCAT(summary, '    @IS_DELETED: ', COALESCE(OLD.isDeleted, 'NULL'), '-->', COALESCE(NEW.isDeleted, 'NULL'));

Also, you can use a CONCAT_WS() function, it skips any NULL values after the separator argument -

SET summary = CONCAT_WS('', summary, '    @IS_DELETED: ', OLD.isDeleted, '-->', NEW.isDeleted);

Upvotes: 1

Related Questions