A self-written trigger

Alter trigger [inserttopic] -- check whether the gold coin is sufficient before posting. If it is sufficient, post and reward points.
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 [email protected] -- total user points
Select @ goldsum = sum (bbsgold) from bbsgold where [email protected] -- total user gold coins
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 = 'ordinary title') -- publish a topic post and make a payment-8 gold coins + 12 points
Begin

If (@ goldsum> = 8) -- if the gold coin is 8 enough, you can post it.
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 @ [email protected] @ identity

Insert into [wts_community]. [DBO]. [bbsgold]
([Uid]
, [Bbsgold]
, [Datetime]
, [Itemtype])
Values
(@ Uid
,-8
, Getdate ()
, 'Post daily ') Select @ [email protected] @ identity
Insert into [wts_community]. [DBO]. [bbsintegral]
([Uid]
, [Bbsintegral]
, [Datetime]
, [Itemtype])
Values
(@ Uid
, 12
, Getdate ()
, 'Post daily ') Select @ [email protected] @ identity

If (@ inserttopicidentity! = 0 and @ insertgoldidentity! = 0 and @ insertgoldidentity! = 0)
Begin
Print 'you have successfully published a regular post, rewarded with 12 points, and deducted 8 gold coins'
End
Else
Begin
Print 'failed to publish common posts! '
End
End
Else
Begin

Print 'Insufficient coins'
-- Raiserror ('Insufficient coins ', 16, 1 );
-- Rollback Tran;

end
else if (@ topictype = 'topic title ') -- publish a regular post with a payment-10 gold coins + 10 Points
begin
If (@ goldsum> = 10) -- if the gold coin is sufficient for 10, then you can post
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 @ [email protected] @ identity

insert into [wts_community]. [DBO]. [bbsgold]
([uid]
, [bbsgold]
, [datetime]
, [itemtype])
values
(@ uid
,-10
, getdate ()
, 'Post every day ') select @ [email protected] @ identity
insert into [wts_community]. [DBO]. [bbsintegral]
([uid]
, [bbsintegral]
, [datetime]
, [itemtype])
values
(@ uid
, 10
, getdate ()
, 'Post every day') Select @ [email protected] @ ident Ity
If (@ inserttopicidentity! = 0 and @ insertgoldidentity! = 0 and @ insertintegralidentity! = 0)
begin
Print 'your topic post has been published successfully! Reward 10 points, deduct 10 gold coins '
end
else
begin
Print' your topic post failed! '
end
else
begin

Print 'Insufficient coins '
-- raiserror ('Insufficient coins', 16, 1);
-- rollback Tran;
end
else if (@ topictype = 'Post question ') -- publish a question post: payment-12 gold coins + 12 points
begin
If (@ goldsum> = 12) -- if the gold coin is 12 enough, then you can post
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 @ [email protected] @ identity

Insert into [wts_community]. [DBO]. [bbsgold]
([Uid]
, [Bbsgold]
, [Datetime]
, [Itemtype])
Values
(@ Uid
,-12
, Getdate ()
, 'Post daily ') Select @ [email protected] @ identity
Insert into [wts_community]. [DBO]. [bbsintegral]
([Uid]
, [Bbsintegral]
, [Datetime]
, [Itemtype])
Values
(@ Uid
, 12
, Getdate ()
, 'Post daily ') Select @ [email protected] @ identity
If (@ inserttopicidentity! = 0 and @ insertgoldidentity! = 0 and @ insertintegralidentity! = 0)
Begin
Print 'your post has been published! Reward 12 points, deduct 12 gold coins! '
End
Else
Begin
Print 'the post you posted failed! '
End
End
Else
Begin

Print 'Insufficient coins'
-- Raiserror ('Insufficient coins ', 16, 1 );
-- Rollback Tran; The transaction ends in the trigger. The batch processing has been aborted.
End
End

End

-- Test

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
('Chunadao'
, 'Test'
, 'Test'
, 125
,''
, 'Chunadao'
, 0
, 0
, 0
, '2017/9 00:00:00'
, 0
, 0
, 0
, 0
, 'Question requests'
,''
, 10
, '2017/9 00:00:00'
, 0
, '2017/9 00:00:00 ')

 

A self-written trigger