34,590
社区成员
发帖
与我相关
我的任务
分享
create table tb(订单号 varchar(14),客户编号 int,订单金额 int)
go
create trigger tri_dd
on tb
INSTEAD OF insert
as
begin
insert into tb
select convert(varchar(8),getdate(),112)+
right('0000'+ltrim((select count(*) from tb where left(订单号,8)=convert(varchar(8),getdate(),112))),4)订单号,
客户编号,订单金额 from inserted
end
go
insert into tb(客户编号,订单金额) select 12,5000
insert into tb(客户编号,订单金额) select 18,12000
go
select * from tb
/*
订单号 客户编号 订单金额
-------------- ----------- -----------
201107150000 12 5000
201107150001 18 12000
(2 行受影响)
*/
go
drop table tb
create table tb(订单号 varchar(14),客户编号 int,订单金额 int)
go
create trigger tri_dd
on tb
INSTEAD OF insert
as
begin
insert into tb
select convert(varchar(10),getdate(),112)+
right('0000'+ltrim((select count(*) from tb where left(订单号,10)=convert(varchar(10),getdate(),112))),4)订单号,
客户编号,订单金额 from inserted
end
go
insert into tb(客户编号,订单金额) select 12,5000
insert into tb(客户编号,订单金额) select 18,12000
go
select * from tb
/*
订单号 客户编号 订单金额
-------------- ----------- -----------
201107150000 12 5000
201107150000 18 12000
(2 行受影响)
*/
go
drop table tb
select CONVERT(varchar(100), GETDATE(), 112)+cast((10000-ROW_NUMBER() over(order by getdate(), 客户编号)) as varchar(100)) as,客户编号,订单金额
from tb
select CONVERT(varchar(100), GETDATE(), 112)+cast((10000-ROW_NUMBER() over(order by id)) as varchar(100)),客户编号,订单金额
from tb
-------------步长表--------------
if exists(select 1 from sysobjects where id=object_id(N't_Step') and objectproperty(id,N'isUserTable')=1)
drop table t_Step
Go
create table t_Step
(
fnumber int,
fdatetime datetime
)
insert into t_Step values(0,getdate())
-----------------销售订单--------------------
if exists(select 1 from sysobjects where id=object_id(N't_SaleOrder') and objectproperty(id,N'IsUserTable')=1)
drop table t_SaleOrder
Go
create table t_SaleOrder
(
fid int identity(1,1),
订单号 varchar(20),
客户编号 varchar(20),
订单金额 money
)
-------------触发器--------------
if exists(select 1 from sysobjects where id=object_id(N'tri_insertSaleOrder') and objectproperty(id,N'IsTrigger')=1)
drop Trigger tri_insertSaleOrder
Go
create trigger tri_insertSaleOrder
on t_SaleOrder
for insert
as
--年月日
declare @FYear int
declare @FMonth int
declare @FDay int
declare @fnumber varchar(20)
--步长表中number
declare @maxNum int
--分别取出步长表中的年月日
select @maxNum=fnumber,@FYear=datepart(year,fdatetime),
@FMonth=datepart(month,fdatetime),@FDay=datepart(day,fdatetime) from t_Step
--步长计算是否到了第二天
if (datepart(year,getdate())*1000+datepart(month,getdate())*50+datepart(day,getdate()))>(@FYear*1000+@FMonth*50+@FDay)
begin
--步长计算如果到了第二天,清空NUMBER
update t_Step set fnumber=1,fdatetime=getdate()
set @maxNum=1
End
Else
Begin
set @maxNum=@maxNum+1
update t_Step set fnumber=@maxNum
End
--设置流水号格式
If len(convert(varchar(20),@maxNum))=1
Begin
set @fnumber='000'+convert(varchar(20),@maxNum)
End
Else if len(convert(varchar(20),@maxNum))=2
Begin
set @fnumber='00'+convert(varchar(20),@maxNum)
End
Else if len(convert(varchar(20),@maxNum))=3
Begin
set @fnumber='0'+convert(varchar(20),@maxNum)
End
set @fnumber=convert(varchar(20),getdate(),112)+@fnumber
update t_SaleOrder set 客户编号=@fnumber from t_SaleOrder t inner join inserted i on(t.fid=i.fid)
delete from t_SaleOrder
select * from t_SaleOrder
insert into t_SaleOrder values('C001','sssss',20000)
用除法器做
create table tbA(订单号 nvarchar(20), 客户编号 nvarchar(10), 订单金额 int)
alter trigger SetID on tbA instead of insert
as
begin
declare @returnStr as int
set @returnStr=0
select @returnStr=max(cast(right(订单号,4) as int)) from tbA
where convert(varchar(8),getdate(),112)=Left(订单号,8)
if @returnStr is null
set @returnStr=0
;with CTE as (select convert(varchar(8),getdate(),112)+right('000'+ltrim(@returnStr+Row_number()over(order by getdate())),4) as 订单号 ,
客户编号,订单金额 from inserted)
insert tbA select * from CTE
end
insert tbA
select '111','c5',123 union all
select '111','c6',123 union all
select '111','c7',123 union all
select '111','c8',123
select * from tbA
订单号 客户编号 订单金额
-------------------- ---------- -----------
201107150001 c5 123
201107150002 c6 123
201107150003 c7 123
201107150004 c8 123
(4 row(s) affected)
--下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO
--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
--插入资料
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN
--显示结果
SELECT * FROM tb
/*--结果
BH col
---------------- -----------
BH000001 1
BH000002 2
BH000003 4
BH000004 14
--*/