十年河东,十年河西,莫欺少年穷
学无止境,精益求精
今天是数据库脚本类的代码,所以不想过多阐述
如下数据表:
create table Card(
Id int identity(1,1) primary key,
CardNo varchar(50) unique not null,
Status bit default(0)
)
上述表中有自增长字段Id,现在要求字段 CardNo 和自增长Id 相关联,譬如,当Id为1时,CardNo 应该为 Card00001,当Id为15时,CardNo应该为: Card00015 ,要求通过触发器或存储过程实现?
其中触发器实现如下:
create TRIGGER [dbo].[TGR_Card]
ON [dbo].[Card]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for trigger here、
DECLARE @Id bigint
SELECT @Id = Id FROM INSERTED UPDATE Card
SET CardNo = CASE
WHEN LEN(@Id) = 1 THEN 'Crad0000000' + CAST(@Id AS varchar(20))
WHEN LEN(@Id) = 2 THEN 'Crad000000' + CAST(@Id AS varchar(20))
WHEN LEN(@Id) = 3 THEN 'Crad00000' + CAST(@Id AS varchar(20))
WHEN LEN(@Id) = 4 THEN 'Crad0000' + CAST(@Id AS varchar(20))
WHEN LEN(@Id) = 5 THEN 'Crad000' + CAST(@Id AS varchar(20))
WHEN LEN(@Id) = 6 THEN 'Crad00' + CAST(@Id AS varchar(20))
WHEN LEN(@Id) = 7 THEN 'Crad0' + CAST(@Id AS varchar(20))
WHEN LEN(@Id) = 8 THEN 'Crad' + CAST(@Id AS varchar(20))
END
WHERE orderId = @Id END
如果用存储过程如何实现呢?
存储过程实现就需要考虑如下因素了:
1、存储过程需要程序调用执行
2、如果同一时刻多条数据插入数据库时,就应该考虑到数据并发,因此,采用存储过程实现应该避免并发
那么,存储过程应该怎么实现呢?
我们都知道,防止并发我们采用乐观锁或悲观锁,在此不做演示了,总之,要加上版本字段 ,数据库类型:TimeStamp!
关于并发的避免大家可参考:http://www.cnblogs.com/chenwolong/p/Lock.html
http://www.cnblogs.com/chenwolong/p/BF.html
@陈卧龙的博客