Quest for eq change

SnickQ · 09/07/2014, 17:30

Hello
I try change a bit this

to change set with weap and cape. But i need change only epic weap 60 lvl
I try @ItemLevel like Nubness with

Nad with my query change gears but weap not or if change with out Type
For example 10 204 to 6 203 then change to only 203 and give 10 203 ;/

Code:

USE [PS_GameData]
GO

/****** Object: Trigger [dbo].[EQ_War] Script Date: 08/07/2014 22:49:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[EQ_War]
ON [dbo].[CharQuests]
AFTER INSERT
AS 
BEGIN

SET NOCOUNT ON;

DECLARE @QuestID INT = (SELECT QuestID FROM inserted)

IF @QuestID = 2237 -- QuestID
BEGIN

DECLARE @CharID INT = (SELECT CharID FROM inserted)
DECLARE	@UserUID INT = (SELECT UserUID FROM Chars WHERE CharID = @CharID)
DECLARE @TypeID INT
DECLARE @Slot TINYINT

DECLARE @ItemID TINYINT = (SELECT ItemID FROM UserStoredItems WHERE UserUID = @UserUID AND Slot in (9,10,11,12))
DECLARE @ItemLevel TINYINT = (SELECT Reqlevel FROM PS_GameDefs.dbo.Items WHERE ItemID = @ItemID)
DECLARE	@ItemType TINYINT,
@EnchantItemId INT

DECLARE @EnchantItem TABLE (EnchantID INT)
DECLARE @GearTypes TABLE (GearTypes TINYINT)

SET @ItemType = (SELECT Type FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0)
SET @EnchantItemId = (SELECT ItemID FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 1)
-- the perfect lapisia itemid, change it for you needs
INSERT INTO @EnchantItem VALUES (100241)
-- add all the gear types you want to use here

INSERT INTO @GearTypes VALUES (16),(17),(18),(19),(20),(21),(31),(32),(33),(35),(36)
UPDATE PS_GameData.dbo.CharItems
SET [TypeID]=(
CASE TypeID

 WHEN 205 THEN 35
 WHEN 216 THEN 35
 WHEN 131 THEN 35
 WHEN 120 THEN 35
 WHEN 210 THEN 35
 WHEN 125 THEN 35
 WHEN 45 THEN 35
 WHEN 40 THEN 35
 WHEN 215 THEN 35

ELSE [TypeID] 
END)
WHERE CharID = @CharID and Bag =1 and Slot in (1,2,3,4,5,6,7,8)
END

UPDATE PS_GameData.dbo.CharItems
SET Count -= 1
WHERE CharID = @CharID AND Bag = 1 and Slot = 0
END

INSERT INTO @GearTypes VALUES (2),(4),(5),(6),(7),(9),(10),(11),(12),(13),(14),(15)
If @ItemLevel >59
Begin
UPDATE PS_GameData.dbo.CharItems
SET Type=
CASE Type
 WHEN 2 THEN 6 
 WHEN 4 THEN 6
 WHEN 5 THEN 6
 WHEN 7 THEN 6
 WHEN 9 THEN 6 
 WHEN 10 THEN 6
 WHEN 11 THEN 6
 WHEN 12 THEN 6
 WHEN 14 THEN 6
 WHEN 15 THEN 6
ELSE [Type] 
END
WHERE CharID = @CharID and Bag =1 and Slot in (9,10,11,12)
END

INSERT INTO @GearTypes VALUES (2),(4),(5),(6),(7),(9),(10),(11),(12),(13),(14),(15)
UPDATE PS_GameData.dbo.CharItems
SET [TypeID]=(
CASE TypeID
 WHEN 202 THEN 204
 WHEN 203 THEN 204
 WHEN 205 THEN 204
 WHEN 206 THEN 204
 WHEN 211 THEN 204
 WHEN 213 THEN 204
 WHEN 214 THEN 204
 WHEN 215 THEN 204
ELSE [TypeID] 
END)
WHERE CharID = @CharID and Bag =1 and Slot in (9,10,11,12)

DELETE FROM PS_GameData.dbo.CharItems
WHERE CharID = @CharID AND Bag = 1 AND Slot = 0

INSERT INTO @GearTypes VALUES (24),(39)
UPDATE PS_GameData.dbo.CharItems
SET [TypeID]=(
CASE TypeID
 WHEN 158 THEN 74
 WHEN 244 THEN 74
 ELSE [TypeID] 
END)
WHERE CharID = @CharID and Bag =1 and Slot in (13)

FAIL:
DELETEQUEST:
DELETE FROM CharQuests
WHERE CharID = @CharID AND QuestID = @QuestID
GO

Ok i change a bit it and now i have:

Code:

USE [PS_GameData]
GO

/****** Object: Trigger [dbo].[EQ_War] Script Date: 08/07/2014 22:49:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[EQ_War]
ON [dbo].[CharQuests]
AFTER INSERT
AS 
BEGIN

SET NOCOUNT ON;

DECLARE @QuestID INT = (SELECT QuestID FROM inserted)

IF @QuestID = 2237 -- QuestID
BEGIN

DECLARE @CharID INT = (SELECT CharID FROM inserted)
DECLARE	@UserUID INT = (SELECT UserUID FROM Chars WHERE CharID = @CharID)
DECLARE @TypeID INT
DECLARE @Slot TINYINT

DECLARE @ItemID TINYINT = (SELECT ItemID FROM UserStoredItems WHERE UserUID = @UserUID AND Slot in (9,10,11,12))
DECLARE @ItemLevel TINYINT = (SELECT Reqlevel FROM PS_GameDefs.dbo.Items WHERE ItemID = @ItemID)
DECLARE @SlotChar TINYINT = (SELECT UserUID FROM Chars WHERE CharID = @CharID)
DECLARE	@ItemType TINYINT,
@EnchantItemId INT

DECLARE @EnchantItem TABLE (EnchantID INT)
DECLARE @GearTypes TABLE (GearTypes TINYINT)

SET @ItemType = (SELECT Type FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 0)
SET @EnchantItemId = (SELECT ItemID FROM UserStoredItems WHERE UserUID = @UserUID AND Slot = 1)
-- the perfect lapisia itemid, change it for you needs
INSERT INTO @EnchantItem VALUES (100241)
-- add all the gear types you want to use here

INSERT INTO @GearTypes VALUES (16),(17),(18),(19),(20),(21),(31),(32),(33),(35),(36)
UPDATE PS_GameData.dbo.CharItems
SET [TypeID]=(
CASE TypeID

 WHEN 205 THEN 35
 WHEN 216 THEN 35
 WHEN 131 THEN 35
 WHEN 120 THEN 35
 WHEN 210 THEN 35
 WHEN 125 THEN 35
 WHEN 45 THEN 35
 WHEN 40 THEN 35
 WHEN 215 THEN 35

ELSE [TypeID] 
END)
WHERE CharID = @CharID and Bag =1 and Slot in (1,2,3,4,5,6,7,8)
END

INSERT INTO @GearTypes VALUES (2),(4),(5),(6),(7),(9),(10),(11),(12),(13),(14),(15)

UPDATE PS_GameData.dbo.CharItems 
SET Type = '6', TypeID = '204'  
WHERE Type BETWEEN 2 and 15 and TypeID BETWEEN 202 and 215 and CharID = @CharID and Bag =1 and Slot in (9,10,11,12)

INSERT INTO @GearTypes VALUES (24),(39)
UPDATE PS_GameData.dbo.CharItems
SET [TypeID]=(
CASE TypeID
 WHEN 158 THEN 74
 WHEN 244 THEN 74
 ELSE [TypeID] 
END)
WHERE CharID = @CharID and Bag =1 and Slot in (13)


UPDATE PS_GameData.dbo.CharItems
SET Count -= 1
WHERE CharID = @CharID AND Bag = 1 and Slot = 0
END


DELETE FROM PS_GameData.dbo.CharItems
WHERE CharID = @CharID AND Bag = 1 AND Slot = 0

FAIL:
DELETEQUEST:
DELETE FROM CharQuests
WHERE CharID = @CharID AND QuestID = @QuestID
GO

Thanks for Nubness and AdmCritical