Oracle触发器

最新推荐文章于 2025-04-23 09:02:24 发布

ll123456

最新推荐文章于 2025-04-23 09:02:24 发布

阅读量589

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/ls_hai/article/details/18813267

Oracle 专栏收录该内容

3 篇文章

订阅专栏

本文介绍如何在Oracle数据库中使用序列与触发器实现自动增长的ID字段。包括创建序列、插入触发器、更新触发器及删除触发器的具体步骤与SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先,创建一个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的表结构