oracle触发器实例

最新推荐文章于 2023-01-04 11:18:58 发布

原创最新推荐文章于 2023-01-04 11:18:58 发布 · 166 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #SQL

oracle数据库专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一种使用Oracle触发器实现表间数据同步的方法。包括DELETE、INSERT和UPDATE操作后的触发器实现细节，确保源表与历史表之间的数据一致性。

1、delete触发器


create or replace
TRIGGER "TRWATCH" AFTER
  DELETE ON WATCH FOR EACH ROW DECLARE watchname VARCHAR2(30);
  watchphone VARCHAR2(30);
  starttime DATE;
  endtime DATE;
  wwid   VARCHAR2(14);
  flag   INT;
  counts INT;
  BEGIN
    --deleted --> :old
    watchname  := :old.WATCHNAME;
    watchphone := :old.WATCHPHONE;
    starttime  := :old.STARTTIME;
    endtime    := :old.ENDTIME;
    wwid       := :old.WWID;
    flag       := :old.FLAG;
    IF flag     = 0 THEN
      SELECT COUNT(*)
      INTO counts
      FROM HIS_WATCH
      WHERE rownum  =1
      AND WATCHNAME =watchname
      AND WATCHPHONE=watchphone
      AND WWID      =wwid
      AND FLAG      = 0;
      IF counts     > 0 THEN
        UPDATE HIS_WATCH
        SET FLAG       =1,
          ENDTIME      =sysdate
        WHERE WATCHNAME=watchname
        AND WATCHPHONE =watchphone
        AND WWID       =wwid
        AND FLAG       = 0;
      END IF;
    END IF;
  END;

2、insert、update触发器


create or replace
TRIGGER "TRWATCH1" AFTER
  INSERT OR
  UPDATE ON WATCH FOR EACH ROW DECLARE watchname VARCHAR2(30);
  watchphone VARCHAR2(30);
  starttime DATE;
  endtime DATE;
  wwid   VARCHAR2(14);
  flag   INT;
  counts INT;
  BEGIN
    ----inserted --> :new
    watchname  := :new.WATCHNAME;
    watchphone := :new.WATCHPHONE;
    starttime  := :new.STARTTIME;
    endtime    := :new.ENDTIME;
    wwid       := :new.WWID;
    flag       := :new.FLAG;
    IF flag     = 0 THEN
      SELECT COUNT(*)
      INTO counts
      FROM HIS_WATCH
      WHERE rownum  =1
      AND WATCHNAME =watchname
      AND WATCHPHONE=watchphone
      AND WWID      =wwid
      AND FLAG      = 0;
      IF counts     > 0 THEN
        UPDATE HIS_WATCH
        SET FLAG       =1,
          ENDTIME      =sysdate
        WHERE WATCHNAME=watchname
        AND WATCHPHONE =watchphone
        AND WWID       =wwid
        AND FLAG       = 0;
      END IF;
      INSERT
      INTO HIS_WATCH
        (
          WATCHNAME,
          WATCHPHONE,
          STARTTIME,
          WWID,
          FLAG
        )
        VALUES
        (
          watchname,
          watchphone,
          sysdate,
          wwid,
          0
        );
    ELSE
      SELECT COUNT(*)
      INTO counts
      FROM HIS_WATCH
      WHERE rownum  =1
      AND WATCHNAME =watchname
      AND WATCHPHONE=watchphone
      AND WWID      =wwid
      AND FLAG      = 0;
      IF counts     > 0 THEN
        UPDATE HIS_WATCH
        SET FLAG       =1,
          ENDTIME      =sysdate
        WHERE WATCHNAME=watchname
        AND WATCHPHONE =watchphone
        AND WWID       =wwid
        AND FLAG       = 0;
      END IF;
    END IF;
  END;