[SQL] Custom Spell to Trainers

[CODE]-- Mage

DELETE FROM npc_trainer WHERE (spell=28272);

INSERT INTO npc_trainer SELECT entry, ‘28272’, ‘3000’, ‘0’, ‘0’, ‘30’ FROM creature_template WHERE subname=‘Mage Trainer’; – Polymorph Pig

DELETE FROM npc_trainer WHERE (spell=10140);

INSERT INTO npc_trainer SELECT entry, ‘10140’, ‘42000’, ‘0’, ‘0’, ‘60’ FROM creature_template WHERE subname=‘Mage Trainer’; – Conjure Water

DELETE FROM npc_trainer WHERE (spell=53140);

INSERT INTO npc_trainer SELECT entry, ‘53140’, ‘100000’, ‘0’, ‘0’, ‘71’ FROM creature_template WHERE subname=‘Mage Trainer’; – Teleport: Dalaran

– Warrior

DELETE FROM npc_trainer WHERE (spell=20252);

DELETE FROM npc_trainer WHERE (spell=71);

DELETE FROM npc_trainer WHERE (spell=2458);

DELETE FROM npc_trainer WHERE (spell=7386);

DELETE FROM npc_trainer WHERE (spell=355);

INSERT INTO npc_trainer SELECT entry, ‘20252’, ‘12000’, ‘0’, ‘0’, ‘30’ FROM creature_template WHERE subname=‘Warrior Trainer’; – Intercept

INSERT INTO npc_trainer SELECT entry, ‘71’, ‘1000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Warrior Trainer’; – Defensive Stance

INSERT INTO npc_trainer SELECT entry, ‘2458’, ‘3000’, ‘0’, ‘0’, ‘30’ FROM creature_template WHERE subname=‘Warrior Trainer’; – Berserker Stance

INSERT INTO npc_trainer SELECT entry, ‘7386’, ‘3000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Warrior Trainer’; – Sunder Armor

INSERT INTO npc_trainer SELECT entry, ‘355’, ‘3000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Warrior Trainer’; – Taunt

– Druid

DELETE FROM npc_trainer WHERE (spell=5487);

DELETE FROM npc_trainer WHERE (spell=1066);

DELETE FROM npc_trainer WHERE (spell=40120);

DELETE FROM npc_trainer WHERE (spell=6807);

DELETE FROM npc_trainer WHERE (spell=6795);

DELETE FROM npc_trainer WHERE (spell=8946);

INSERT INTO npc_trainer SELECT entry, ‘5487’, ‘1000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Druid Trainer’; – Bear Form

INSERT INTO npc_trainer SELECT entry, ‘6807’, ‘1000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Druid Trainer’; – Maul

INSERT INTO npc_trainer SELECT entry, ‘6795’, ‘1000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Druid Trainer’; – Growl

INSERT INTO npc_trainer SELECT entry, ‘1066’, ‘900’, ‘0’, ‘0’, ‘16’ FROM creature_template WHERE subname=‘Druid Trainer’; – Aquatic Form

INSERT INTO npc_trainer SELECT entry, ‘40120’, ‘200000’, ‘0’, ‘0’, ‘70’ FROM creature_template WHERE subname=‘Druid Trainer’; – Swift Flight Form

INSERT INTO npc_trainer SELECT entry, ‘8946’, ‘2000’, ‘0’, ‘0’, ‘14’ FROM creature_template WHERE subname=‘Druid Trainer’; – Cure Poison

– Paladin

DELETE FROM npc_trainer WHERE (spell=7328);

DELETE FROM npc_trainer WHERE (spell=5502);

INSERT INTO npc_trainer SELECT entry, ‘7328’, ‘1200’, ‘0’, ‘0’, ‘12’ FROM creature_template WHERE subname=‘Paladin Trainer’; – Redemption

INSERT INTO npc_trainer SELECT entry, ‘5502’, ‘1200’, ‘0’, ‘0’, ‘12’ FROM creature_template WHERE subname=‘Paladin Trainer’; – Sense Undead

– Hunter

DELETE FROM npc_trainer WHERE (spell=883);

DELETE FROM npc_trainer WHERE (spell=6991);

DELETE FROM npc_trainer WHERE (spell=2641);

DELETE FROM npc_trainer WHERE (spell=982);

DELETE FROM npc_trainer WHERE (spell=1515);

INSERT INTO npc_trainer SELECT entry, ‘883’, ‘2000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Hunter Trainer’; – Call Pet

INSERT INTO npc_trainer SELECT entry, ‘6991’, ‘2000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Hunter Trainer’; – Feed Pet

INSERT INTO npc_trainer SELECT entry, ‘2641’, ‘2000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Hunter Trainer’; – Dismiss Pet

INSERT INTO npc_trainer SELECT entry, ‘982’, ‘2000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Hunter Trainer’; – Revive Pet

INSERT INTO npc_trainer SELECT entry, ‘1515’, ‘2000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Hunter Trainer’; – Tame Beast

REPLACE INTO npc_trainer SELECT entry, ‘5149’, ‘20000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Hunter Trainer’; – Beast Training

– Warlock

DELETE FROM npc_trainer WHERE (spell=688);

DELETE FROM npc_trainer WHERE (spell=697);

DELETE FROM npc_trainer WHERE (spell=712);

DELETE FROM npc_trainer WHERE (spell=691);

DELETE FROM npc_trainer WHERE (spell=1122);

DELETE FROM npc_trainer WHERE (spell=18540);

DELETE FROM npc_trainer WHERE (spell=54785);

DELETE FROM npc_trainer WHERE (spell=59671);

DELETE FROM npc_trainer WHERE (spell=50589);

DELETE FROM npc_trainer WHERE (spell=5784);

DELETE FROM npc_trainer WHERE (spell=23161);

INSERT INTO npc_trainer SELECT entry, ‘688’, ‘100’, ‘0’, ‘0’, ‘1’ FROM creature_template WHERE subname=‘Warlock Trainer’; – Summon Imp

INSERT INTO npc_trainer SELECT entry, ‘697’, ‘1000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Warlock Trainer’; – Summon Voidwalker

INSERT INTO npc_trainer SELECT entry, ‘712’, ‘2000’, ‘0’, ‘0’, ‘20’ FROM creature_template WHERE subname=‘Warlock Trainer’; – Summon Succubus

INSERT INTO npc_trainer SELECT entry, ‘691’, ‘3000’, ‘0’, ‘0’, ‘30’ FROM creature_template WHERE subname=‘Warlock Trainer’; – Summon Felhunter

INSERT INTO npc_trainer SELECT entry, ‘1122’, ‘5000’, ‘0’, ‘0’, ‘50’ FROM creature_template WHERE subname=‘Warlock Trainer’; – Summon Inferno

INSERT INTO npc_trainer SELECT entry, ‘18540’, ‘5000’, ‘0’, ‘0’, ‘60’ FROM creature_template WHERE subname=‘Warlock Trainer’; – Ritual of Doom

INSERT INTO npc_trainer SELECT entry, ‘54785’, ‘5000’, ‘0’, ‘0’, ‘60’ FROM creature_template WHERE subname=‘Warlock Trainer’; – Demon Charge

INSERT INTO npc_trainer SELECT entry, ‘59671’, ‘5000’, ‘0’, ‘0’, ‘1’ FROM creature_template WHERE subname=‘Warlock Trainer’; – Challenging Howl

INSERT INTO npc_trainer SELECT entry, ‘50589’, ‘5000’, ‘0’, ‘0’, ‘60’ FROM creature_template WHERE subname=‘Warlock Trainer’; – Immolation Aura

INSERT INTO npc_trainer SELECT entry, ‘5784’, ‘5000’, ‘0’, ‘0’, ‘20’ FROM creature_template WHERE subname=‘Warlock Trainer’; – Felsteed

INSERT INTO npc_trainer SELECT entry, ‘23161’, ‘10000’, ‘0’, ‘0’, ‘40’ FROM creature_template WHERE subname=‘Warlock Trainer’; – Dreadsteed

– Shaman

DELETE FROM npc_trainer WHERE (spell=8071);

DELETE FROM npc_trainer WHERE (spell=3599);

DELETE FROM npc_trainer WHERE (spell=5394);

INSERT INTO npc_trainer SELECT entry, ‘8071’, ‘100’, ‘0’, ‘0’, ‘4’ FROM creature_template WHERE subname=‘Shaman Trainer’; – Stoneskin Totem

INSERT INTO npc_trainer SELECT entry, ‘3599’, ‘1000’, ‘0’, ‘0’, ‘10’ FROM creature_template WHERE subname=‘Shaman Trainer’; – Searing Totem

INSERT INTO npc_trainer SELECT entry, ‘5394’, ‘2000’, ‘0’, ‘0’, ‘20’ FROM creature_template WHERE subname=‘Shaman Trainer’; – Healing Stream Totem

– Priest

DELETE FROM npc_trainer WHERE (spell=2944);

DELETE FROM npc_trainer WHERE (spell=6346);

INSERT INTO npc_trainer SELECT entry, ‘2944’, ‘3000’, ‘0’, ‘0’, ‘20’ FROM creature_template WHERE subname=‘Priest Trainer’; – Devouring Plague

INSERT INTO npc_trainer SELECT entry, ‘6346’, ‘800’, ‘0’, ‘0’, ‘20’ FROM creature_template WHERE subname=‘Priest Trainer’; – Fear Ward

– Death Knight

DELETE FROM npc_trainer WHERE (spell=48778);

DELETE FROM npc_trainer WHERE (spell=50977);

DELETE FROM npc_trainer WHERE (spell=53428);

INSERT INTO npc_trainer SELECT entry, ‘48778’, ‘10000’, ‘0’, ‘0’, ‘55’ FROM creature_template WHERE entry in (28474, 29194, 31084, 28471, 29195, 28472, 29196) ; – Acherus Deathcharger

INSERT INTO npc_trainer SELECT entry, ‘50977’, ‘1000’, ‘0’, ‘0’, ‘55’ FROM creature_template WHERE entry in (28474, 29194, 31084, 28471, 29195, 28472, 29196); – Death Gate

INSERT INTO npc_trainer SELECT entry, ‘53428’, ‘3000’, ‘0’, ‘0’, ‘55’ FROM creature_template WHERE entry in (28474, 29194, 31084, 28471, 29195, 28472, 29196); – Runeforging

– Totemy

REPLACE INTO playercreateinfo_item (race, class, itemid, amount) VALUES

(11, 7, 5178, 1), (11, 7, 5175, 1), (11, 7, 5176, 1), (11, 7, 5177, 1),

(2, 7, 5175, 1), (2, 7, 5176, 1), (2, 7, 5177, 1), (2, 7, 5178, 1),

(6, 7, 5175, 1), (6, 7, 5176, 1), (6, 7, 5177, 1), (6, 7, 5178, 1),

(8, 7, 5175, 1), (8, 7, 5176, 1), (8, 7, 5177, 1), (8, 7, 5178, 1); [/CODE]

Thank you! You made me remember how to make better sql in a much faster way. Forgot that you could make the sql so much easier /emoticons/default_smile.png

BTW are you using any program when making sql or are you writing everything by hand?

Either way he still has to manually entered the entry id and spell ids for each spell…

Gary quest spells this class?

You should give more information on exactly what this does to what trainers for the people that have no idea what they’re doing (which is 95% of the population) /emoticons/default_smile.png

While I do know what this does, and its very awesome that you posted it, it seems to have zero effect when I add it. No errors. No difference to trainers in game (after a restart). Am I missing something?

its very nice , and thank you for it .

but i think you use a hard sql codes !!1

for the warrior for example , in the delete section you can use this

DELETE FROM `npc_trainer` WHERE `spell` IN (20252, 71, 2458, 7386, 355)[/CODE]

instead of 5 row .

anyway , thanks for this useful script .

good luck .

updated query

100% working


-- Mage

DELETE FROM npc_trainer WHERE `spell` IN (28272,10140,53140);

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('28272', '3000', '0', '0', '30'); -- Polymorph Pig

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('10140', '42000', '0', '0', '60'); -- Conjure Water

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('53140', '100000', '0', '0', '71'); -- Teleport: Dalaran


-- Warrior

DELETE FROM npc_trainer WHERE `spell` IN (20252,71,2458,7386,355);

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('20252', '12000', '0', '0', '30'); -- Intercept

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('71', '1000', '0', '0', '10'); -- Defensive Stance

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('2458', '3000', '0', '0', '30'); -- Berserker Stance

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('7386', '3000', '0', '0', '10') ; -- Sunder Armor

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('355', '3000', '0', '0', '10'); -- Taunt

-- Druid

DELETE FROM npc_trainer WHERE `spell` IN (5487,1066,40120,6807,6795,8946);

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('5487', '1000', '0', '0', '10'); -- Bear Form

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('6807', '1000', '0', '0', '10'); -- Maul

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('6795', '1000', '0', '0', '10'); -- Growl

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('1066', '900', '0', '0', '16'); -- Aquatic Form

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('40120', '200000', '0', '0', '70'); -- Swift Flight Form

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('8946', '2000', '0', '0', '14'); -- Cure Poison

-- Paladin

DELETE FROM npc_trainer WHERE `spell` IN (7328,5502);

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('7328', '1200', '0', '0', '12'); -- Redemption

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('5502', '1200', '0', '0', '12'); -- Sense Undead

-- Hunter

DELETE FROM npc_trainer WHERE `spell` IN (883,6991,2641,982,1515);

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('883', '2000', '0', '0', '10'); -- Call Pet

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('6991', '2000', '0', '0', '10'); -- Feed Pet

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('2641', '2000', '0', '0', '10'); -- Dismiss Pet

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('982', '2000', '0', '0', '10'); -- Revive Pet

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('1515', '2000', '0', '0', '10'); -- Tame Beast

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('5149', '20000', '0', '0', '10'); -- Beast Training

-- Warlock

DELETE FROM npc_trainer WHERE `spell` IN (688,697,712,691,1122,18540,54785,59671,50589,5784,23161);

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('688', '100', '0', '0', '1'); -- Summon Imp

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('697', '1000', '0', '0', '10'); -- Summon Voidwalker

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('712', '2000', '0', '0', '20'); -- Summon Succubus

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('691', '3000', '0', '0', '30'); -- Summon Felhunter

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('1122', '5000', '0', '0', '50'); -- Summon Inferno

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('18540', '5000', '0', '0', '60'); -- Ritual of Doom

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('54785', '5000', '0', '0', '60'); -- Demon Charge

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('59671', '5000', '0', '0', '1'); -- Challenging Howl

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('50589', '5000', '0', '0', '60'); -- Immolation Aura

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('5784', '5000', '0', '0', '20'); -- Felsteed

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('23161', '10000', '0', '0', '40'); -- Dreadsteed

-- Shaman

DELETE FROM npc_trainer WHERE `spell` IN (8071,3599,5394);

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('8071', '100', '0', '0', '4'); -- Stoneskin Totem

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('3599', '1000', '0', '0', '10'); -- Searing Totem

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('5394', '2000', '0', '0', '20'); -- Healing Stream Totem

-- Priest

DELETE FROM npc_trainer WHERE `spell` IN (2944,6346);

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('2944', '3000', '0', '0', '20'); -- Devouring Plague

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('6346', '800', '0', '0', '20'); -- Fear Ward

-- Death Knight

DELETE FROM npc_trainer WHERE `spell` IN (48778,50977,53428);

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('48778', '10000', '0', '0', '55'); -- Acherus Deathcharger

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('50977', '1000', '0', '0', '55'); -- Death Gate

REPLACE INTO `npc_trainer` (`entry`, `spell`, `spellcost`, `reqskill`, `reqlevel`) VALUES ('53428', '3000', '0', '0', '55'); -- Runeforging

-- Totemy

REPLACE INTO playercreateinfo_item (`race`, `class`, `itemid`, `amount`) VALUES

(11, 7, 5178, 1), (11, 7, 5175, 1), (11, 7, 5176, 1), (11, 7, 5177, 1),

(2, 7, 5175, 1), (2, 7, 5176, 1), (2, 7, 5177, 1), (2, 7, 5178, 1),

(6, 7, 5175, 1), (6, 7, 5176, 1), (6, 7, 5177, 1), (6, 7, 5178, 1),

(8, 7, 5175, 1), (8, 7, 5176, 1), (8, 7, 5177, 1), (8, 7, 5178, 1);

and here is the trainers entry

[CODE]Warrior-> 26332

Paladin-> 26327

Hunter-> 26325

Rogue-> 26329

Priest-> 26328

Deathknight-> 29196

Shaman-> 26330

Mage-> 26326

Warlock-> 26331

Druid-> 26324[/CODE]

Good luck .