自动处理复制问题的SQL脚本

Published: Dec 13, 2023   Comment: No Comments

游戏数据库 如果觉得对你有帮助，并想支持我，可以适当打赏以作勉励，点击打赏

它使用了一个表来记录全部的物品ID，并且记录了物品最后的拥有者
例如玩家A进行了复制，当他复制完以后，确实一个东西在仓库，一个东西在包里，但这包里的东西即不能存仓，也不能交易给别人，他自已也不能下线，打怪也不行。只要一有这些动作，等待他的将是自动清空仓库。而且A把东西交易给了别人，别人能收到正确的物品，而A自已的复制品会被清空。

if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[trg_CheckSameID]) and OBJECTPROPERTY(id, NIsTrigger) = 1)
drop trigger [dbo].[trg_CheckSameID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[AllItemsLog]) and OBJECTPROPERTY(id, NIsUserTable) = 1)
drop table [dbo].[AllItemsLog]
GO

if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[CopyLog]) and OBJECTPROPERTY(id, NIsUserTable) = 1)
drop table [dbo].[CopyLog]
GO

CREATE TABLE [dbo].[AllItemsLog] (
[items_id] [int] IDENTITY (1, 1) NOT NULL ,
[items_type] [binary] (1) NOT NULL ,
[items_serial] [binary] (4) NOT NULL ,
[items_acid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CopyLog] (
[copy_id] [int] IDENTITY (1, 1) NOT NULL ,
[copy_acid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[copy_whdata] [binary] (1200) NOT NULL , 内容来自dedecms
[copy_date] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AllItemsLog] ADD
CONSTRAINT [DF_CT_ITEM_item] DEFAULT (0) FOR [items_type],
CONSTRAINT [DF_CT_ITEM_itemid] DEFAULT (0) FOR [items_serial],
CONSTRAINT [DF_CT_ITEM_itemrole] DEFAULT () FOR [items_acid]
GO

CREATE INDEX [IX_CT_ITEM] ON [dbo].[AllItemsLog]([items_type], [items_serial]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CopyLog] ADD
CONSTRAINT [DF_CopyLog_copy_date] DEFAULT (getdate()) FOR [copy_date]
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [dbo].[trg_CheckSameID] ON [dbo].[character]
FOR UPDATE
AS
BEGIN
IF UPDATE(inventory)
BEGIN
SET NOCOUNT ON
DECLARE
@wh_acid varchar(10),
@wh_data binary(1200),
@wh_type binary(1),
@wh_serial binary(4),

@cr_user varchar(10),
@cr_acid varchar(10),
@cr_data binary(760),
@cr_type binary(1),
@cr_serial binary(4),

@al_acid varchar(10),
@j int,
@k int,
@find bit

SELECT @cr_acid=i.accountid,
@cr_data=i.inventory
FROM inserted i

SET @j=0
SET @find=0

WHILE @j<76 AND @cr_data IS NOT NULL
BEGIN
SET @cr_type=SUBSTRING(@cr_data,@j*10+1,1)
SET @cr_serial=SUBSTRING(@cr_data,@j*10+4,4)
IF @cr_type<>0xFF AND @cr_serial<>0x00000000
BEGIN
SELECT @al_acid=items_acid FROM allitemslog WHERE items_type=@cr_type AND items_serial=@cr_serial

--给物品加上新主人标识
IF @al_acid IS NULL
INSERT INTO allitemslog (items_type,items_serial,items_acid) VALUES (@cr_type,@cr_serial,@cr_acid)
ELSE
BEGIN
UPDATE allitemslog SET items_acid=@cr_acid WHERE items_type=@cr_type AND items_serial=@cr_serial

--从旧主人仓库中查找是否还有同样ID物品
SELECT @wh_data=items FROM warehouse WHERE accountid=@al_acid

SET @k=0
WHILE @k<120 AND @wh_data IS NOT NULL
BEGIN
SET @wh_type=SUBSTRING(@wh_data,@k*10+1,1)
SET @wh_serial=SUBSTRING(@wh_data,@k*10+4,4)
IF @wh_type=@cr_type AND @wh_serial=@cr_serial 内容来自dedecms
SET @find=1
SET @k=@k+1
END
END
END
SET @j=@j+1
END
--发现就清仓库
IF @find=1
BEGIN
INSERT INTO copylog (copy_whdata,copy_acid,copy_date) VALUES (@wh_data,@al_acid,getdate())
UPDATE warehouse SET items=NULL WHERE accountid=@al_acid
END
SET NOCOUNT OFF
END
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO