oracle 触发器输出值,Oracle表触发器并写入序列值

--创建 序列 seq_1

drop sequence seq_1;

create sequence seq_1

minvalue 1

maxvalue 999999999999999999999999999

start with 100000000000000

increment by 1

nocache;

drop table test_Depart;

create table test_Depart (

did varchar2(32) not null primary key,

dname varchar2(32) not null

);

--建解发器代码为

create or replace trigger tri_test_id

before insert on test_Depart --test_Depart 是表名

for each row

declare

nextid varchar2(32);

begin

IF :new.did IS NULL or :new.did=0 THEN --did是列名

--取得年度

select to_char(sysdate,'yyyy')||seq_1.nextval

into nextid

from sys.dual;

:new.did:=nextid;

end if;

end tri_test_id;

-- for test

--select to_char(sysdate,'yyyy')||seq_1.nextval from dual;

--insert into test_Depart(dname) values('部门1');

或者

--建解发器代码为

create or replace trigger tri_test_id

before insert on test_Depart --test_Depart 是表名

for each row

declare

nextid number;

begin

IF :new.did IS NULL or :new.did=0 THEN --did是列名

select seq_1.nextval

into nextid

from sys.dual;

:new.did:=nextid;

end if;

end tri_test_id;

或者

--建解发器代码为

create or replace trigger tri_test_id

before insert on test_Depart --test_Depart 是表名

for each row

declare

nextid number;

begin

IF :new.did IS NULL or :new.did=0 THEN --did是列名

select to_number(to_char(sysdate,'yyyymmddHH24miss'))

into nextid

from sys.dual;

:new.did:=nextid;

end if;

end tri_test_id;