NOTE: I don’t update this regularly and it is possible that the DB might change. If you get errors on import and no appropriate archive exists, feel free to fix the errors and post your corrections. Think of it as a learning experience.
Thought I’d share this SQL, I got sick of running to the Exodar so I added a copy of the JC trainer and vendor to Stormwind.
They are added on the porch outside Ol’ Emma’s house (outside Cathedral Square). Just exit the Trade District through the back, cross the bridge toward Cathedral Square and turn right. Pass the entrance to Cathedral Square and they will be standing on the porch of the only house that you can enter (Ol’ Emma paths in and out of this house as she walks around Stormwind).
IMPORTANT:
These NPCs are assigned an ID dynamically so they won’t overwrite any existing data. They may however be overwritten by Trinity during updates so PLEASE follow these guidelines:
[ul][li]If you change their names in the SQL or DB you MUST change them in the “UNDO” as well[/li][li]AlWAYS run the UNDO before updating your world DB and rerun the SQL after to reinsert them with new IDs[/li]
[/ul]
Current Version
[ul][li]Updated to reflect the removal of faction_A, faction_H and WDBVerified fields[/li][/ul]
This SQL creates them:
trainer_jewelcrafting_stormwind.sql
– ### Adds a Jewelcrafting trainer and vendor to Stormwind
– Note: This is for newer databases that use the faction field instead of faction_A, faction_H
– and VerifiedBuild instead of WDBVerified
SET @max_ct_entry=(SELECT MAX(entry) FROM creature_template);
SET @max_cr_guid=(SELECT MAX(guid) FROM creature);
– ### Jewelcrafting Trainer
– creature_template
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, npcflag, speed_walk, speed_run, scale, rank, mindmg, maxdmg, dmgschool, attackpower, dmg_multiplier, baseattacktime, rangeattacktime, unit_class, unit_flags, 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, Health_mod, Mana_mod, Armor_mod, RacialLeader, questItem1, questItem2, questItem3, questItem4, questItem5, questItem6, movementId, RegenHealth, mechanic_immune_mask, flags_extra, ScriptName, VerifiedBuild)
values(@max_ct_entry+1,‘0’,‘0’,‘0’,‘0’,‘0’,‘19195’,‘0’,‘0’,‘0’,‘Astawi’,‘Jewelcrafting Trainer’,NULL,‘8382’,‘30’,‘30’,‘0’,‘1638’,‘81’,‘1’,‘1.14286’,‘1’,‘0’,‘39’,‘52’,‘0’,‘96’,‘1’,‘2000’,‘0’,‘2’,‘512’,‘8’,‘0’,‘2’,‘0’,‘0’,‘0’,‘27’,‘40’,‘8’,‘7’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘’,‘1’,‘3’,‘1’,‘1’,‘1’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘1’,‘0’,‘2’,‘’,‘10001’);
– creature_equip_template
– creature_template.entry, outfit ID, right hand, left hand, ranged
– Note: outfit id must start at 1 and can be incremented to allow for random outfits
insert into creature_equip_template (entry, id, itemEntry1, itemEntry2, itemEntry3)
values(@max_ct_entry+1,‘1’,‘0’,‘0’,‘0’);
– creature (spawns)
insert into creature (guid, id, map, spawnMask, phaseMask, modelid, equipment_id, position_x, position_y, position_z, orientation, spawntimesecs, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags)
values(@max_cr_guid+1,@max_ct_entry+1,‘0’,‘1’,‘1’,‘0’,‘0’,‘-8653.34’,‘670.949’,‘101.759’,‘3.80677’,‘300’,‘0’,‘0’,‘811’,‘852’,‘0’,‘0’,‘0’,‘0’);
– npc_trainer (copy from the trainer in Exodar: creature_template.entry = 19778)
INSERT INTO npc_trainer (entry,spell,spellcost,reqskill,reqskillvalue,reqlevel)
SELECT (@max_ct_entry+1),spell,spellcost,reqskill,reqskillvalue,reqlevel
FROM npc_trainer
WHERE entry=19778;
– ### Jewelcrafting Supplies
– creature_template
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, npcflag, speed_walk, speed_run, scale, rank, mindmg, maxdmg, dmgschool, attackpower, dmg_multiplier, baseattacktime, rangeattacktime, unit_class, unit_flags, 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, Health_mod, Mana_mod, Armor_mod, RacialLeader, questItem1, questItem2, questItem3, questItem4, questItem5, questItem6, movementId, RegenHealth, mechanic_immune_mask, flags_extra, ScriptName, VerifiedBuild)
values(@max_ct_entry+2,‘0’,‘0’,‘0’,‘0’,‘0’,‘17222’,‘0’,‘0’,‘0’,‘Rahl’,‘Jewelcrafting Supplies’,NULL,‘0’,‘50’,‘50’,‘0’,‘1638’,‘128’,‘1’,‘1.14286’,‘1’,‘0’,‘79’,‘104’,‘0’,‘194’,‘1’,‘1500’,‘0’,‘2’,‘512’,‘8’,‘0’,‘0’,‘0’,‘0’,‘0’,‘54’,‘79’,‘18’,‘7’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘’,‘1’,‘3’,‘1’,‘1’,‘1’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘1’,‘0’,‘2’,‘’,‘10001’);
– creature_equip_template
– creature_template.entry, outfit ID, right hand, left hand, ranged
– Note: outfit id must start at 1 and can be incremented to allow for random outfits
insert into creature_equip_template (entry, id, itemEntry1, itemEntry2, itemEntry3)
values(@max_ct_entry+2,‘1’,‘0’,‘0’,‘0’);
– creature (spawns)
insert into creature (guid, id, map, spawnMask, phaseMask, modelid, equipment_id, position_x, position_y, position_z, orientation, spawntimesecs, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags)
values(@max_cr_guid+2,@max_ct_entry+2,‘0’,‘1’,‘1’,‘0’,‘0’,‘-8653.12’,‘666.96’,‘101.76’,‘2.97953’,‘300’,‘0’,‘0’,‘1772’,‘1807’,‘0’,‘0’,‘0’,‘0’);
– npc_vendor (copy the items from the vendor in The Exodar: creature_template.entry = 17512)
INSERT INTO npc_vendor (entry,slot,item,maxcount,incrtime,ExtendedCost)
SELECT (@max_ct_entry+2), slot, item,maxcount,incrtime,ExtendedCost
FROM npc_vendor
WHERE entry=17512;
This SQL removes them:
trainer_jewelcrafting_stormwind_UNDO.sql
– ### Removes the Jewelcrafting trainer and vendor from Stormwind
– ### Jewelcrafting Trainer
SET @ct_entry=(SELECT entry FROM creature_template WHERE name=‘Astawi’ AND subname=‘Jewelcrafting Trainer’);
SET @cr_guid=(SELECT guid FROM creature WHERE id=@ct_entry);
– creature_template
DELETE FROM creature_template WHERE entry=@ct_entry;
– creature equipment
DELETE FROM creature_equip_template WHERE entry=@ct_entry;
– creature (spawns)
DELETE FROM creature WHERE guid=@cr_guid;
– npc_trainer
DELETE FROM npc_trainer WHERE entry=@ct_entry;
– ### Jewelcrafting Supplies
SET @ct_entry=(SELECT entry FROM creature_template WHERE name=‘Rahl’ AND subname=‘Jewelcrafting Supplies’);
SET @cr_guid=(SELECT guid FROM creature WHERE id=@ct_entry);
– creature_template
DELETE FROM creature_template WHERE entry=@ct_entry;
– creature equipment
DELETE FROM creature_equip_template WHERE entry=@ct_entry;
– creature (spawns)
DELETE FROM creature WHERE guid=@cr_guid;
– npc_vendor
DELETE FROM npc_vendor WHERE entry=@ct_entry;
Old versions
These versions are here for archive. If you’re running an older core you’ll want
to use these because DB fields may be different.
Archive_2
[ul][li]Use this if your creature_template table has faction_A, faction_H and WDBVerified[/li]and does not have equipment_id
[/ul]
– ### Adds a Jewelcrafting trainer and vendor to Stormwind
– NOTE: This is for OLD databases that use the faction_A and faction_H fields
SET @max_ct_entry=(SELECT MAX(entry) FROM creature_template);
SET @max_cr_guid=(SELECT MAX(guid) FROM creature);
– ### Jewelcrafting Trainer
– creature_template
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, 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, Health_mod, Mana_mod, Armor_mod, RacialLeader, questItem1, questItem2, questItem3, questItem4, questItem5, questItem6, movementId, RegenHealth, mechanic_immune_mask, flags_extra, ScriptName, WDBVerified)
values(@max_ct_entry+1,‘0’,‘0’,‘0’,‘0’,‘0’,‘19195’,‘0’,‘0’,‘0’,‘Astawi’,‘Jewelcrafting Trainer’,NULL,‘8382’,‘30’,‘30’,‘0’,‘1638’,‘1638’,‘81’,‘1’,‘1.14286’,‘1’,‘0’,‘39’,‘52’,‘0’,‘96’,‘1’,‘2000’,‘0’,‘2’,‘512’,‘8’,‘0’,‘2’,‘0’,‘0’,‘0’,‘27’,‘40’,‘8’,‘7’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘’,‘1’,‘3’,‘1’,‘1’,‘1’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘1’,‘0’,‘2’,‘’,‘10001’);
– creature_equip_template
– creature_template.entry, outfit ID, right hand, left hand, ranged
– Note: outfit id must start at 1 and can be incremented to allow for random outfits
insert into creature_equip_template (entry, id, itemEntry1, itemEntry2, itemEntry3)
values(@max_ct_entry+1,‘1’,‘0’,‘0’,‘0’);
– creature (spawns)
insert into creature (guid, id, map, spawnMask, phaseMask, modelid, equipment_id, position_x, position_y, position_z, orientation, spawntimesecs, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags)
values(@max_cr_guid+1,@max_ct_entry+1,‘0’,‘1’,‘1’,‘0’,‘0’,‘-8653.34’,‘670.949’,‘101.759’,‘3.80677’,‘300’,‘0’,‘0’,‘811’,‘852’,‘0’,‘0’,‘0’,‘0’);
– npc_trainer (copy from the trainer in Exodar: creature_template.entry = 19778)
INSERT INTO npc_trainer (entry,spell,spellcost,reqskill,reqskillvalue,reqlevel)
SELECT (@max_ct_entry+1),spell,spellcost,reqskill,reqskillvalue,reqlevel
FROM npc_trainer
WHERE entry=19778;
– ### Jewelcrafting Supplies
– creature_template
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, 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, Health_mod, Mana_mod, Armor_mod, RacialLeader, questItem1, questItem2, questItem3, questItem4, questItem5, questItem6, movementId, RegenHealth, mechanic_immune_mask, flags_extra, ScriptName, WDBVerified)
values(@max_ct_entry+2,‘0’,‘0’,‘0’,‘0’,‘0’,‘17222’,‘0’,‘0’,‘0’,‘Rahl’,‘Jewelcrafting Supplies’,NULL,‘0’,‘50’,‘50’,‘0’,‘1638’,‘1638’,‘128’,‘1’,‘1.14286’,‘1’,‘0’,‘79’,‘104’,‘0’,‘194’,‘1’,‘1500’,‘0’,‘2’,‘512’,‘8’,‘0’,‘0’,‘0’,‘0’,‘0’,‘54’,‘79’,‘18’,‘7’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘’,‘1’,‘3’,‘1’,‘1’,‘1’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘1’,‘0’,‘2’,‘’,‘10001’);
– creature_equip_template
– creature_template.entry, outfit ID, right hand, left hand, ranged
– Note: outfit id must start at 1 and can be incremented to allow for random outfits
insert into creature_equip_template (entry, id, itemEntry1, itemEntry2, itemEntry3)
values(@max_ct_entry+2,‘1’,‘0’,‘0’,‘0’);
– creature (spawns)
insert into creature (guid, id, map, spawnMask, phaseMask, modelid, equipment_id, position_x, position_y, position_z, orientation, spawntimesecs, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags)
values(@max_cr_guid+2,@max_ct_entry+2,‘0’,‘1’,‘1’,‘0’,‘0’,‘-8653.12’,‘666.96’,‘101.76’,‘2.97953’,‘300’,‘0’,‘0’,‘1772’,‘1807’,‘0’,‘0’,‘0’,‘0’);
– npc_vendor (copy the items from the vendor in The Exodar: creature_template.entry = 17512)
INSERT INTO npc_vendor (entry,slot,item,maxcount,incrtime,ExtendedCost)
SELECT (@max_ct_entry+2), slot, item,maxcount,incrtime,ExtendedCost
FROM npc_vendor
WHERE entry=17512;
Archive_1
[ul][li]Use this if your creature_template table has the field equipment_id[/li][/ul]
– ### Adds a Jewelcrafting trainer and vendor to Stormwind
SET @max_ct_entry=(SELECT MAX(entry) FROM creature_template);
SET @max_cr_guid=(SELECT MAX(guid) FROM creature);
– ### Jewelcrafting Trainer
– creature_template
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, 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, Health_mod, Mana_mod, Armor_mod, RacialLeader, questItem1, questItem2, questItem3, questItem4, questItem5, questItem6, movementId, RegenHealth, equipment_id, mechanic_immune_mask, flags_extra, ScriptName, WDBVerified)
values(@max_ct_entry+1,‘0’,‘0’,‘0’,‘0’,‘0’,‘19195’,‘0’,‘0’,‘0’,‘Astawi’,‘Jewelcrafting Trainer’,NULL,‘8382’,‘30’,‘30’,‘0’,‘1638’,‘1638’,‘81’,‘1’,‘1.14286’,‘1’,‘0’,‘39’,‘52’,‘0’,‘96’,‘1’,‘2000’,‘0’,‘2’,‘512’,‘8’,‘0’,‘2’,‘0’,‘0’,‘0’,‘27’,‘40’,‘8’,‘7’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘’,‘1’,‘3’,‘1’,‘1’,‘1’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘1’,‘0’,‘0’,‘2’,‘’,‘10001’);
– creature (spawns)
insert into creature (guid, id, map, spawnMask, phaseMask, modelid, equipment_id, position_x, position_y, position_z, orientation, spawntimesecs, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags)
values(@max_cr_guid+1,@max_ct_entry+1,‘0’,‘1’,‘1’,‘0’,‘0’,‘-8653.34’,‘670.949’,‘101.759’,‘3.80677’,‘300’,‘0’,‘0’,‘811’,‘852’,‘0’,‘0’,‘0’,‘0’);
– npc_trainer (copy from the trainer in Exodar: creature_template.entry = 19778)
INSERT INTO npc_trainer (entry,spell,spellcost,reqskill,reqskillvalue,reqlevel)
SELECT (@max_ct_entry+1),spell,spellcost,reqskill,reqskillvalue,reqlevel
FROM npc_trainer
WHERE entry=19778;
– ### Jewelcrafting Supplies
– creature_template
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, 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, Health_mod, Mana_mod, Armor_mod, RacialLeader, questItem1, questItem2, questItem3, questItem4, questItem5, questItem6, movementId, RegenHealth, equipment_id, mechanic_immune_mask, flags_extra, ScriptName, WDBVerified)
values(@max_ct_entry+2,‘0’,‘0’,‘0’,‘0’,‘0’,‘17222’,‘0’,‘0’,‘0’,‘Rahl’,‘Jewelcrafting Supplies’,NULL,‘0’,‘50’,‘50’,‘0’,‘1638’,‘1638’,‘128’,‘1’,‘1.14286’,‘1’,‘0’,‘79’,‘104’,‘0’,‘194’,‘1’,‘1500’,‘0’,‘2’,‘512’,‘8’,‘0’,‘0’,‘0’,‘0’,‘0’,‘54’,‘79’,‘18’,‘7’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘’,‘1’,‘3’,‘1’,‘1’,‘1’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘0’,‘1’,‘0’,‘0’,‘2’,‘’,‘10001’);
– creature (spawns)
insert into creature (guid, id, map, spawnMask, phaseMask, modelid, equipment_id, position_x, position_y, position_z, orientation, spawntimesecs, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags)
values(@max_cr_guid+2,@max_ct_entry+2,‘0’,‘1’,‘1’,‘0’,‘0’,‘-8653.12’,‘666.96’,‘101.76’,‘2.97953’,‘300’,‘0’,‘0’,‘1772’,‘1807’,‘0’,‘0’,‘0’,‘0’);
– npc_vendor (copy the items from the vendor in The Exodar: creature_template.entry = 17512)
INSERT INTO npc_vendor (entry,slot,item,maxcount,incrtime,ExtendedCost)
SELECT (@max_ct_entry+2), slot, item,maxcount,incrtime,ExtendedCost
FROM npc_vendor
WHERE entry=17512;
Edit: Updated SQL, added archives
Edit: Fixed typo in deletion of npc_vendor