oracle触发器实例

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;