Oracle触发器

create or replace procedure get_name(v_id in number, v_name out varchar2) as
begin
select addr into v_name from student_info where stu_id = v_id;
exception
when no_data_found then
raise_application_error(-20001, '你输入的ID不存在！');
end get_name;

/***********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;
 
 
/******************** 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;