ALTER TABLE [dbo].[bas_prd_barcodeLogInfo] ADD CONSTRAINT [DF_bas_prd_barcodeLogInfo_ID] DEFAULT (newid()) FOR [ID]
GO
ALTER TABLE [dbo].[bas_prd_barcodeLogInfo] ADD CONSTRAINT [DF_bas_prd_barcodeLogInfo_changeDatetime] DEFAULT (CONVERT([nvarchar](19),getdate(),(120))) FOR
[changeDatetime]
GO
函数
create FUNCTION GenerateBarcode()
RETURNS nvarchar(8)
AS
begin
DECLARE @barcodeMax DECIMAL(18,0)
SET @barcodeMax = 0
SELECT @barcodeMax = MAX(prd_barcode) FROM dbo.BAS_PRD WHERE LEN(prd_barcode) = 8 AND PATINDEX('%[^0-9]%',prd_barcode) = 1 and SUBSTRING
(prd_barcode,1,1) = '1'
IF(@barcodeMax=0 OR @barcodeMax IS NULL)
BEGIN
SET @barcodeMax = 10000001
END
return REPLICATE('0',8-len((@barcodeMax+1)))+cast((@barcodeMax+1) as varchar)
end
触发器
CREATE TRIGGER [dbo].[TR_BAS_PRD_Insert] ON [dbo].[BAS_PRD]
FOR INSERT
AS
DECLARE basPrdCursor CURSOR
FOR
SELECT prd_id,fas_id,col_id,siz_id FROM Inserted
OPEN basPrdCursor
DECLARE @prd_id NVARCHAR(20),@fas_id NVARCHAR(20),@col_id NVARCHAR(20),@siz_id NVARCHAR(20),@prdbarcode nvarchar(20)
FETCH NEXT FROM basPrdCursor INTO @prd_id,@fas_id,@col_id,@siz_id
WHILE @@FETCH_STATUS =0
BEGIN
select @prdbarcode = dbo.GenerateBarcode()
UPDATE dbo.BAS_PRD SET prd_barcode = @prdbarcode WHERE prd_id = @prd_id
insert into bas_prd_barcodeLogInfo(fas_id,col_id,siz_id,prd_barcode)
values(@fas_id,@col_id,@siz_id,@prdbarcode)
FETCH NEXT FROM basPrdCursor INTO @prd_id,@fas_id,@col_id,@siz_id
END
--关闭游标
CLOSE basPrdCursor
--释放资源
DEALLOCATE basPrdCursor
go
-----------------------------------------------------------我是分割线----------------------------------------------------------------------------
更新的代码
alter FUNCTION GenerateBarcode()
RETURNS nvarchar(8)
AS
begin
DECLARE @barcodeMax DECIMAL(18,0)
SET @barcodeMax = 0
SELECT @barcodeMax = MAX(prd_barcode) FROM dbo.BAS_PRD WHERE LEN(prd_barcode) = 8 AND PATINDEX('%[^0-9]%',prd_barcode) = 1 and SUBSTRING
(prd_barcode,1,1) = '1'
IF(@barcodeMax=0 OR @barcodeMax IS NULL)
BEGIN
SET @barcodeMax = 10000001
END
return REPLICATE('0',8-len((@barcodeMax+1)))+cast((@barcodeMax+1) as varchar)
end