化肥小火炬数据库充值过程代码

USE ACCOUNT
GO
IF  EXISTS (SELECT * FROM sysobjects WHERE name='TBL_CZ' AND xtype in (N'U'))
drop table TBL_CZ
GO
print '创建记录保存表！'

CREATE TABLE [TBL_CZ] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Fld_LoginID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
	[FLD_CZJE] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[FLD_opTime] [datetime] NULL CONSTRAINT [DF_TBL_CZ_FLD_opTime] DEFAULT (getdate()-1),
	CONSTRAINT [PK_TBL_CZ] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO
IF  EXISTS (SELECT * FROM sysobjects WHERE name='T_CZ_INSERT' AND xtype in (N'TR'))
drop trigger T_CZ_INSERT
GO
print '创建充值过程！'
go



create trigger T_CZ_INSERT on tbl_cz
for insert
as 
begin
	declare @fldid  nvarchar(50)
	set @fldid=(select fld_loginid from inserted) --获取充值ID
	declare @old datetime --获取原有到期时间
	declare @new datetime --获取本次充值时间
	declare @czje int --充值金额
	select @old=FLD_VALIDUNTIL from tbl_ACCOUNT where FLD_LOGINID=@fldid
	select @new=fld_optime,@czje=fld_czje from inserted
	if (@old<=@new)
	begin
		update tbl_ACCOUNT set FLD_VALIDUNTIL=getdate()+@czje where FLD_LOGINID=@fldid
	end
	else
	begin
		update tbl_ACCOUNT set FLD_VALIDUNTIL=FLD_VALIDUNTIL+@czje where FLD_LOGINID=@fldid
	end
end
go
print '执行成功！'