you can design a table to simular the function .create table seq ( province varchar2(10),seq_no number)in your trigger select seq_no from seq where province = :new.province for update no wait then update seq set seq_no = seq_no + 1 where province = :new.province
create or replace trigger testInsertTrigger
AFTER INSERT
on TEST
for each row
/* ERwin Builtin Mon Apr 21 12:01:18 2003 */
/* default body for ProvinceInsertTrigger */
declare
numrows INTEGER;
v_str varchar(1000);
beginv_str :='create sequence TESTSEQUENCEssss minvalue 1 maxvalue 999999999999999999 start with 10 increment by 1 cache 20';
execute immediate v_str;end;
/
then update seq set seq_no = seq_no + 1 where province = :new.province
Lastdrop(空杯) 的答复是我想知道的。
只是我我创建的Sequence 需要用插入的纪录中某一字段(:Pro_name)的纸为名称。
比如,插入“jing”,sequence 的名称就是 jing_SEQ
v_str :='create sequence TESTSEQUENCEssss minvalue 1 maxvalue 999999999999999999 start with 10 increment by 1 cache 20';改为
v_str :='create sequence '||:new.pro_name||'_SEQ minvalue 1 maxvalue 999999999999999999 start with 10 increment by 1 cache 20';
是不是我的表健的有问题?Pro_name是varchar2(255)。
你可以在执行v_str前,加上DBMS_OUTPUT.PUT_LINE('Pro name is '||:new.pro_name);把pro_name打印出来瞧瞧。别忘记在SQL*PLUS中执行set serveroutput on
create or replace trigger testInsertTrigger
AFTER INSERT
on TEST
for each row
/* ERwin Builtin Mon Apr 21 12:01:18 2003 */
/* default body for ProvinceInsertTrigger */
declare
numrows INTEGER;
v_str varchar(1000);
v_temp varchar(20);
begin
v_temp := :new.Pro_name;
v_str :='create sequence '||v_temp||'_SEQ minvalue 1 maxvalue 999999999999999999 start with 10 increment by 1 cache 20';
execute immediate v_str;
end;
grant create sequence to 当前用户;
A trigger cannot contain DDL or transaction control statements nor can it call stored procedures which do;