Обновление Oracle с предложением Case When Exists

Мне нужен триггер для обновления таблицы DIRECTORY_NUMBER, когда одно значение столбца DN_NUM совпадает со значением столбца MSISDN другой таблицы (RNPH_REQUETS_DETAILS ) по другой схеме (NKADM). Триггер будет запускаться каждый раз, когда в таблице DIRECTORY_NUMBER появляется новая запись. В зависимости от нескольких условий значения столбца DN_STATUS и нескольких других столбцов необходимо обновить. Обновленное значение столбца DN_STATUS будет "r", если условия выполнены, и "w", если условия не выполнены. Активная часть моего кода приведена ниже:

UPDATE d 
  SET d.DN_STATUS = CASE WHEN EXISTS (SELECT 1 from NKADM.RNPH_REQUESTS_DETAILS n where n.MSISDN = d.DN_NUM AND n.PROCESS_STATE_ID = 4 AND n.ACTION='IN' AND n.FAILED_STATUS IS NULL AND TRUNC(n.MODIFICATION_DATE) = TRUNC(SYSDATE))
                      THEN 'r' 
                      ELSE 'w'
                      END,
    d.DN_MODDATE = SYSDATE,
    d.BUSINESS_UNIT_ID = 2,
    d.HLCODE = 5
  WHERE d.DN_ID =: NEW.DN_ID
  AND d.PLCODE = 1004
  AND d.DN_STATUS = 'f'
  FROM DIRECTORY_NUMBER d;

Я получаю следующую ошибку:

Ошибка (48,1): PL/SQL: оператор SQL игнорируется

Ошибка (60,3): PL/SQL: ORA-00933: команда SQL завершилась неправильно

Ошибки устраняются, только если я удаляю ссылки. Но это дает другой результат, чем предполагалось. Когда код выглядит следующим образом:

UPDATE DIRECTORY_NUMBER 
  SET DN_STATUS     = CASE WHEN EXISTS (SELECT 1 from NKADM.RNPH_REQUESTS_DETAILS where MSISDN = DN_NUM AND PROCESS_STATE_ID = 4 
                                        AND ACTION='IN' AND FAILED_STATUS IS NULL AND TRUNC(MODIFICATION_DATE) = TRUNC(SYSDATE))
                      THEN 'r' 
                      ELSE 'w'
                      END,
    DN_MODDATE      =SYSDATE,
    BUSINESS_UNIT_ID=2,
    HLCODE          =5
  WHERE DN_ID       =:NEW.DN_ID
  AND PLCODE        =1004
  AND DN_STATUS     ='f';

  COMMIT;

Даже если условие CASE WHEN EXISTS истинно (возвращает результат при независимом запуске), значение DN_STATUS обновляется до 'w'.

Обновление: я пробовал со следующим кодом:

UPDATE DIRECTORY_NUMBER
    SET DN_STATUS     = 'r',
    DN_MODDATE      =SYSDATE,
    BUSINESS_UNIT_ID=2,
    HLCODE          =5
  WHERE DN_ID       =:NEW.DN_ID
  AND PLCODE        =1004
  AND DN_STATUS     ='f';
  AND DN_NUM in (select MSISDN from NKADM.RNPH_PROCESS_DETAILS where PROCESS_STATE_ID = 4);

Это тоже не работает. Если я удалю последнее условие, результирующая строка будет иметь значение DN_STATUS «f», а MSISDN находится в таблице NKADM.RNPH_PROCESS_DETAILS с PROCESS_STATE_ID = 4. Я не понимаю, почему это не работает.

Что я делаю не так?


sql-update case-when exists
person Avi Bis    schedule 27.08.2018    source источник

Ответы (1)


arrow_upward
1
arrow_downward

В триггере BEFORE update/insert для КАЖДОЙ СТРОКИ вы можете изменить данные записи, которая обрабатывается в данный момент. Вам не нужно вызывать дополнительное ОБНОВЛЕНИЕ для изменения данных.

Другими словами, вы можете сделать что-то вроде этого

IF :NEW.PLCODE = 1004 AND :NEW.DN_STATUS = 'f' THEN

  :NEW.DN_MODDATE       := SYSDATE;
  :NEW.BUSINESS_UNIT_ID := 2;
  :NEW.HLCODE           := 5;

  -- this query you can wrap in a function and call this function
  SELECT COUNT(1) 
    INTO lv_count
    FROM NKADM.RNPH_REQUESTS_DETAILS n 
   WHERE n.MSISDN = :NEW.DN_NUM 
     AND n.PROCESS_STATE_ID = 4 
     AND n.ACTION = 'IN' 
     AND n.FAILED_STATUS IS NULL 
     AND TRUNC(n.MODIFICATION_DATE) = TRUNC(SYSDATE);

  IF lv_count > 0 THEN
    :NEW.DN_STATUS := 'r';
  ELSE
    :NEW.DN_STATUS := 'w';
  END IF;   

END IF;
person RGruca    schedule 28.08.2018