用触发器也可能实现啊. 如果新增记录或变动就自动调动触发器,我分析过几个大的ERP软件就是用触发器实现的,方便又实用. 下面是一个国内比较流行的ERP的一个实例: CREATE TRIGGER t_Item_AutoNumber ON t_Item --### FOR INSERT NOT FOR REPLICATION AS DECLARE @Next int, @Step smallint, @ID int, @TableName varchar(30) if @@Rowcount = 0 return set @TableName = 't_Item' --### SELECT @Next = FNext,@Step = FStep FROM t_Identity WHERE FName = @TableName declare ID_Curs cursor for select FItemID --### from Inserted
open ID_Curs fetch ID_Curs into @ID
while(@@fetch_status = 0) begin if @ID = -1 begin UPDATE a SET a.FItemID = @Next--### FROM t_Item a--### WHERE a.FItemID = @ID --### set @Next = @Next + @Step end else if @ID >= @Next set @Next = @ID + @Step fetch ID_Curs into @ID end UPDATE t_Identity SET FNext = @Next WHERE FName = @TableName UPDATE IcMaxNum SET FMaxNum = @Next WHERE FTableName = @TableName close ID_Curs deallocate ID_Curs
(column_name datatype identity
...)
人家要的是oracle中sequence有用法,
我也不清楚,!
我也在找,看来没有会的了!
如果新增记录或变动就自动调动触发器,我分析过几个大的ERP软件就是用触发器实现的,方便又实用.
下面是一个国内比较流行的ERP的一个实例:
CREATE TRIGGER t_Item_AutoNumber ON t_Item --###
FOR INSERT
NOT FOR REPLICATION
AS
DECLARE @Next int,
@Step smallint,
@ID int,
@TableName varchar(30)
if @@Rowcount = 0
return
set @TableName = 't_Item' --###
SELECT @Next = FNext,@Step = FStep
FROM t_Identity
WHERE FName = @TableName
declare ID_Curs cursor for
select FItemID --###
from Inserted
open ID_Curs
fetch ID_Curs into @ID
while(@@fetch_status = 0)
begin
if @ID = -1
begin
UPDATE a SET a.FItemID = @Next--###
FROM t_Item a--###
WHERE a.FItemID = @ID --###
set @Next = @Next + @Step
end
else
if @ID >= @Next
set @Next = @ID + @Step
fetch ID_Curs into @ID
end
UPDATE t_Identity SET FNext = @Next WHERE FName = @TableName
UPDATE IcMaxNum SET FMaxNum = @Next WHERE FTableName = @TableName
close ID_Curs
deallocate ID_Curs