4、oracle创建触发器trigger实现自增id

目录

• oracle创建触发器trigger实现自增id
  • 1、创建测试表
  • 2、创建序列
  • 3、创建触发器
  • 4、插入语句
  • 5、查看效果
  • 6、多个触发器执行

oracle创建触发器trigger实现自增id

1、创建测试表

create table dm_test (id number,name varchar2(100));

2、创建序列

 -- Create sequence 
 create sequence dm_seq_test
 minvalue 1
 maxvalue 9999999999999999999999999999
 start with 1
 increment by 1
 cache 20;

3、创建触发器

create or replace trigger dm_tr_test  
  before insert on dm_test    
  for each row    
declare  
  -- local variable here  
begin    
  -- Column "ID" uses sequence di_test_id  
  select dm_seq_test.nextval into :new.ID from dual;  
end dm_tr_test;

4、插入语句

insert into dm_test (name)values('123'); insert into dm_test (name)values('1223');

5、查看效果

select * from dm_test;

6、多个触发器执行

declare 
  v_sql varchar2(5000);
  
begin 
  for cur_trigger in (
select 'create or replace trigger dm_tr_'||biz_code||'  
  before insert on dm_src_'||biz_code||'    
  for each row    
declare  
  -- local variable here  
begin    
  select '||SRC_SEQ_NAME||'.nextval into :new.ID from dual;  
end dm_tr_'||biz_code||';'  as trigger_sql from dm_conf_table where SRC_SEQ_NAME is not null)  loop 
    dbms_output.put_line(cur_trigger.trigger_sql);
    execute immediate cur_trigger.trigger_sql;
end loop;

end;