dimanche 18 juillet 2021

Db2 Trigger is triggered even if the condition is doesn't match

I have an update trigger as following which should ideally trigger on the IF statement and should add the column name if the column entry was modified but my trigger executes even if the condition is not fulfilled . Can someone perhaps point out what i am doing wrong here ? screenshot following enter image description here

 CREATE OR REPLACE
TRIGGER test_trg_update AFTER
UPDATE
    ON
    test.person REFERENCING NEW AS N OLD AS O FOR EACH ROW
BEGIN
        
DECLARE change_type VARCHAR(10);

DECLARE modifier VARCHAR(50);

DECLARE change_time TIMESTAMP;

DECLARE changed_columns VARCHAR(1000);

DECLARE column_name VARCHAR(50);

DECLARE changeuser VARCHAR(50);

SET
changed_columns = '';

IF (n.NAME <> o.NAME) THEN SET
changed_columns = CONCAT (changed_columns,
'NAME,');


ELSEIF (n.PASSWORD <> o.PASSWORD) THEN SET
changed_columns = CONCAT (changed_columns,
'PASSWORD,');



ELSEIF (n.USERNAME <> o.USERNAME) THEN SET
changed_columns = CONCAT (changed_columns,
'USERNAME,');

END IF ;


SET
change_type = 'update';

SET
modifier = (
SELECT
    USER
FROM
    SYSIBM.SYSDUMMY1);

SET
change_time = (
VALUES CURRENT_TIMESTAMP);

if modifier= 'db2usr' THEN 

INSERT
    INTO
    Test_changelog (change_number,
    change_type,
    table_name,
    identifier,
    entry_type,
    changed_columns,
    modifiers_name,
    change_time)
VALUES (NEXT VALUE FOR TEST_CHANGELOG_SEQ,
change_type,
'test.person',
CONCAT ('id=',
N.ID),
'account',
changed_columns,
modifier,
change_time);
END IF;
END

Aucun commentaire:

Enregistrer un commentaire