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