CREATE TRIGGER TG_AutoChangePearlDiamond ON dbo.tblMail1
For Insert
AS
declare @iTitle varchar(80)
declare @Sender varchar(16)
declare @Recipient varchar(16)
declare @iContent varchar(200)
declare @Time datetime
declare @iCount int
declare @DHWP varchar(10)
declare @iDHWPDM int
declare @iDHXYSL int
declare @iLL int
declare @iYZ int
declare @iMJ int
declare @iGQ int
declare @iTmpS varchar(200)
declare @iLen int
declare @iID int
Select @iTitle=Title,@Sender=Sender,@Recipient=Recipient,@iContent=Content,@Time=Time From Inserted
set @iTitle=ltrim(rtrim(@iTitle))
set @iContent=ltrim(rtrim(@iContent))
--判断发信人和收信人的名字是否相同，不相同直接结束
if (@Sender<>@Recipient) return
--判断标题是否少于3个字，少于则直接结束
if (len(@iTitle)<3) return
--判断标题前2个字是否为兑换，不是则直接结束
if (left(@iTitle,2)<>*兑换*) return
--根据兑换的目标确定物品和需要的珍珠钻石数量基数[武器6装备3，后面再根据次数确定剩余部分]，格式不对直接结束
set @DHWP=right(@iTitle,len(@iTitle)-2)
if (@DHWP=*刀*)
begin
set @iDHWPDM=190
end
else if (@DHWP=*矛*)
begin
set @iDHWPDM=192
end
else if (@DHWP=*杖*)
begin
set @iDHWPDM=191
end
else if (@DHWP=*枪*)
begin
set @iDHWPDM=194
end
else if (@DHWP=*弓*)
begin
set @iDHWPDM=193
end
else if (@DHWP=*戒指*)
begin
set @iDHWPDM=187
end
else if (@DHWP=*项链*)
begin
set @iDHWPDM=188
end
else if (@DHWP=*衣服* OR @DHWP=*甲*)
begin
set @iDHWPDM=181
end
else if (@DHWP=*裤子*)
begin
set @iDHWPDM=182
end
else if (@DHWP=*帽子* OR @DHWP=*头盔*)
begin
set @iDHWPDM=180
end
else if (@DHWP=*手套*)
begin
set @iDHWPDM=185
end
else if (@DHWP=*盾牌*)
begin
set @iDHWPDM=184
end
else if (@DHWP=*腰带*)
begin
set @iDHWPDM=186
end
else if (@DHWP=*鞋子* OR @DHWP=*鞋*)
begin
set @iDHWPDM=183
end
else return

--检查信内容格式是否符合要求，根据邮件内容计算总次数并记录目标生化四围
if (@iContent is null)
begin
set @iContent=*信内容格式不符合要求！
"[力量-意志-敏捷-骨气]"
[单项不小于10，总加等于129 139 149 159 ] *
update tblMail1 Set Content=@iContent,Line=20 Where Sender=@Sender AND Recipient=@Recipient AND Time=@Time
return
end
if (@iContent=**)
begin
set @iContent=*信内容格式不符合要求！
"[力量-意志-敏捷-骨气]"
[单项不小于10，总加等于129 139 149 159 ] *
update tblMail1 Set Content=@iContent,Line=20 Where Sender=@Sender AND Recipient=@Recipient AND Time=@Time
return
end
if (len(@iContent)<9 Or Left(@iContent,1)<>*[*)
begin
set @iContent=*信内容格式不符合要求！
"[力量-意志-敏捷-骨气]"
[单项不小于10，总加等于129 139 149 159 ] *
update tblMail1 Set Content=@iContent,Line=20 Where Sender=@Sender AND Recipient=@Recipient AND Time=@Time
return
end
set @iContent=right(@iContent,len(@iContent)-1)
--力量
set @iLen=0
set @iTmpS=**
while (@iContent<>**)
begin
if (left(@iContent,1)=*-*) Break
set @iLen =@iLen +1
set @iTmpS=@iTmpS + left(@iContent,1)
set @iContent=right(@iContent,len(@iContent)-1)
end
if (@iContent=** OR Isnumeric(@iTmpS)<>1 or @iLen>3 )
begin
set @iContent=*信内容格式不符合要求！
"[力量-意志-敏捷-骨气]"
[单项不小于10，总加等于129 139 149 159 ]
*
update tblMail1 Set Content=@iContent,Line=20 Where Sender=@Sender AND Recipient=@Recipient AND Time=@Time
return
end
set @iLL=Cast(@iTmpS AS float)
set @iContent=right(@iContent,len(@iContent)-1)
--意志
set @iLen=0
set @iTmpS=**
while (@iContent<>**)
begin
if (left(@iContent,1)=*-*) Break
set @iLen=@iLen+1
set @iTmpS=@iTmpS + left(@iContent,1)
set @iContent=right(@iContent,len(@iContent)-1)
end

if (@iContent=** OR Isnumeric(@iTmpS)<>1 or @iLen>3)
begin
set @iContent=*信内容格式不符合要求！
"[力量-意志-敏捷-骨气]"
[单项不小于10，总加等于129 139 149 159 ]
*
update tblMail1 Set Content=@iContent,Line=20 Where Sender=@Sender AND Recipient=@Recipient AND Time=@Time
return
end
set @iYZ=Cast(@iTmpS AS float)
set @iContent=right(@iContent,len(@iContent)-1)
--敏捷
set @iLen=0
set @iTmpS=**
while (@iContent<>**)
begin
if (left(@iContent,1)=*-*) Break
set @iLen=@iLen+1
set @iTmpS=@iTmpS + left(@iContent,1)
set @iContent=right(@iContent,len(@iContent)-1)
end
if (@iContent=** OR Isnumeric(@iTmpS)<>1 or @iLen>3)
begin
set @iContent=*信内容格式不符合要求！
"[力量-意志-敏捷-骨气]"
[单项不小于10，总加等于129 139 149 159 ]
*
update tblMail1 Set Content=@iContent,Line=20 Where Sender=@Sender AND Recipient=@Recipient AND Time=@Time
return
end
set @iMJ=Cast(@iTmpS AS float)
set @iContent=right(@iContent,len(@iContent)-1)
--骨气
set @iLen=0
set @iTmpS=**
while (@iContent<>**)
begin
if (left(@iContent,1)=*]*) Break
set @iLen=@iLen+1
set @iTmpS=@iTmpS + left(@iContent,1)
set @iContent=right(@iContent,len(@iContent)-1)
end
if (Isnumeric(@iTmpS)<>1 or @iLen>3)
begin
set @iContent=*信内容格式不符合要求！
"[力量-意志-敏捷-骨气]"
[单项不小于10，总加等于129 139 149 159 ]
*
update tblMail1 Set Content=@iContent,Line=20 Where Sender=@Sender AND Recipient=@Recipient AND Time=@Time
return
end
set @iGQ=Cast(@iTmpS AS float)
if (@iLL<10 OR @iYZ<10 OR @iMJ<10 OR @iGQ<10)
begin
set @iContent=*信内容格式不符合要求！"信内容格式不符合要求！
"[力量-意志-敏捷-骨气]"
[单项不小于10，总加等于129 139 149 159 ]
*
update tblMail1 Set Content=@iContent,Line=20 Where Sender=@Sender AND Recipient=@Recipient AND Time=@Time
return
end
if (@iLL+@iYZ+@iMJ+@iGQ=135)
set @iDHXYSL=9
else if (@iLL+@iYZ+@iMJ+@iGQ=125)
set @iDHXYSL=3
else if (@iLL+@iYZ+@iMJ+@iGQ=145)
set @iDHXYSL=27
else if (@iLL+@iYZ+@iMJ+@iGQ=155)
set @iDHXYSL=81
else
begin
set @iContent=*信内容格式不符合要求！
"[力量-意志-敏捷-骨气]"
[单项不小于10，总加等于129 139 149 159 ]
*
update tblMail1 Set Content=@iContent,Line=20 Where Sender=@Sender AND Recipient=@Recipient AND Time=@Time
return
end
if (@iDHWPDM=191)
set @iDHXYSL=2*@iDHXYSL
else if (@iDHWPDM>=190 AND @iDHWPDM<=194)
set @iDHXYSL=3*@iDHXYSL
--检查F3和F8中贝壳的个数
select @iCount=(select Count(GameID) AS iCount From tblSpecialItem1
Where ItemKind=6 AND Position=1 AND GameID=@Recipient AND (WindowKind=1 OR WindowKind=3) AND ItemDurability>=4 AND (ItemIndex=43))
if (@iCount<@iDHXYSL)
begin
set @iContent=*贝壳数量不够！
*
update tblMail1 Set Content=@iContent,Line=20 Where Sender=@Sender AND Recipient=@Recipient AND Time=@Time
return
end
--核对结束，开始兑换

begin transaction
--删除F3和F8中的物品
if (@iDHXYSL=3)
delete tblSpecialItem1 Where ID IN (Select Top 3 ID From tblSpecialItem1 Where ItemKind=6 AND
Position=1 AND GameID=@Recipient AND (WindowKind=1 OR WindowKind=3)
AND ItemDurability>=4 AND (ItemIndex=43))
else if (@iDHXYSL=9)
delete tblSpecialItem1 Where ID IN (Select Top 9 ID From tblSpecialItem1 Where ItemKind=6 AND
Position=1 AND GameID=@Recipient AND (WindowKind=1 OR WindowKind=3)
AND ItemDurability>=4 AND (ItemIndex=43))
else if (@iDHXYSL=27)
delete tblSpecialItem1 Where ID IN (Select Top 27 ID From tblSpecialItem1 Where ItemKind=6 AND
Position=1 AND GameID=@Recipient AND (WindowKind=1 OR WindowKind=3)
AND ItemDurability>=4 AND (ItemIndex=43))
else if (@iDHXYSL=81)
delete tblSpecialItem1 Where ID IN (Select Top 81 ID From tblSpecialItem1 Where ItemKind=6 AND
Position=1 AND GameID=@Recipient AND (WindowKind=1 OR WindowKind=3)
AND ItemDurability>=4 AND (ItemIndex=43))
else if (@iDHXYSL=6)
delete tblSpecialItem1 Where ID IN (Select Top 6 ID From tblSpecialItem1 Where ItemKind=6 AND
Position=1 AND GameID=@Recipient AND (WindowKind=1 OR WindowKind=3)
AND ItemDurability>=4 AND (ItemIndex=43))
else if (@iDHXYSL=18)
delete tblSpecialItem1 Where ID IN (Select Top 18 ID From tblSpecialItem1 Where ItemKind=6 AND
Position=1 AND GameID=@Recipient AND (WindowKind=1 OR WindowKind=3)
AND ItemDurability>=4 AND (ItemIndex=43))
else if (@iDHXYSL=54)
delete tblSpecialItem1 Where ID IN (Select Top 54 ID From tblSpecialItem1 Where ItemKind=6 AND
Position=1 AND GameID=@Recipient AND (WindowKind=1 OR WindowKind=3)
AND ItemDurability>=4 AND (ItemIndex=43))
else if (@iDHXYSL=162)
delete tblSpecialItem1 Where ID IN (Select Top 162 ID From tblSpecialItem1 Where ItemKind=6 AND
Position=1 AND GameID=@Recipient AND (WindowKind=1 OR WindowKind=3)
AND ItemDurability>=4 AND (ItemIndex=43))
else if (@iDHXYSL=243)
delete tblSpecialItem1 Where ID IN (Select Top 243 ID From tblSpecialItem1 Where ItemKind=6 AND
Position=1 AND GameID=@Recipient AND (WindowKind=1 OR WindowKind=3)
AND ItemDurability>=4 AND (ItemIndex=43))

--发放生化
insert into tblSpecialItem1 (ItemKind, ItemIndex, ItemDurability, Position, Map, X, Y, TileKind, GameID, WindowKind, WindowIndex, MiscTime, AttackGrade, StrengthGrade, SpiritGrade, DexterityGrade, PowerGrade)
values (6, @iDHWPDM, 4, 2, 1, 100, 100, 1, @Recipient , 100, 0, @Time , 4,@iLL,@iYZ,@iMJ,@iGQ)
set @iContent=*兑换成功！请查收物品，有问题请联系GM *
update tblMail1 Set Content=@iContent,Line=20 Where Sender=@Sender AND Recipient=@Recipient AND Time=@Time
commit transaction
--------------------------------------------------------------------------------

查询分析器一分析全是错误，无言