Oracle 触发器学习笔记一

触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。
表名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

实例一：

create or replace trigger UpdateEleHealthCard
before insert ON hisapplicationform
 --after insert or update on hisapplicationform
 --after insert  on hisapplicationform
for each row
DECLARE elehealthcardValue varchar2(100);
                idwhere varchar2(100);
BEGIN
     --错误赋值方式
     --select new.HEALTHCARD into elehealthcard from dual;
    -- select new.ID into idwhere from dual;
    -- dbms_output.put_line('elehealthcard=='|| elehealthcard);
    -- dbms_output.put_line('idwhere=='|| idwhere);
    --方式一       
     :new.elehealthcard := :new.HEALTHCARD;
     dbms_output.put_line('电子健康卡号：'|| :new.elehealthcard);
    
   --方式二   赋值方式
   /*  elehealthcardValue:=:new.HEALTHCARD;
    idwhere:=:new.ID;
     dbms_output.put_line('elehealthcard=='||elehealthcardValue);
     dbms_output.put_line('id=='||idwhere);
     --数据变更
    update hisapplicationform set elehealthcard=elehealthcardValue  where id=idwhere;*/
 
END;

实例二：

create or replace trigger INS_QS_QueueBusiness
  before insert on QS_QUEUEBUSINESS
  for each row
declare
  nextid number;
begin
     if :new.id is null or :new.id=0 then
        select SEQUENCE_BUSINESS.NEXTVAL into nextid from dual;
        :new.id := nextid;
     end if;
end INS_QS_QueueBusiness;

实例三：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

create or replace trigger INS_QS_US_Type
  after insert or update on us_Type
  for each row
declare                                           --完成提交登记数据的同时，产生排队叫号队列数据
  sBUSINESSID   varchar2(5);
  iQUEUEID     integer;
  iOldQueueID integer;
  sPATNAME     varchar2(50);
  iPATID       integer;
  iSTUDYID     integer;
  sHISID       varchar2(50);
  dENROLDATE   date;
  icount integer;
  sClinsitPat  varchar2(20);
  sPartOfCheck varchar2(100);
  sPhotoNo varchar2(50);
  iTotalFee FLOAT;
  sSex varchar2(20);
  sAge varchar2(20);
  sLodgeSection varchar2(20);
  sLodgeDoctor varchar2(20);
  dBirth date;
 
  sSTATUS       varchar2(10);
  iSortno integer;--2009-10-13增加过号
 
  oldDate        date;
  newDate        date;
  imaxID        number;
  iEndNo        number;
  dayhr         number;
  xflag         number;
begin
    select count(*) into icount from qs_queue;
    if icount=0 then
        return;
    end if;
 
   --队列当前最大号初始化
   select trunc(LASTINITDATE) into oldDate from QS_QUEUE where rownum=1; --上次登记日期,即系统日期的前一天
   select trunc(sysdate) into newDate from dual; --数据库系统日期
 
   iSTUDYID := :new.ID;
   iQUEUEID := :new.ustype;
   --iPATID := :new.Patientid;
   sSTATUS := '已登记';
                                                     --检查号
   --select to_date(enroldate,'yyyy-mm-dd'), cliisinpat,partofcheck,
   --        into dENROLDATE,sClinsitPat,sPartOfCheck  from us_studies where studyid= iStudyid; --登记日期
   --select name,HisID into sPATNAME,sHISID from us_patient where patientid= iPATID;--患者姓名、HISID
 
   select a.name,a.HisID, a.patientid, b.cliisinpat,b.partofcheck,b.photono,
            a.sex,to_date(a.birthdate,'yyyy-mm-dd'), b.age||b.ageunit as age,b.lodgesection,b.lodgedoctor,
            b.totalfee,to_date(b.enroldate,'yyyy-mm-dd')
       into sPATNAME,sHISID,iPATID, sClinsitPat, sPartOfCheck,sPhotoNo,
            sSex,dBirth,sAge,sLodgeSection,sLodgeDoctor,iTotalFee,dENROLDATE
     from us_patient a ,us_studies b
     where a.patientid= b.patientid and b.studyid= iSTUDYID;--患者姓名、HISID
 
   if newDate>oldDate then--删除两天前的数据
   begin
     if dEnrolDate = newDate then --如果登记日期等于系统日期时，修改上次登记日期和最大队列号
     begin
        select MAXBUSIID2 into imaxID from QS_QUEUE where QUEUEID = :new.ustype;
        if imaxID > 0 then
        begin
           update qs_queue set maxbusiid = MAXBUSIID2;
           update qs_queue set MAXBUSIID2 = MAXBUSIID3;
           update qs_queue set MAXBUSIID3 = STARTNO,LASTINITDATE=newDate,flag=0;   --比较日期，初始化各队列的信息
        end;
        end if;
        delete from QS_QUEUEBUSINESS where trunc(enroldate) <= trunc(newDate);   --删除两天前的数据
     end;
     end if;
   end;
   end if;
 
 
 
     /* --add by yzl...同一天且分上下午( 13点以后约的从1开始 )..begin */
     select   to_number(to_char(sysdate,'hh24') )  into dayhr   from dual;
 
     select flag into xflag from qs_queue where rownum=1;
 
     if (newDate=oldDate)  and  (dayhr>=13) and (xflag=0) then
     begin
        select MAXBUSIID2 into imaxID from QS_QUEUE where QUEUEID = :new.ustype;
        if imaxID > 0 then
        begin
           update qs_queue set maxbusiid = MAXBUSIID2;
           update qs_queue set MAXBUSIID2 = MAXBUSIID3;
           update qs_queue set MAXBUSIID3 = STARTNO,flag=1;   --比较日期，初始化各队列的信息
        end;
        end if;
        delete from QS_QUEUEBUSINESS where trunc(enroldate) <= trunc(newDate);   --删除两天前的数据
     end;
     end if;
    /* --add by yzl...同一天且分上下午( 12点以后约的从1开始 ).. end */
 
 
 
 
 
 
   --队列号为0时，退出
   if :new.ustype = 0 then
     return;
   end if;
 
   --登记时，首先判断登记日期是不是数据库系统日期，如果是，则取MAXBUSIID为最大队列号，
   --如果比数据库日期大一天，则取MAXBUSIID2，如果大两天，取MAXBUSIID3
   if dENROLDATE = newDate then
   begin
      select MAXBUSIID2 ,ENDNO into imaxID,iEndNo from QS_QUEUE where QUEUEID = :new.ustype;
      if imaxId = -1 then
         update qs_queue set maxbusiid2 = startno, maxbusiid3 = startno ;
      end if;
 
      select MAXBUSIID +1,ENDNO into imaxID,iEndNo from QS_QUEUE where QUEUEID = :new.ustype; --登记日期 = 数据库系统日期
   end;
   elsif dENROLDATE = newDate + 1 then
   begin
      select MAXBUSIID2 +1,ENDNO into imaxID,iEndNo from QS_QUEUE where QUEUEID = :new.ustype;--登记日期 = 数据库系统日期+1
   end;
   elsif dENROLDATE = newDate + 2 then
      select MAXBUSIID3 +1,ENDNO into imaxID,iEndNo from QS_QUEUE where QUEUEID = :new.ustype;--登记日期 = 数据库系统日期+2
   else
       return;
   end if;
   --如果当前排队号大于最大排序号，退出
   if imaxId <>0 and imaxID > iEndNo then
     return;
   end if;
 
 
 
 
 
 
 
 
 
   if inserting then
   begin
     --获得当前队列最大号，并更新队列表中最大值
     if dENROLDATE = newDate then
     begin
        select MAXBUSIID into sBUSINESSID from QS_QUEUE where QUEUEID=:new.ustype;
        update QS_QUEUE set MAXBUSIID = MAXBUSIID +1 where QUEUEID = :new.ustype;
     end;
     elsif dENROLDATE = newDate + 1 then
     begin
        select MAXBUSIID2 into sBUSINESSID from QS_QUEUE where QUEUEID=:new.ustype;
        update QS_QUEUE set MAXBUSIID2 = MAXBUSIID2 +1 where QUEUEID = :new.ustype;
     end;
     elsif dENROLDATE = newDate + 2 then
     begin
        select MAXBUSIID3 into sBUSINESSID from QS_QUEUE where QUEUEID=:new.ustype;
        update QS_QUEUE set MAXBUSIID3 = MAXBUSIID3 +1 where QUEUEID = :new.ustype;
     end;
     end if;
     --数据插入叫号业务表
     select to_date(enroldate || ' ' || enroltime, 'YYYY-MM-DD HH24:MI:SS') into dENROLDATE
            from us_studies where studyid= iStudyid;                              --登记时间
 
     insert into QS_QUEUEBUSINESS (BUSINESSID,QUEUEID,PATNAME,PATID,STUDYID,HISID,ENROLDATE,STATUS, Sortno,CLINSINPAT,partofcheck
            ,Photono,Sex,Age,Totalfee,Lodgesection, Lodgedoctor,Birthdate)
       values(sBUSINESSID,iQUEUEID, sPATNAME,iPATID,iSTUDYID,sHISID,dENROLDATE,sSTATUS,sBUSINESSID,sClinsitPat,sPartOfCheck
             ,sPhotono,sSex,sAge,iTotalfee,sLodgesection,slodgeDoctor,dBirth);
   end;
 
   elsif updating then
   begin
      --获得当前患者的旧的队列号
      select queueid into iOldQueueID from QS_QUEUEBUSINESS where studyid = :old.id;
      --如果更改了队列，则重新生成排队号
      if iQueueid <> iOldQueueID then
      begin
          if dENROLDATE = newDate then
          begin
                select MAXBUSIID into sBUSINESSID from QS_QUEUE where QUEUEID=:new.ustype;
                update QS_QUEUE set MAXBUSIID = MAXBUSIID +1 where QUEUEID = :new.ustype;
          end;
          elsif dENROLDATE = newDate + 1 then
          begin
               select MAXBUSIID2 into sBUSINESSID from QS_QUEUE where QUEUEID=:new.ustype;
               update QS_QUEUE set MAXBUSIID2 = MAXBUSIID2 +1 where QUEUEID = :new.ustype;
          end;
          elsif dENROLDATE = newDate + 2 then
          begin
               select MAXBUSIID3 into sBUSINESSID from QS_QUEUE where QUEUEID=:new.ustype;
               update QS_QUEUE set MAXBUSIID3 = MAXBUSIID3 +1 where QUEUEID = :new.ustype;
          end;
          end if;
      end;
      else
      begin
           select businessid into sBusinessId from qs_queuebusiness where studyid =:old.id;
           select Sortno into iSortno from qs_queuebusiness where studyid =:old.id;--2009-10-13增加过号
      end;
      end if;
 
      select name,HisID into sPATNAME,sHISID from us_patient where patientid= :new.Patientid;--患者姓名、HISID
      update QS_QUEUEBUSINESS set BUSINESSID=sBUSINESSID,SortNo=sBUSINESSID, QUEUEID = :new.ustype, PATNAME = sPATNAME where STUDYID = :old.id;
   end;
   end if;
 
end INS_QS_US_Type;