Oracle触发器-根据值列表限制可以在字段中输入/更新的内容

Question

我正在尝试了解一些PL/SQL逻辑(我对它没有多少经验！)

一位前同事在一张桌子上创建了一个触发器。此触发器在表的更新之前触发，并基本上检查用户是否在单独表中的授权用户列表中。如果它们在列表中，则允许它们对某一列进行更改。

create or replace TRIGGER "SATURN".UWB_SFAREGS_ENROLL BEFORE UPDATE ON SFBETRM
FOR EACH ROW

DECLARE
  l_user            CHAR(12);
  l_authorised      VARCHAR2(1);
  l_new_saved       VARCHAR2(2);
  l_mailhost        VARCHAR2(24) := 'smtp.xxxx';
  l_from            VARCHAR2(24) := 'xxxx';
  l_to              VARCHAR2(24) := 'xxxx';
  l_mail_conn       UTL_SMTP.connection;
BEGIN
  IF :OLD.SFBETRM_ESTS_CODE <> :NEW.SFBETRM_ESTS_CODE THEN
    l_new_saved := :NEW.SFBETRM_ESTS_CODE;
    l_authorised := 'N';
    SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') into l_user FROM DUAL;
        SELECT
            'Y' INTO l_authorised
        FROM STUMAN.UWB_ALLOW_ENROLL
        WHERE (
            ALLOWED_USER = l_user
            AND QUALIFICATION = '9000'
            AND :NEW.SFBETRM_TERM_CODE like '9%'
        ) OR (
            ALLOWED_USER = l_user
            AND QUALIFICATION IS NULL
        );
     IF l_authorised = 'N' THEN
        :NEW.SFBETRM_ESTS_CODE := :OLD.SFBETRM_ESTS_CODE;
    END IF;
  END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN :NEW.SFBETRM_ESTS_CODE := :OLD.SFBETRM_ESTS_CODE;
        INSERT INTO SFBETRM_LOG
        (
            TERM,
            PIDM,
            OLD_ESTS,
            NEW_ESTS,
            CHANGE_DATE,
            CHANGED_BY,
            ALLOWED
        )
        VALUES
        (
            :old.SFBETRM_TERM_CODE,
            :old.SFBETRM_PIDM,
            :old.SFBETRM_ESTS_CODE,
            :new.SFBETRM_ESTS_CODE,
            SYSDATE,
            l_user,
            'N'
        );
        l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
        UTL_SMTP.helo(l_mail_conn, l_mailhost);
        UTL_SMTP.mail(l_mail_conn, l_from);
        UTL_SMTP.rcpt(l_mail_conn, l_to);
        UTL_SMTP.data(l_mail_conn, l_user || ' tried to change SFAREGS enrollment status from ' || :OLD.SFBETRM_ESTS_CODE || ' to ' || l_new_saved || ' for PIDM ' || :NEW.SFBETRM_PIDM || ' l_authorised = ' || l_authorised || Chr(13));
        UTL_SMTP.quit(l_mail_conn);
END UWB_SFAREGS_ENROLL;

我想做的是进一步限制这种“身份验证”，并限制某些用户能够根据值列表将SFBETRM_ESTS_CODE字段更改为特定的“代码”。

如果在SFBETRM_ESTS_CODE上选择distinct，我会得到以下值：

--------
RE
IS
RS
NS
RT
ER
SR
TR
RF
EX
EZ
EL

以上是我列出的不同的代码。

现在，我的授权用户表实际上只包含了他们的用户名。我想进一步扩展它，以包括一个列，其中包含他们可以更改的代码，然后在触发器中包含这个条件，例如：

"USER1" "EL, EZ"
"USER2" "EL, EZ, RX"
"USER3" "EL, ER"
"USER4" "RF, RE"

希望这是有意义的(至少对某人！)知道我该怎么做吗？

提亚

哈士奇

Answer 1

您必须使用姓名‘)，尝试使用以下内容：

CREATE TRIGGER ...
BEFORE UPDATE
...
DECLARE
  l_exists number(10);
  ...
BEGIN
  ...
  WHEN UPDATING ('SFBETRM_ESTS_CODE') THEN
    SELECT COUNT(*)
      INTO l_exists
      FROM DUAL
     WHERE EXISTS (SELECT null FROM SFBETRM_ESTS_CODE WHERE code = :new.SFBETRM_ESTS_CODE)
    ;
    IF l_exists = 1
    THEN
      ... -- perform the update operation
    ELSE
      ... -- ignore update operation
    END IF;
    ...
END;

如果l_exists值存储在SFBETRM_ESTS_CODE表中，则为1；如果SFBETRM_ESTS_CODE表没有:new.SFBETRM_ESTS_CODE值，则为0

P.S:根据你的需要调整它