解码函数中的多个条件插入表中的值

Question

我有下面的触发器，我想在FIELD_TRACKING表中插入值。 我有FIELD_TRACKING_COMMENTS字段，我想根据条件插入值。条件是如果(Last_Updated_By = 115 and new.KPI_ACTIVE_MANUAL,N)那么评论应该是This KPI has been deactivated by System -> Active Alerts。我已经在解码声明decode(:new.KPI_ACTIVE_MANUAL,'N','This KPI has been deactivated by System -> Active Alerts',''))中为FIELD_TRACKING_COMMENTS字段添加了条件，并且它的工作正常。但我想添加额外的条件'Last_Updated_By = 115'，我不知道如何将它添加到解码语句中。

create or replace TRIGGER RATOR_MONITORING_CONFIGURATION."TRG_TRK_KPI_DEFINITION" AFTER UPDATE ON RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION 
    FOR EACH ROW 
BEGIN 
    ----New change 
    --IF NOT :old.KPI_ACTIVE_MANUAL=:new.KPI_ACTIVE_MANUAL THEN 
    IF NOT NVL(:old.KPI_ACTIVE_MANUAL,0)=NVL(:new.KPI_ACTIVE_MANUAL,0) THEN 
    INSERT INTO RATOR_MONITORING_CONFIGURATION.FIELD_TRACKING (TABLE_ID, FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,USER_ID, FIELD_TRACKING_COMMENTS) 
    VALUES (:new.KPI_DEF_ID,'Active(Manual)',NVL(to_char(:old.KPI_ACTIVE_MANUAL),'Y'),NVL(to_char(:new.KPI_ACTIVE_MANUAL),'Y'),:new.LAST_UPDATED_BY,decode(:new.KPI_ACTIVE_MANUAL,'N','This KPI has been deactivated by System -> Active Alerts','')); 
    END IF; 

END; 

Answer 1

你可以使用一个嵌套decode：

decode(:new.KPI_ACTIVE_MANUAL,'N', 
    decode(:new.Last_Updated_By, 115, 
    'This KPI has been deactivated by System -> Active Alerts', 
    ''), 
    '') 

但它的简单使用case表达式来代替：

INSERT INTO RATOR_MONITORING_CONFIGURATION.FIELD_TRACKING (TABLE_ID, 
    FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,USER_ID, FIELD_TRACKING_COMMENTS) 
VALUES (:new.KPI_DEF_ID, 'Active(Manual)', 
    NVL(to_char(:old.KPI_ACTIVE_MANUAL),'Y'), 
    NVL(to_char(:new.KPI_ACTIVE_MANUAL),'Y'), 
    :new.LAST_UPDATED_BY, 
    case when :new.KPI_ACTIVE_MANUAL = 'N' AND :new.Last_Updated_By = 115 
    then 'This KPI has been deactivated by System -> Active Alerts' 
    else '' end 
); 

虽然persionally我会使用null代替''，和您可以省略else null子句，因为这是默认设置。看起来奇怪的是，您的外部IF条件将NVL的旗标值设置为0，因为它似乎是Y/N的字符列。