TrinityCore 3.3.5 Some very useful queries

[Portals]

I do not take credit for these queries nor the post I found this on another forum and decided to share it here since I havent seen a post like this.

Play sound on quest accept:

SET @Sound :=1234;  -- Replace with desired sound ID you wish you play.
SET @Start :=5555; -- Replace with Unique ID for end Script, set this to whatever you want.
SET @Quest :=25; -- Replace with Quest ID wished to link sound to.
INSERT INTO `quest_start_scripts` VALUES (@Start, 1, 16, @Sound, 1, 0, 0, 0, 0, 0);
UPDATE `quest_template` SET startscript=@Start WHERE id=@Quest;

Remove the "Level 80" Mail (Could be a nice feature for Instant 80 realms.):

DELETE FROM `achievement_reward` WHERE `entry` = "13";

Remove all spell costs in trainers and remove all item costs in vendors:

UPDATE `npc_trainer` SET `spellcost` = 0
UPDATE `item_template` SET `buycost` = 0

Disable a spell in a certain zone:

SET @entry :=1234;  -- Replace with the Spell ID.
SET @params_1 :=5555; -- Replace with the Zone ID.
SET @comment :=Spell Disable; -- Replace with any comment.
INSERT INTO `disables` VALUES (3, @entry, 49, 0, @params_1, @comment);

Set the same starting area for all races/classes:

SET @map :=1234;  -- Replace with the Map ID.
SET @zone :=1234; -- Replace with the Zone ID.
SET @position_x :=1234; -- Replace with Position_x.
SET @position_y :=1234;  -- Replace with the Position_y.
SET @position_z :=1234; -- Replace with the Position_z.
SET @orientation :=1234; -- Replace with Orientation.
UPDATE `playercreateinfo` SET map=@map AND zone=@zone AND position_x=@position_x AND position_y=@position_y AND position_z=@position_z AND orientation=@orientation WHERE race>=1 AND race<=11;

Set the required level on the item you want to change:

UPDATE `Item_template` SET requiredLevel = required level WHERE `Name` LIKE '%Name on item here%';

Change Minimum and maximum damage:

UPDATE `Item_template` SET dmg_min1 = Min DMG WHERE `Name` LIKE '%Name on item here%';
UPDATE `Item_template` SET dmg_max1 = Max DMG WHERE `Name` LIKE '%Name on item here%';

Choose what class the item should be available for:

UPDATE `Item_template` SET AllowableClass = Class id here WHERE `entry` LIKE '%Entry id on item  here%';

Explanation:
CONCAT merges strings together
SUBSTRING gets a part of a string, in this case the name of the item (example: the Claymore part)
LENGTH calculates the length of the string. I used it there so you can change the string to take away from the beginning of the name.
So, "Relentless " is erased and "Starter " is placed in it's place for all items with name like "Relentless Gladiator's "..:

UPDATE item_template SET name = CONCAT("Starter ", SUBSTRING(name, LENGTH("Relentless ")+1)) WHERE name like "Relentless Gladiator's %";

This sql will update your item sets stats by specific set name.
So if you want all wrathful items updated 15% stats more this is how, but if you want some other Set just replace wrathful.
and if u want more % just change number 1.15 if 25% = 1.25 etc:

UPDATE `Item_template` SET stat_value1 = (stat_value1 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value2 = (stat_value2 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value3 = (stat_value3 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value4 = (stat_value4 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value5 = (stat_value5 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value6 = (stat_value6 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value7 = (stat_value7 * 1.15) WHERE `Name` LIKE '%Wrathful%';
UPDATE `Item_template` SET stat_value8 = (stat_value8 * 1.15) WHERE `Name` LIKE '%Wrathful%';

COPY an existing display ID:

SET @myItem = '96000'; /* entry of your item */
SET @copyItem = 'Hearthstone'; /* name of item to copy */


SET @myValue = (SELECT displayid FROM item_template WHERE NAME LIKE @copyItem);
UPDATE item_template
SET displayid=@myValue
WHERE entry=@myItem;

Make a simple NPC:

SET @Entry :=50003;
SET @ModelID :=27436;
SET @Name :='Name Here';
SET @Subname :='Subname Here';
SET @NPCFLAG :=2; -- 1 is gossip / scripted npc's, 4224 is vendor
DELETE FROM `creature_template` WHERE `entry`=@Entry;
INSERT INTO `creature_template` (`entry`, `difficulty_entry_1`, `difficulty_entry_2`, `difficulty_entry_3`, `KillCredit1`, `KillCredit2`, `modelid1`, `modelid2`, `modelid3`, `modelid4`, `name`, `subname`, `IconName`, `gossip_menu_id`, `minlevel`, `maxlevel`, `exp`, `faction_A`, `faction_H`, `npcflag`, `speed_walk`, `speed_run`, `scale`, `rank`, `mindmg`, `maxdmg`, `dmgschool`, `attackpower`, `dmg_multiplier`, `baseattacktime`, `rangeattacktime`, `unit_class`, `unit_flags`, `unit_flags2`, `dynamicflags`, `family`, `trainer_type`, `trainer_spell`, `trainer_class`, `trainer_race`, `minrangedmg`, `maxrangedmg`, `rangedattackpower`, `type`, `type_flags`, `lootid`, `pickpocketloot`, `skinloot`, `resistance1`, `resistance2`, `resistance3`, `resistance4`, `resistance5`, `resistance6`, `spell1`, `spell2`, `spell3`, `spell4`, `spell5`, `spell6`, `spell7`, `spell8`, `PetSpellDataId`, `VehicleId`, `mingold`, `maxgold`, `AIName`, `MovementType`, `InhabitType`, `HoverHeight`, `Health_mod`, `Mana_mod`, `Armor_mod`, `RacialLeader`, `questItem1`, `questItem2`, `questItem3`, `questItem4`, `questItem5`, `questItem6`, `movementId`, `RegenHealth`, `mechanic_immune_mask`, `flags_extra`, `ScriptName`, `WDBVerified`) VALUES
(@Entry, 0, 0, 0, 0, 0, @ModelID, 0, 0, 0, @Name, @Subname, '', 0, 80, 80, 2, 35, 35, @NPCFLAG, 1, 1.14286, 1, 3, 10, 10, 0, 0, 1, 1000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', 0, 3, 1, 10000, 0, 100, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, '', 1);

Insert all weapons with a specific item level into the npc_vendor table:

SET @Entry :=60047;
SET @iLvL :='232';
INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`)
SELECT @Entry, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel=@iLvL and (class='2' and name!='Furious Gladiator');

This one is for off pieces:

SET @Entry :=50003;
SET @iLvL :='219';
INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`)
SELECT @Entry, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel=@iLvL and (subclass='7' or subclass='9' or subclass='8' or subclass='10' and InventoryType='2' or InventoryType='6' or InventoryType='8' or InventoryType='9' or InventoryType='11' or InventoryType='12' or InventoryType='16');

and this one is to add all shields with a specific name to a vendor:

SET @Entry :=500123;
INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`)
SELECT @Entry, 0, entry, 0, 0, 0 FROM item_template WHERE name like "%Wrathful Gladiator's%" and (class='4' and subclass='6' and sheath='4');

and the last one I have right now is meant to be created for a multivendor, it adds all the main pieces into different entries:

INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`)
SELECT 60046, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel='219' AND InventoryType='1';

INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`)
SELECT 60047, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel='219' AND InventoryType='3';

INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`)
SELECT 60048, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel='219' AND InventoryType='5';

INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`)
SELECT 60049, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel='219' AND InventoryType='7';

INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `ExtendedCost`)
SELECT 60050, 0, entry, 0, 0, 0 FROM item_template WHERE ItemLevel='219' AND InventoryType='10';

Credits: Zafire, ellrond2, xeztrixx, & Mathix.

 

[Tommy]

Wow, these are some very useful queries. Thanks for sharing.

 

[Portals]

Wow, these are some very useful queries. Thanks for sharing.

Anytime, I have learned from these and figured I would share . There was an original post on another forum but I was not sure if I should have linked it from here. But I gave credits to the owners anyways.

 

[jonmii]

Yay! Thanks for yuor sharing, it will be very usefull for me. Bookmarked thread.

Thnks

 

[Portals]

If anyone wants to contribute to the thread please feel free to post your queries and I will add them to the main post