ALTER TRIGGER [insertTopic] --发帖前检查金币是否足够，若足够的话，则可以发帖，并且奖励积分
ON [dbo].[bbsTopic]
instead of insert
AS
BEGIN
declare @topictype nvarchar(50)
,@integralSum int
,@goldSum numeric(18,2)
,@code nvarchar(50)
,@title nvarchar(max)
,@Tcontent nvarchar(max)
,@uid int
,@ucode nvarchar(50)
,@ccode nvarchar(50)
,@replyCount int
,@clickCount int
,@Tstate int
,@Ttime datetime
,@isRecommand bit
,@isessential bit
,@customSort int
,@belongTo int
,@ttcode nvarchar(50)
,@jieriCode nvarchar(50)
,@xuanshangjinbi int
,@zhidingTime datetime
,@isPingBi bit
,@editTime datetime
,@insertTopicIdentity int
,@insertGoldIdentity int
,@insertIntegralIdentity int;
select @topictype=ttcode from inserted
select @uid=uid from inserted
select @integralSum=sum(bbsIntegral) from bbsIntegral where uid=@uid --用户总的积分
select @goldSum=sum(bbsGold) from bbsGold where uid=@uid --用户总的金币
SELECT @code=[code]
,@title=[title]
,@Tcontent=[Tcontent]
,@uid=[uid]
,@ucode=[ucode]
,@ccode=[ccode]
,@replyCount=[replyCount]
,@clickCount=[clickCount]
,@Tstate=[Tstate]
,@Ttime=[Ttime]
,@isRecommand=[isRecommand]
,@isessential=[isessential]
,@customSort=[customSort]
,@belongTo=[belongTo]
,@ttcode=[ttcode]
,@jieriCode=[jieriCode]
,@xuanshangjinbi=[xuanshangjinbi]
,@zhidingTime=[zhidingTime]
,@isPingBi=[isPingBi]
,@editTime=[editTime]
FROM inserted
if(@topictype='普通帖') --发布主题帖一篇支付 - 8金币 +12积分
begin
if(@goldSum>=8) --如果金币足够8，那么就可以发帖了
begin
insert into bbsTopic values(@code
,@title
,@Tcontent
,@uid
,@ucode
,@ccode
,@replyCount
,@clickCount
,@Tstate
,@Ttime
,@isRecommand
,@isessential
,@customSort
,@belongTo
,@ttcode
,@jieriCode
,@xuanshangjinbi
,@zhidingTime
,@isPingBi
,@editTime) select @insertTopicIdentity=@@identity
INSERT INTO [wts_Community].[dbo].[bbsGold]
([uid]
,[bbsGold]
,[datetime]
,[itemtype])
VALUES
(@uid
,-8
,getdate()
,'每日发帖') select @insertGoldIdentity=@@identity
INSERT INTO [wts_Community].[dbo].[bbsIntegral]
([uid]
,[bbsIntegral]
,[datetime]
,[itemtype])
VALUES
(@uid
,12
,getdate()
,'每日发帖') select @insertIntegralIdentity=@@identity
if(@insertTopicIdentity!=0 and @insertGoldIdentity!=0 and @insertGoldIdentity!=0)
begin
print '您成功发布了一篇普通帖，奖励12积分，扣除8金币'
end
else
begin
print '发布普通帖失败！'
end
end
else
begin
print '金币不够'
--raisError('金币不够', 16, 1);
--rollback tran;
end
end
else if(@topictype='主题帖') --发布普通帖一篇支付 - 10金币 +10积分
begin
if(@goldSum>=10) --如果金币足够10，那么就可以发帖了
begin
insert into bbsTopic values(@code
,@title
,@Tcontent
,@uid
,@ucode
,@ccode
,@replyCount
,@clickCount
,@Tstate
,@Ttime
,@isRecommand
,@isessential
,@customSort
,@belongTo
,@ttcode
,@jieriCode
,@xuanshangjinbi
,@zhidingTime
,@isPingBi
,@editTime) select @insertTopicIdentity=@@identity
INSERT INTO [wts_Community].[dbo].[bbsGold]
([uid]
,[bbsGold]
,[datetime]
,[itemtype])
VALUES
(@uid
,-10
,getdate()
,'每日发帖') select @insertGoldIdentity=@@identity
INSERT INTO [wts_Community].[dbo].[bbsIntegral]
([uid]
,[bbsIntegral]
,[datetime]
,[itemtype])
VALUES
(@uid
,10
,getdate()
,'每日发帖') select @insertIntegralIdentity=@@identity
if(@insertTopicIdentity!=0 and @insertGoldIdentity!=0 and @insertIntegralIdentity!=0)
begin
print '您发布的主题帖成功！奖励10积分，扣除10金币'
end
else
begin
print '您发布的主题帖失败！'
end
end
else
begin
print '金币不够'
--raisError('金币不够', 16, 1);
--rollback tran;
end
end
else if(@topictype='提问帖') --发布提问帖一篇支付 - 12金币 +12积分
begin
if(@goldSum>=12) --如果金币足够12，那么就可以发帖了
begin
insert into bbsTopic values(@code
,@title
,@Tcontent
,@uid
,@ucode
,@ccode
,@replyCount
,@clickCount
,@Tstate
,@Ttime
,@isRecommand
,@isessential
,@customSort
,@belongTo
,@ttcode
,@jieriCode
,@xuanshangjinbi
,@zhidingTime
,@isPingBi
,@editTime) select @insertTopicIdentity=@@identity
INSERT INTO [wts_Community].[dbo].[bbsGold]
([uid]
,[bbsGold]
,[datetime]
,[itemtype])
VALUES
(@uid
,-12
,getdate()
,'每日发帖') select @insertGoldIdentity=@@identity
INSERT INTO [wts_Community].[dbo].[bbsIntegral]
([uid]
,[bbsIntegral]
,[datetime]
,[itemtype])
VALUES
(@uid
,12
,getdate()
,'每日发帖') select @insertIntegralIdentity=@@identity
if(@insertTopicIdentity!=0 and @insertGoldIdentity!=0 and @insertIntegralIdentity!=0)
begin
print '您发布的提问帖成功！奖励12积分，扣除12金币！'
end
else
begin
print '您发布的提问帖失败！'
end
end
else
begin
print '金币不够'
--raisError('金币不够', 16, 1);
--rollback tran; 事务在触发器中结束.批处理已中止.
end
end
END
--测试
INSERT INTO [wts_Community].[dbo].[bbsTopic]
([code]
,[title]
,[Tcontent]
,[uid]
,[ucode]
,[ccode]
,[replyCount]
,[clickCount]
,[Tstate]
,[Ttime]
,[isRecommand]
,[isessential]
,[customSort]
,[belongTo]
,[ttcode]
,[jieriCode]
,[xuanshangjinbi]
,[zhidingTime]
,[isPingBi]
,[editTime])
VALUES
('chadao'
,'test'
,'test'
,125
,''
,'chadao'
,0
,0
,0
,'2014/10/9 00:00:00'
,0
,0
,0
,0
,'提问帖'
,''
,10
,'2014/10/9 00:00:00'
,0
,'2014/10/9 00:00:00')