if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExtWarehouse]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ExtWarehouse]
GO
CREATE TABLE [dbo].[ExtWarehouse] (
[AccountID] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
[Items] [varbinary] (1920) NULL ,
[Money] [int] NULL ,
[EndUseDate] [smalldatetime] NULL ,
[DbVersion] [tinyint] NULL ,
[pw] [smallint] NULL ,
[VaultID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ExtWarehouse] ADD
CONSTRAINT [DF_ExtWarehouse_Money] DEFAULT (0) FOR [Money],
CONSTRAINT [DF_ExtWarehouse_DbVersion] DEFAULT (0) FOR [DbVersion],
CONSTRAINT [DF_ExtWarehouse_pw] DEFAULT (0) FOR [pw],
CONSTRAINT [DF_ExtWarehouse_NUMBER] DEFAULT (1) FOR [VaultID]
GO
ALTER TABLE [dbo].[warehouse] ADD
[VaultID] [int] NOT NULL DEFAULT (1)
GO
CREATE PROCEDURE [dbo].[ENC_ChangeVault]
@login varchar(10),
@futureVaultID int
AS
Begin
SET NOCOUNT ON
DECLARE
@actualVaultID int,
@actualVaultItem varbinary(1920),
@futureVaultItem varbinary(1920),
@actualMoney int,
@futureMoney int
IF NOT EXISTS ( SELECT * FROM warehouse WHERE AccountID = @login )
Begin
INSERT INTO warehouse (AccountID, Money, EndUseDate, DbVersion, VaultID) VALUES (@login,0, getdate(), 3, 1)
End
IF (@futureVaultID <= 0) Begin SELECT @futureVaultID = 1 End SELECT @actualVaultID = VaultID FROM warehouse WHERE AccountID = @login IF (@actualVaultID <> @futureVaultID)
Begin
IF EXISTS ( SELECT * FROM ExtWarehouse WHERE VaultID = @futureVaultID AND AccountID = @login )
Begin
SELECT @actualVaultItem = Items FROM warehouse WHERE AccountID = @login
SELECT @actualMoney = Money FROM warehouse WHERE AccountID = @login
SELECT @futureVaultItem = Items FROM ExtWarehouse WHERE AccountID = @login and VaultID=@futureVaultID
SELECT @futureMoney = Money FROM ExtWarehouse WHERE AccountID = @login and VaultID=@futureVaultID
UPDATE warehouse SET Items = @futureVaultItem, Money = @futureMoney, VaultID = @futureVaultID WHERE AccountID = @login
IF EXISTS ( SELECT * FROM ExtWarehouse WHERE VaultID = @actualVaultID AND AccountID = @login )
Begin
UPDATE ExtWarehouse SET Items = @actualVaultItem, Money = @actualMoney WHERE AccountID = @login AND VaultID = @actualVaultID
End
ELSE
Begin
INSERT INTO ExtWarehouse (AccountID, Items, Money, EndUseDate, DbVersion, VaultID) VALUES (@login, @actualVaultItem, @actualMoney, getdate(), 3, @actualVaultID)
End
End
ELSE
Begin
SELECT @actualVaultItem = Items FROM warehouse WHERE AccountID = @login
SELECT @actualMoney = Money FROM warehouse WHERE AccountID = @login
UPDATE warehouse SET Items = NULL, Money = 0, VaultID = @futureVaultID WHERE AccountID = @login
IF EXISTS ( SELECT * FROM ExtWarehouse WHERE VaultID = @actualVaultID AND AccountID = @login )
Begin
UPDATE ExtWarehouse SET Items = @actualVaultItem, Money = @actualMoney WHERE AccountID = @login AND VaultID = @actualVaultID
End
ELSE
Begin
INSERT INTO ExtWarehouse (AccountID, Items, Money, EndUseDate, DbVersion, VaultID) VALUES (@login, @actualVaultItem, @actualMoney, getdate(), 3, @actualVaultID)
End
End
End
End
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'NewCharactersFixs' AND type = 'TR')
DROP TRIGGER NewCharactersFixs
GO
CREATE TRIGGER [NewCharactersFixs] ON [dbo].[Character]
FOR INSERT
AS
BEGIN
IF ( SELECT Class FROM INSERTED ) = 96
BEGIN
UPDATE Character SET MagicList=Null, Quest=Null, Inventory=0x00001200000000000010000000000000FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
WHERE Class='96'
END
END
