oracle sql中的语句

发布于 2025-02-13 18:39:23 字数 470 浏览 0 评论 0原文

我可以在SQL触发下获得一些帮助吗？使用多个“ when whn”语句有问题。

create or replace TRIGGER TRANS_TASKS_TRIG02
      BEFORE INSERT OR UPDATE ON "TASKS"
      REFERENCING FOR EACH ROW
    WHEN(NEW.STATUS='WIP') BEGIN
      IF INSERTING OR UPDATING THEN
        :NEW.UPDATED_DATE := NEW_TIME(SYSDATE, 'GMT', 'PDT' );
       END IF;
    WHEN(NEW.STATUS<>'WIP') BEGIN
      IF INSERTING OR UPDATING THEN
        :NEW.UPDATED_DATE := NULL;
       END IF;
    END;

原文

Can I get some help on below SQL trigger. Something is wrong with using multiple "WHEN" statements.

create or replace TRIGGER TRANS_TASKS_TRIG02
      BEFORE INSERT OR UPDATE ON "TASKS"
      REFERENCING FOR EACH ROW
    WHEN(NEW.STATUS='WIP') BEGIN
      IF INSERTING OR UPDATING THEN
        :NEW.UPDATED_DATE := NEW_TIME(SYSDATE, 'GMT', 'PDT' );
       END IF;
    WHEN(NEW.STATUS<>'WIP') BEGIN
      IF INSERTING OR UPDATING THEN
        :NEW.UPDATED_DATE := NULL;
       END IF;
    END;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

卷耳 2025-02-20 18:39:23

根据 oracle's文档，当触发器中的子句时，您不能具有多个。

您可以创建以分开触发器：

create or replace TRIGGER TRANS_TASKS_TRIG02_WIP
      BEFORE INSERT OR UPDATE ON "TASKS"
      REFERENCING FOR EACH ROW
    WHEN(NEW.STATUS='WIP') BEGIN
      IF INSERTING OR UPDATING THEN
        :NEW.UPDATED_DATE := NEW_TIME(SYSDATE, 'GMT', 'PDT' );
       END IF;
    END;

create or replace TRIGGER TRANS_TASKS_TRIG02_WIP
      BEFORE INSERT OR UPDATE ON "TASKS"
      REFERENCING FOR EACH ROW
    WHEN(NEW.STATUS<>'WIP') BEGIN
      IF INSERTING OR UPDATING THEN
        :NEW.UPDATED_DATE := NULL;
       END IF;
    END;

或在其中具有的单个触发器。请注意，如果插入或更新条件是多余的，因为触发器仅在插入或更新之前调用：

create or replace TRIGGER TRANS_TASKS_TRIG02
  BEFORE INSERT OR UPDATE ON "TASKS"
  REFERENCING FOR EACH ROW
BEGIN
  IF :NEW.STATUS='WIP' THEN
    :NEW.UPDATED_DATE := NEW_TIME(SYSDATE, 'GMT', 'PDT' );
  ELSIF :NEW.STATUS<>'WIP' THEN
    :NEW.UPDATED_DATE := NULL;
  END IF;
END;
/

According to Oracle's documentation, you can't have multiple when clauses in a trigger.

You could create to separate triggers:

create or replace TRIGGER TRANS_TASKS_TRIG02_WIP
      BEFORE INSERT OR UPDATE ON "TASKS"
      REFERENCING FOR EACH ROW
    WHEN(NEW.STATUS='WIP') BEGIN
      IF INSERTING OR UPDATING THEN
        :NEW.UPDATED_DATE := NEW_TIME(SYSDATE, 'GMT', 'PDT' );
       END IF;
    END;

create or replace TRIGGER TRANS_TASKS_TRIG02_WIP
      BEFORE INSERT OR UPDATE ON "TASKS"
      REFERENCING FOR EACH ROW
    WHEN(NEW.STATUS<>'WIP') BEGIN
      IF INSERTING OR UPDATING THEN
        :NEW.UPDATED_DATE := NULL;
       END IF;
    END;

Or have a single trigger with an if statement in it. Note that the if inserting or updating condition is redundant, since the trigger is invoked only before insert or update:

create or replace TRIGGER TRANS_TASKS_TRIG02
  BEFORE INSERT OR UPDATE ON "TASKS"
  REFERENCING FOR EACH ROW
BEGIN
  IF :NEW.STATUS='WIP' THEN
    :NEW.UPDATED_DATE := NEW_TIME(SYSDATE, 'GMT', 'PDT' );
  ELSIF :NEW.STATUS<>'WIP' THEN
    :NEW.UPDATED_DATE := NULL;
  END IF;
END;
/

回复收藏 0 原文

~没有更多了~