[传奇技术]化肥小火炬数据库充值过程代码

源码资源网

[传奇技术]化肥小火炬数据库充值过程代码

1. USE ACCOUNT
   
2. GO
   
3. IF  EXISTS (SELECT * FROM sysobjects WHERE name='TBL_CZ' AND xtype in (N'U'))
   
4. drop table TBL_CZ
   
5. GO
   
6. print '创建记录保存表！'
   
7. 
   
8. CREATE TABLE [TBL_CZ] (
   
9.         [ID] [int] IDENTITY (1, 1) NOT NULL ,
   
10.         [Fld_LoginID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    
11.         [FLD_CZJE] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
    
12.         [FLD_opTime] [datetime] NULL CONSTRAINT [DF_TBL_CZ_FLD_opTime] DEFAULT (getdate()-1),
    
13.         CONSTRAINT [PK_TBL_CZ] PRIMARY KEY  CLUSTERED
    
14.         (
    
15.                 [ID]
    
16.         )  ON [PRIMARY]
    
17. ) ON [PRIMARY]
    
18. GO
    
19. IF  EXISTS (SELECT * FROM sysobjects WHERE name='T_CZ_INSERT' AND xtype in (N'TR'))
    
20. drop trigger T_CZ_INSERT
    
21. GO
    
22. print '创建充值过程！'
    
23. go
    
24. 
    
25. 
    
26. 
    
27. create trigger T_CZ_INSERT on tbl_cz
    
28. for insert
    
29. as
    
30. begin
    
31.         declare @fldid  nvarchar(50)
    
32.         set @fldid=(select fld_loginid from inserted) --获取充值ID
    
33.         declare @old datetime --获取原有到期时间
    
34.         declare @new datetime --获取本次充值时间
    
35.         declare @czje int --充值金额
    
36.         select @old=FLD_VALIDUNTIL from tbl_ACCOUNT where FLD_LOGINID=@fldid
    
37.         select @new=fld_optime,@czje=fld_czje from inserted
    
38.         if (@old<=@new)
    
39.         begin
    
40.                 update tbl_ACCOUNT set FLD_VALIDUNTIL=getdate()+@czje where FLD_LOGINID=@fldid
    
41.         end
    
42.         else
    
43.         begin
    
44.                 update tbl_ACCOUNT set FLD_VALIDUNTIL=FLD_VALIDUNTIL+@czje where FLD_LOGINID=@fldid
    
45.         end
    
46. end
    
47. go
    
48. print '执行成功！'
    

复制代码