DB CODE SNIPPITS

[CODE]-- Condition code for item target “First 4 comment lines can be removed after filling in values”
– XXXXX = item entry
– YYYYY = creature entry
– ZZ = 1 (creature alive) 2 (creature dead)

– Add condition for Item XXXXX “item name” to target YYYYY “NPC name”
DELETE FROM conditions WHERE SourceTypeOrReferenceId=18 AND SourceEntry=XXXXX;
INSERT INTO conditions (SourceTypeOrReferenceId,SourceGroup,SourceEntry,ElseGroup,ConditionTypeOrReference,ConditionValue1,ConditionValue2,ConditionValue3,ErrorTextId,ScriptName,Comment) VALUES
(18,0,XXXXX,0,24,ZZ,YYYYY,0,63,‘’,‘Item XXXXX “item name” targets YYYYY “NPC name”’);[/sql]
[/CODE]

[CODE]-- Pathing for located
SET @NPC :=xxx;
SET @PATH :=@NPC*10;
UPDATE creature SET spawndist=0,MovementType=2 WHERE guid=@NPC;

DELETE FROM creature_addon WHERE guid=@NPC;
INSERT INTO creature_addon (guid,path_id) VALUES (@NPC,@PATH);

DELETE FROM waypoint_data WHERE id=@PATH;
INSERT INTO waypoint_data (id,point,position_x,position_y,position_z,delay,move_flag,action,action_chance,wpguid) VALUES[/sql]
[/CODE]

[CODE]DELETE FROM smart_scripts WHERE entryorguid=X AND source_type=X;
INSERT INTO smart_scripts (entryorguid,source_type,id,link,event_type,event_phase_mask,event_chance,event_flags,event_param1,event_param2,event_param3,event_param4,action_type,action_param1,action_param2,action_param3,action_param4,action_param5,action_param6,target_type,target_param1,target_param2,target_param3,target_x,target_y,target_z,target_o,comment) VALUES
[/sql]

[sql]DELETE FROM conditions WHERE SourceTypeOrReferenceId=X AND SourceGroup=X AND SourceEntry=X;
INSERT INTO conditions (SourceTypeOrReferenceId,SourceGroup,SourceEntry,ElseGroup,ConditionTypeOrReference,ConditionValue1,ConditionValue2,ConditionValue3,ErrorTextId,Comment) VALUES
[/CODE]

[CODE]DELETE FROM creature_text WHERE entry=X;
INSERT INTO creature_text (entry,groupid,id,text,type,language,probability,emote,duration,sound,comment) VALUES[/sql]

[sql]DELETE FROM gossip_menu_option WHERE menu_id=X AND id=X;
INSERT INTO gossip_menu_option (menu_id,id,option_icon,option_text,option_id,npc_option_npcflag,action_menu_id,action_poi_id,box_coded,box_money,box_text) VALUES
[/CODE]

[CODE]DELETE FROM gossip_menu WHERE entry=X;
INSERT INTO gossip_menu (entry,text_id) VALUES[/sql]

[sql]DELETE FROM creature_equip_template WHERE entry=X;
INSERT INTO creature_equip_template (entry,equipentry1,equipentry2,equipentry3) VALUES
[/CODE]

[CODE]DELETE FROM creature_template_addon WHERE entry=X;
INSERT INTO creature_template_addon (entry,mount,bytes1,bytes2,emote,auras) VALUES[/sql]

[sql]DELETE FROM creature_addon WHERE guid=X;
INSERT INTO creature_addon (guid,emote,bytes2,auras) VALUES
[/CODE]

[CODE]DELETE FROM creature WHERE id=X;
INSERT INTO creature (guid,id,map,spawnMask,phaseMask,modelid,equipment_id,position_x,position_y,position_z,orientation,spawntimesecs,spawndist,currentwaypoint,curhealth,curmana,MovementType) VALUES
[/sql]

[sql]DELETE FROM gameobject WHERE id=X;
INSERT INTO gameobject (guid,id,map,spawnMask,phaseMask,position_x,position_y,position_z,orientation,rotation0,rotation1,rotation2,rotation3,spawntimesecs,animprogress,state) VALUES
[/CODE]

[CODE]DELETE FROM creature_involvedrelation WHERE id=X;
INSERT INTO creature_involvedrelation (id,quest) VALUES[/sql]

[sql]DELETE FROM creature_questrelation WHERE id=X;
INSERT INTO creature_questrelation (id,quest) VALUES
[/CODE]

Gameobject table:

SELECT  `guid` FROM (SELECT  1 AS `guid`) q1 WHERE NOT EXISTS (SELECT  1 FROM `gameobject` WHERE `guid` = 1) UNION ALL SELECT * FROM (SELECT `guid` + 1 FROM `gameobject` t WHERE NOT EXISTS (SELECT  1 FROM `gameobject` ti WHERE ti.`guid` = t.`guid` + 1) ORDER BY `guid` LIMIT 1) q2 ORDER BY `guid` LIMIT 1

Creature table:

SELECT  `guid` FROM (SELECT  1 AS `guid`) q1 WHERE NOT EXISTS (SELECT  1 FROM `creature` WHERE `guid` = 1) UNION ALL SELECT * FROM (SELECT `guid` + 1 FROM `creature` t WHERE NOT EXISTS (SELECT  1 FROM `creature` ti WHERE ti.`guid` = t.`guid` + 1) ORDER BY `guid` LIMIT 1) q2 ORDER BY `guid` LIMIT 1

Returns the first unused GUID

-- Set creature to not award xp on death SET @ENTRY := XXXXX; UPDATE `creature_template` SET `flags_extra`=`flags_extra`|64 WHERE `entry`=@ENTRY;[/sql]

-- First free ENTRY from creature_template SELECT `entry` FROM (SELECT 1 AS `entry`) q1 WHERE NOT EXISTS (SELECT 1 FROM `creature_template` WHERE `entry` = 1) UNION ALL SELECT * FROM (SELECT `entry` + 1 FROM `creature_template` t WHERE NOT EXISTS (SELECT 1 FROM `creature_template` ti WHERE ti.`entry` = t.`entry` + 1) ORDER BY `entry` LIMIT 1) q2 ORDER BY `entry` LIMIT 1 [/SQL]

P.S. Great idea.

-- Quest With Npc Spell Click Loot
SET @ENTRY= xxx; -- Creature ID
SET @SPELL= xxx; -- SPELL ID
SET @FLAG= xxx; -- Cast_Flags ID (0 on self 1 playercast)
SET @NPCFLAG= 16777216; -- NpcFlag 16777216(spellclick)
SET @QUEST= xxx; -- QuestID ACTIVE
SET @SMARTID= xxx; -- Set THE ID of the SmartScript
SET @COMMAND= xxx; -- Use 37 for DIE or 41 for despawn
SET @DESPAWNTIME= xxx; -- Use when DespawnCommand is Used or use 0 when its kill
SET @CONDITIONTYPE= xxx; -- Define type of conditions 8 for quest complete 9 for quest active 28 for questComplete

-- Set Flag For NpcSpellClick
UPDATE creature_template set npcflag=@NPCFLAG where entry=@ENTRY;

-- Insert The SpellClickFlag
DELETE FROM npc_spellclick_spells where npc_entry=@ENTRY;
INSERT INTO npc_spellclick_spells (npc_entry, spell_id, cast_flags, user_type) VALUES 
(@ENTRY, @SPELL, @FLAG, 0);


-- Condition to Click On Creature with a quest need to be active
DELETE FROM conditions where SourceGroup=@ENTRY and SourceEntry=@SPELL and ConditionValue1=@QUEST;
INSERT INTO conditions(SourceTypeOrReferenceId, SourceGroup, SourceEntry, SourceId, ElseGroup, ConditionTypeOrReference, ConditionTarget, ConditionValue1, ConditionValue2, ConditionValue3, NegativeCondition, ErrorType, ErrorTextId, ScriptName, Comment) VALUES 
(18, @ENTRY, @SPELL, 0, 0, @CONDITIONTYPE, 0, @QUEST, 0, 0, 0, 0, 0, '', 'Required quest active for spellclick');


-- SmartScript on use SpellClick despawn creature or DIE
UPDATE creature_template set ainame='SmartAI' where entry=@ENTRY;
DELETE FROM smart_scripts where entryorguid=@ENTRY and id=@SMARTID;
INSERT INTO smart_scripts(entryorguid, source_type, id, link, event_type, event_phase_mask, event_chance, event_flags, event_param1, event_param2, event_param3, event_param4, action_type, action_param1, action_param2, action_param3, action_param4, action_param5, action_param6, target_type, target_param1, target_param2, target_param3, target_x, target_y, target_z, target_o, comment) VALUES 
(@ENTRY, 0, @SMARTID, 0, 73, 0, 100, 0, 0, 0, 0, 0, @COMMAND, @DESPAWNTIME, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 'force despawn');
-- GAME EVENT CREATURE EASY INSERT

SET @NPC= xxx; -- ID of the creature you want to be Only in a Specific Event
SET @EVENT= xxx; -- Event ID
INSERT INTO game_event_creature (guid)
SELECT  guid
FROM    creature
WHERE   ID = @NPC;

update game_event_creature set eventEntry=@EVENT where guid in (select guid from creature where id=@NPC);


-- GAME EVENT GAMEOBJECT EASY INSERT

SET @GOB= xxx; -- ID of the gameobject you want to be Only in a Specific Event
SET @EVENT= xxx; -- Event ID
INSERT INTO game_event_gameobject (guid)
SELECT  guid
FROM    gameobject
WHERE   ID = @GOB;

update game_event_gameobject set eventEntry=@EVENT where guid in (select guid from gameobject where id=@GOB);
-- Vehicle Template Accessory

SET @ENTRY= xxx; -- ID of the VEHICLE
SET @NPC= xxx; -- ID of the NPC
SET @NPCFLAG= 16777216; -- NpcFlag 16777216(spellclick)
SET @SPELL= 46598; -- Hardcoded Spell 46598
SET @FLAG= 0; -- Cast_Flags ID (0 on self 1 playercast)

UPDATE creature_template set npcflag=@NPCFLAG where entry=@ENTRY;
delete from vehicle_template_accessory where entry=@Entry;
INSERT INTO vehicle_template_accessory(entry, accessory_entry, seat_id, minion, description, summontype, summontimer) VALUES 
(@ENTRY, @NPC, 0, 0, '', 6, 30000);


-- Insert The SpellClickFlag
DELETE FROM npc_spellclick_spells where npc_entry=@ENTRY;
INSERT INTO npc_spellclick_spells (npc_entry, spell_id, cast_flags, user_type) VALUES 
(@ENTRY, @SPELL, @FLAG, 0);
-- Condition Gossip_Menu , Gossip_menu_option

-- Gossip Menu
SET @SourceGroup = xxx; -- menu entry
SET @SourceEntry = xxx; -- gossip menu text id
SET @ConditionTypeOrReference = xxx; -- Condition TYPE 15 FOR CLASS REQUIREMENT 8 for quest need to be completed 9 for quest active 28 quest rewarded
SET @ConditionValue1= 0; -- Value1
SET @ConditionValue2= 0; -- Value2
SET @ConditionValue3= 0; -- Value3
SET @NegativeCondition= 0; -- Set 1 if not REQUIRED ConditionTypeOrReference

DELETE FROM conditions where SourceGroup=@SourceGroup and SourceEntry=@SourceEntry;
INSERT INTO conditions(SourceTypeOrReferenceId, SourceGroup, SourceEntry, SourceId, ElseGroup, ConditionTypeOrReference, ConditionTarget, ConditionValue1, ConditionValue2, ConditionValue3, NegativeCondition, ErrorType, ErrorTextId, ScriptName, Comment) VALUES 
(14, @SourceGroup, @SourceEntry, 0, 0, @ConditionTypeOrReference, 0, @ConditionValue1, @ConditionValue2, @ConditionValue3, @NegativeCondition, 0, 0, '', '');


-- Gossip Menu Option
SET @SourceGroup = xxx; -- (gossip_menu_option.menu_id) 
SET @SourceEntry = xxx; -- (gossip_menu_option.id) 
SET @ConditionTypeOrReference = xxx; -- Condition TYPE 15 FOR CLASS REQUIREMENT 8 for quest need to be completed 9 for quest active 28 quest rewarded
SET @ConditionValue1= 0; -- Value1
SET @ConditionValue2= 0; -- Value2
SET @ConditionValue3= 0; -- Value3
SET @NegativeCondition= 0; -- Set 1 if not REQUIRED ConditionTypeOrReference

DELETE FROM conditions where SourceGroup=@SourceGroup and SourceEntry=@SourceEntry;
INSERT INTO conditions(SourceTypeOrReferenceId, SourceGroup, SourceEntry, SourceId, ElseGroup, ConditionTypeOrReference, ConditionTarget, ConditionValue1, ConditionValue2, ConditionValue3, NegativeCondition, ErrorType, ErrorTextId, ScriptName, Comment) VALUES 
(15, @SourceGroup, @SourceEntry, 0, 0, @ConditionTypeOrReference, 0, @ConditionValue1, @ConditionValue2, @ConditionValue3, @NegativeCondition, 0, 0, '', '');
-- Condition spell target
SET @SPELL= xxx; -- SpellID
SET @ENTRY= xxx; -- CreatureID
SET @EFFECTMASK= xxx; -- Effect of the SPELL Use SpellWORKS
SET @ELSEGROUP= 0; -- ELSE GROUPE IF NEED



DELETE FROM conditions where SourceGroup=@EFFECTMASK and SourceEntry=@SPELL and ConditionValue2=@Entry;
INSERT INTO conditions(SourceTypeOrReferenceId, SourceGroup, SourceEntry, SourceId, ElseGroup, ConditionTypeOrReference, ConditionTarget, ConditionValue1, ConditionValue2, ConditionValue3, NegativeCondition, ErrorType, ErrorTextId, ScriptName, Comment) VALUES 
(13, @EFFECTMASK, @SPELL, 0, @ELSEGROUP, 31, 0, 3, @Entry, 0, 0, 0, 0, '', '');
-- Quest that need spell cast on creature That finnaly despawn or die

SET @ENTRY = xxx; -- ID of the creature
SET @SPELL = xxx; -- ID of the SPELL
SET @SMARTID = xxx; -- ID of the SmartScript
SET @COMMAND = xxx; -- Use 37 for DIE or 41 for despawn
SET @DESPAWNTIME= xxx; -- Used when command 41 is active otherwise Put it to 0
SET @CREDIT= xxx; -- Credit that needed for the quest

DELETE FROM conditions where SourceEntry=@SPELL and ConditionValue2=@ENTRY ;
INSERT INTO `conditions` (`SourceTypeOrReferenceId`, `SourceGroup`, `SourceEntry`, `ConditionTypeOrReference`, `ConditionValue1`, `ConditionValue2`) VALUES
(13, 1, @SPELL, 31, 3, @ENTRY);


UPDATE `creature_template` SET `AIName` = 'SmartAI' WHERE `entry` =  @ENTRY;
DELETE FROM `smart_scripts` WHERE (`entryorguid`= @ENTRY AND `source_type`=0);
INSERT INTO `smart_scripts` (`entryorguid`, `source_type`, `id`, `link`, `event_type`, `event_phase_mask`, `event_chance`, `event_flags`, `event_param1`, `event_param2`, `event_param3`, `event_param4`, `action_type`, `action_param1`, `action_param2`, `action_param3`, `action_param4`, `action_param5`, `action_param6`, `target_type`, `target_param1`, `target_param2`, `target_param3`, `target_x`, `target_y`, `target_z`, `target_o`, `comment`) VALUES
(@ENTRY, 0, @SMARTID, @SMARTID+1, 8, 0, 100, 0, @SPELL, 0, 0, 0, 33,  @CREDIT, 0, 0, 0, 0, 0, 7, 0, 0, 0, 0, 0, 0, 0, "Get Credit"),
(@ENTRY, 0, @SMARTID+1, 0, 61, 0, 100, 0, 0, 0, 0, 0, @COMMAND, @DESPAWNTIME, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, "Disapear");
-- Prisoneer Cage Style Quest

-- Gameobject Smart
SET @ENTRY := xxx; -- Gameobject ID Cage
SET @SOURCETYPE := 1;
SET @SOURCETYPE2 := 9;
SET @SOURCETYPE3 := 0;
SET @QUESTCREDIT := xxx; -- Quest Credit
SET @NPC := xxx; -- NPC ID


DELETE FROM `smart_scripts` WHERE `entryorguid`=@ENTRY AND `source_type`=@SOURCETYPE;
UPDATE gameobject_template SET AIName="SmartGameObjectAI", ScriptName='' WHERE entry=@ENTRY LIMIT 1;
INSERT INTO `smart_scripts` (`entryorguid`,`source_type`,`id`,`link`,`event_type`,`event_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`action_type`,`action_param1`,`action_param2`,`action_param3`,`action_param4`,`action_param5`,`action_param6`,`target_type`,`target_param1`,`target_param2`,`target_param3`,`target_x`,`target_y`,`target_z`,`target_o`,`comment`) VALUES 
(@ENTRY,@SOURCETYPE,0,0,64,0,100,1,0,0,0,0,80,@ENTRY*100,0,0,0,0,0,1,0,0,0,0.0,0.0,0.0,0.0,"On gossip Hello");


DELETE FROM `smart_scripts` WHERE `entryorguid`=@ENTRY*100 AND `source_type`=@SOURCETYPE2;
INSERT INTO `smart_scripts` (`entryorguid`,`source_type`,`id`,`link`,`event_type`,`event_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`action_type`,`action_param1`,`action_param2`,`action_param3`,`action_param4`,`action_param5`,`action_param6`,`target_type`,`target_param1`,`target_param2`,`target_param3`,`target_x`,`target_y`,`target_z`,`target_o`,`comment`) VALUES 
(@ENTRY*100,@SOURCETYPE2,0,0,0,0,100,0,1000,1000,0,0,45,0,1,0,0,0,0,11,@NPC,10,0,0.0,0.0,0.0,0.0,"Send data 1 to creature"),
(@ENTRY*100,@SOURCETYPE2,1,0,0,0,100,0,0,0,0,0,33,@QUESTCREDIT,0,0,0,0,0,18,100,0,0,0.0,0.0,0.0,0.0,"give credit to player");


-- Creature Smart
DELETE FROM `smart_scripts` WHERE `entryorguid`=@NPC AND `source_type`=@SOURCETYPE3;
UPDATE creature_template SET AIName="SmartAI",ScriptName='' WHERE entry=@NPC LIMIT 1;
INSERT INTO `smart_scripts` (`entryorguid`,`source_type`,`id`,`link`,`event_type`,`event_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`action_type`,`action_param1`,`action_param2`,`action_param3`,`action_param4`,`action_param5`,`action_param6`,`target_type`,`target_param1`,`target_param2`,`target_param3`,`target_x`,`target_y`,`target_z`,`target_o`,`comment`) VALUES 
(@NPC,@SOURCETYPE3,0,0,38,0,100,0,0,1,0,0,80,@NPC*100,0,0,0,0,0,1,0,0,0,0.0,0.0,0.0,0.0,"On data set do action");


DELETE FROM `smart_scripts` WHERE `entryorguid`=@NPC*100 AND `source_type`=@SOURCETYPE2;
INSERT INTO `smart_scripts` (`entryorguid`,`source_type`,`id`,`link`,`event_type`,`event_phase_mask`,`event_chance`,`event_flags`,`event_param1`,`event_param2`,`event_param3`,`event_param4`,`action_type`,`action_param1`,`action_param2`,`action_param3`,`action_param4`,`action_param5`,`action_param6`,`target_type`,`target_param1`,`target_param2`,`target_param3`,`target_x`,`target_y`,`target_z`,`target_o`,`comment`) VALUES 
(@NPC*100,@SOURCETYPE2,0,0,0,0,100,0,5000,5000,0,0,1,0,0,0,0,0,0,18,10,0,0,0.0,0.0,0.0,0.0,"talk to player"),
(@NPC*100,@SOURCETYPE2,1,0,0,0,100,0,0,0,0,0,46,20,0,0,0,0,0,1,0,0,0,0.0,0.0,0.0,0.0,"Move forward"),
(@NPC*100,@SOURCETYPE2,2,0,0,0,100,0,10000,10000,0,0,37,0,0,0,0,0,0,1,0,0,0,0.0,0.0,0.0,0.0,"Die self");
-- Creature template addon

SET @ENTRY = xxx; -- Id of Creature
SET @PATHID = ''; -- PathID
SET @AURAS=   ''; -- AURAS spellID
SET @MOUNT =  0; -- Mount (DISPLAY entry of a creature)
SET @BYTES1=  0; -- Bytes1 (1=sit, 2=sleep, 3=healthbarempty, 8=kneel , 9=Submerges the creature ,33554432= HoverHeight)
SET @BYTES2=  0; -- Bytes2 (0= unarmed,1=prepared melee weapon 2=prepared ranged weapon)
SET @EMOTE=   0; -- Emote



DELETE FROM `creature_template_addon` WHERE `entry`=@ENTRY;
INSERT INTO `creature_template_addon` (`entry`, `path_id`, `mount`, `bytes1`, `bytes2`, `emote`, `auras`) VALUES 
(@ENTRY, @PATHID , @MOUNT, @BYTES1, @BYTES2, @EMOTE, @AURAS);