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')