使用触发器创建序列

!--说明：药师职称管理触发器
declare
v_MaxId number;
v_sql varchar2(2000);
v_ExistSequence number;
begin
Select Count(*) into v_ExistSequence from USER_OBJECTS WHERE OBJECT_TYPE='SEQUENCE' AND Object_Name = upper('seq_FDAPharmacistType');
if v_ExistSequence = 1 then
execute immediate 'drop sequence seq_FDAPharmacistType';
end if;

select Max(iPharmacistTypeID) into v_MaxId from FDAPharmacistType;
if v_MaxId is null or v_MaxId = '' then
v_MaxId := 1;
else
v_MaxId := v_MaxId + 1;
end if;
v_sql := 'create sequence seq_FDAPharmacistType INCREMENT BY 1 START WITH '||to_Char(v_MaxId);
execute immediate v_sql;
v_sql := 'create or replace trigger trg_FDAPharmacistType' || CHR(10);
v_sql := v_sql || 'before insert on FDAPharmacistType' || CHR(10);
v_sql := v_sql || 'for each row' || CHR(10);
v_sql := v_sql || 'begin' || CHR(10);
v_sql := v_sql || ' select seq_FDAPharmacistType.nextval into :new.iPharmacistTypeID' || CHR(10);
v_sql := v_sql || ' from dual;' || CHR(10);
v_sql := v_sql || 'end trg_FDAPharmacistType;' || CHR(10);
execute immediate v_sql;
end;