Oracle触发器

首先,创建一个Sequences,名字一般为表名_SEQ,可以写语句,也可以如图创建

然后在Triggers文件夹下创建触发器,

create or replace trigger "DEVICE_PACKAGE_INSTTRG"
  before insert on DEVICE_PACKAGE_TAB
  for each row
begin
  select DEVICE_PACKAGE_SEQ.nextval into :new.ID from dual;
end DEVICE_PACKAGE_INSTTRG;

这样在你插入数据时,表DEVICE_PACKAGE_TAB的ID字段就会自动取值填充.

设备表也一样,注意触发插入的字段名称

create or replace trigger "DEVICE_INSTTRG"
  before insert on DEVICE_TAB
  for each row
begin
  select DEVICE_SEQ.nextval into :new.DEVICE_ID from dual;
end DEVICE_INSTTRG;

更新触发器

create or replace trigger "DEVICE_PACKAGE_UPTTRG" 
after update on DEVICE_PACKAGE_TAB for each row
 
begin
  ---stateid Integer;
  ---stateid = :new.state;
  if :new.state = 3 then
    update PACKAGE_DOC_TAB t
       set t.closetime = sysdate, t.state = :new.state
     where t.package_id = :new.ID;
    update PACKAGE_DOC_COMMENT_TAB y
       set y.closetime = sysdate, y.state = :new.state
     where y.package_id = :new.ID;
  end if;
end DEVICE_PACKAGE_UPTTRG;

删除触发器

create or replace trigger "DEVICE_PACKAGE_DETTRG"
after delete on DEVICE_PACKAGE_TAB for each row
begin
  delete from package_doc_tab pd where pd.package_id = :old.id;
  delete from package_doc_comment_tab pdc where pdc.package_id = :old.id;
end DEVICE_PACKAGE_INSTTRG;

DEVICE_PACKAGE_TAB的表结构