oracle触发器--if else demo

 1 CREATE OR REPLACE Trigger trig_solr_index_el_lesson
 2   After Update of lessonid, lessonname, lessongoal, note, teachername, teacherid, classidname, classid, crtime, status Or Delete or insert On el_lesson
 3   For Each Row
 4 declare
 5   -- local variables here
 6   new_status number;
 7 begin
 8   new_status := :NEW.status;
 9   if inserting then
10     begin
11       INSERT INTO solr_index
12         (id, docid, type)
13       VALUES
14         (solr_index_seq.NEXTVAL, :NEW.lessonid, 'add');
15     end;
16   end if;
17 
18   if updating then
19   --对比老的状态和新的状态
20     if new_status = -1 and :OLD.status != -1 then
21     
22       INSERT INTO solr_index
23         (id, docid, type)
24       VALUES
25         (solr_index_seq.NEXTVAL, :OLD.lessonid, 'delete');
26     
27     else
28       INSERT INTO solr_index
29         (id, docid, type)
30       VALUES
31         (solr_index_seq.NEXTVAL, :OLD.lessonid, 'update');
32     
33     end if;
34   end if;
35 
36   if deleting then
37     begin
38       INSERT INTO solr_index
39         (id, docid, type)
40       VALUES
41         (solr_index_seq.NEXTVAL, :OLD.lessonid, 'delete');
42     end;
43   end if;
44 end;

 

posted @ 2015-08-05 11:31  勿妄  阅读(6519)  评论(0编辑  收藏