oracle 触发器中if,oracle触发器--if else demo

CREATE OR REPLACE Trigger trig_solr_index_el_lesson

After Update of lessonid, lessonname, lessongoal, note, teachername, teacherid, classidname, classid, crtime, status Or Delete or insert On el_lesson

For Each Row

declare

-- local variables here

new_status number;

begin

new_status := :NEW.status;

if inserting then

begin

INSERT INTO solr_index

(id, docid, type)

VALUES

(solr_index_seq.NEXTVAL, :NEW.lessonid, 'add');

end;

end if;

if updating then

--对比老的状态和新的状态

if new_status = -1 and :OLD.status != -1 then

INSERT INTO solr_index

(id, docid, type)

VALUES

(solr_index_seq.NEXTVAL, :OLD.lessonid, 'delete');

else

INSERT INTO solr_index

(id, docid, type)

VALUES

(solr_index_seq.NEXTVAL, :OLD.lessonid, 'update');

end if;

end if;

if deleting then

begin

INSERT INTO solr_index

(id, docid, type)

VALUES

(solr_index_seq.NEXTVAL, :OLD.lessonid, 'delete');

end;

end if;

end;