SQL Guidelines

HOW TO GIVE YOUR CONTRIBUTION TO THE TRINITY DATABASE PROJECT:
It is most important that suggested fixes in this section do not take an enormous amount of time to check and test, otherwise they are useless if they take up as much time as writing them from scratch by a Dev (hence also a waste of time for you suggesting the fix).

METHOD:
All the fixes should be done in a "New Issue" on our tracker: https://github.com/TrinityCore/TrinityCore/issues
If any aspect of the following method is not followed, your ticket might be deleted.

DO NOT WASTE THE DEVS' TIME AND YOUR TIME:

Use the search function to see if the fix you want to provide hasn't already been posted or already accepted and committed. Use multiple words search if necessary, do not stop at the first attempt just because the search engine does not return any result. Also search if a bug report for the bug you are trying to fix already exist, if it does post there.
Always state the Trinity Core Hash you are using, make sure you are using the latest TDB release and state any addon you are using.
Cross-check a bug you found with a clean world DB. Any custom addition or change you may have done on your DB could cause your bug, you never know.
Always state the source of your fixes (website, offy account, another project, etc) and provide as many links or details as possible. Always pretend that the Dev reading doesn't know the quest/mob/etc you are trying to fix.
Keep the section tidy. Use tags ```sql ``` to precisely define what your are fixing, and use the "Description" field to state whether you are fixing the spawn, the template, etc and make ONE ticket per fix, DO NOT make one ticket with many fixes. (Follow the example bellow.)

MAKE YOUR FIX USEFUL:

Always comment your queries for better understanding with the -- format.
The ticket will not support too many queries. Attach a sql file to your ticket whenever necessary.
Any INSERT INTO query must be preceded by a DELETE query to prevent import errors. Always write the fields in the query to better trace any structure change and adapt your fix to newer core revs.
If you are modifying an existing line, use an UPDATE query. We do not delete and re-insert a full line just to modify a few fields. Be as precise as you can to make sure your query updates ONLY the line expected.
If you are writing a text, make sure you use '' instead of ' when writing an apostrophe or it will give an import error
If you are providing an SQL query fix, please advise if it has been tested or not (be honest). Others might then test it for you and give feedback, this is a Community work! If you have tested your own work.
DO NOT CHANGE ANY WDB FIELD: http://www.trinitycore.org/f/index.php?/topic/58-wdb-fields/

TICKET EXAMPLE:
Title: [ITEM] Broken Longbow
Description:

Missing from Abjurist Belmara's loot
Core: Hash 809694408aaee6a20203
Database: TDB 43
Addons: Anticheat 1
Abjurist Belmara should drop Broken Longbow but doesn't.
Tested Query: Yes
Suggested Fix:
```sql
-- Adding Broken Rainbow to Abjurist Belmara in Netherstorm
DELETE FROM `creature_loot_template` WHERE `item`=25406;
INSERT INTO `creature_loot_template` (`entry`,`item`,`ChanceOrQuestChance`,`lootmode`,`groupid`,`mincountOrRef`,`maxcount`,`lootcondition`,`con
dition_value1`,`condition_value2`) VALUES
(19546,25406,24,1,0,1,1,0,0,0);
```
(note: ``` ``` is the code tag in GH: Issues)

Source: http://www.wowhead.com/?npc=19546

CONCLUSION:
Use the wiki to learn more about the DB structure. Although new things are added more quickly than the wiki is updated to explain them, it is very very useful.
We will be very harsh if we realise that a suggested fix is just a copy-paste from another project without linking the original post. We do not tolerate leeching. Credits must always be given.
Finally, thank you, because if you are reading this it means you really care about helping and wish not to waste the Devs' time!

EXAMPLES:
Note: This may not be a working code, it is used for this thread as an example.
Notice how table and field names are always included in `` symbols, and how values are written plainly, only texts are included in '' symbols.

Delete before insert into and always put fields in the query.

Wrong

INSERT INTO creature_loot_template VALUES (19546, 25406, 24, 0, 1, 1, 0, 0, 0);

Correct

-- Add item Broken Longbow to Abjurist Belmara loot in Netherstorm
DELETE FROM `creature_loot_template` WHERE `entry`=19546 AND `item`=25406;
INSERT INTO creature_loot_template (`entry`,`item`,`ChanceOrQuestChance`,`lootmode`,`groupid`,`mincountOrRef`,`maxcount`,`lootcondition`,`condition_value1`,`condition_value2`) VALUES
(19546,25406,24,1,0,1,1,0,0,0);

Make sure your update queries are precise. You may end up changing more lines than you had thought you would.

Wrong

UPDATE `creature_loot_template` SET `ChanceOrQuestChance`=100 WHERE `item`=25406;

Correct

-- Changing droprate for Broken Longbow dropped by Abjurist Belmara from 24 to 100.
UPDATE `creature_loot_template` SET `ChanceOrQuestChance`=100 WHERE `entry`=19546 AND `item`=25406;

Remember to use '' instead of a single apostrophe when writing texts, or there will be import errors.

Wrong

UPDATE `page_text` SET `text`='Hello Morgan, you shouldn't be here' WHERE `entry`=15;

Note that shouldn't is written shouldn''t

Correct

-- Correcting text for item William's Shipment.
UPDATE `page_text` SET `text`='Hello Morgan, you shouldn''t be here' WHERE `entry`=15;

SIDENOTES:
Remember that a query must always end with a semicolon( ; ).
In general, always import your own queries into you DB to check if there are any import errors and also any startup errors!

Additional guidelines:
Submit micro-fixes instead of huge ones. For example:
One-type only fix submission (Loot or Stats (and eventual spawns)...; Normal or Heroic and so on)

Comment the lines (especially loot fixes). This way the dev can easily check it without staying ages on WowHead to check the ID of each item. For example:

-- Ingvar the Plunderer (Normal)
-- delete previous loot template
DELETE FROM `creature_loot_template` WHERE `entry`=23954;
DELETE FROM `reference_loot_template` WHERE `entry` IN (14002,14003);
 
-- create new loot template
-- data: WoW Armory
INSERT INTO `creature_loot_template` (`entry`,`item`,`ChanceOrQuestChance`,`groupid`,`mincountOrRef`,`maxcount`,`lootcondition`,`condition_value1`,`condition_value2`) VALUES
(23954,35576,0,1,1,1,0,0,0), -- Ingvar`s Monolithic Cleaver
(23954,35577,0,1,1,1,0,0,0), -- Holistic Patchwork Breeches
(23954,35578,0,1,1,1,0,0,0), -- Overlaid Chain Spaulders
(23954,14002,100,0,-14002,1,0,0,0), -- referenced Ingvar`s Head for quest 11252
(23954,14003,100,0,-14003,1,0,0,0), -- referenced Ingvar`s Head for quest 11262
(23954,33470,4,0,1,7,0,0,0), -- Frostweave Cloth, chance by WowHead
(23954,43228,100,0,1,1,0,0,0); -- Stone Keeper`s Shard
 
-- create Ingvar`s Head reference loot template
-- data: WoW Armory
INSERT INTO `reference_loot_template` (`entry`,`item`,`ChanceOrQuestChance`,`groupid`,`mincountOrRef`,`maxcount`,`lootcondition`,`condition_value1`,`condition_value2`) VALUES
(14002,33330,-100,0,1,1,9,11252,0), -- Ingvar`s Head (quest 11252)
(14003,33330,-100,0,1,1,9,11262,0); -- Ingvar`s Head (quest 11262)

Yes it's a more time consuming work, but it allows us to check them quickly.

Titles (summary) of the fix submissions, i.e.:
[LOOT][Normal] Instance name
[LOOT][Normal] Boss name
[STATS] Creature name
[SPAWN] Creature name

AND REMEMBER: always check if the fix has been submitted before! If you don't check, don't be surprised if your ticket will be closed

Demonstration of proper SmartAI submission:
SAI Guide: http://www.trinitycore.org/f/index.php?/topic/1187-sai-scripting-tutorial/
SAI Wiki: http://www.trinitycore.org/w/Smart_scripts_tc2

Please follow our demonstration(s), failure to do so because of ignorance WILL get the submission deleted.
Please use the search first, you may find your fix already in another thread or in the accepted forums area.
Reminder for submitting queries.... USE the code tag: ```sql sql here ```.

Note: Always state the source_type in the delete query (0 for creature scripts, 9 for timed scripts).
If the creature already has EventAI scripts assigned, do not forget to delete them too.
smart_scripts.comment format: "NPC name - Event name - Action name"

-- Fel Cannon MKI SAI
SET @ENTRY := 22461;
SET @SPELL1 := 36238; -- Fel Cannon Blast
UPDATE `creature_template` SET `AIName`='SmartAI' WHERE `entry`=@ENTRY;
DELETE FROM `smart_scripts` WHERE `source_type`=0 AND `entryorguid`=@ENTRY;
-- always have the fields present in query (no spacs are need within the value columns)
-- comments.. more the better, tell us and everyone what the intention is
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,0,0,1,0,100,1,1000,1000,1000,1000,21,0,0,0,0,0,0,1,0,0,0,0,0,0,0,'Fel Cannon MKI - OOC - Prevent Combat Movement'),
(@ENTRY,0,1,0,0,0,100,0,0,1000,2500,2500,11,@SPELL1,1,0,0,0,0,2,0,0,0,0,0,0,0,'Fel Cannon MKI - Combat - Cast Fel Cannon Blast');

Variables:
If you reference anything more than 3 times, use a variable: official mysql documentation

This will cut down on maintenance of your script and also cuts down on typos. Additionally, it makes the script significantly easier to read and interpret (something that will help not only devs but other people trying to learn from your script).

(before)

-- Ingvar the Plunderer (Normal)
-- delete previous loot template
DELETE FROM `creature_loot_template` WHERE `entry`=23954;
DELETE FROM `reference_loot_template` WHERE `entry` IN (14002,14003);


-- create new loot template
-- data: WoW Armory
INSERT INTO `creature_loot_template` (`entry`,`item`,`ChanceOrQuestChance`,`groupid`,`mincountOrRef`,`maxcount`,`lootcondition`,`condition_value1`,`condition_value2`) VALUES
(23954,35576,0,1,1,1,0,0,0), -- Ingvar`s Monolithic Cleaver
(23954,35577,0,1,1,1,0,0,0), -- Holistic Patchwork Breeches
(23954,35578,0,1,1,1,0,0,0), -- Overlaid Chain Spaulders
(23954,14002,100,0,-14002,1,0,0,0), -- referenced Ingvar`s Head for quest 11252
(23954,14003,100,0,-14003,1,0,0,0), -- referenced Ingvar`s Head for quest 11262
(23954,33470,4,0,1,7,0,0,0), -- Frostweave Cloth, chance by WowHead
(23954,43228,100,0,1,1,0,0,0); -- Stone Keeper`s Shard

(after)

-- Ingvar the Plunderer (Normal)
SET @Ingvar :=23954;
 
-- delete previous loot template
DELETE FROM `creature_loot_template` WHERE `entry`=@Ingvar;
DELETE FROM `reference_loot_template` WHERE `entry` IN (14002,14003);


-- create new loot template
-- data: WoW Armory
INSERT INTO `creature_loot_template` (`entry`,`item`,`ChanceOrQuestChance`,`groupid`,`mincountOrRef`,`maxcount`,`lootcondition`,`condition_value1`,`condition_value2`) VALUES
(@Ingvar,35576,0,1,1,1,0,0,0), -- Ingvar`s Monolithic Cleaver
(@Ingvar,35577,0,1,1,1,0,0,0), -- Holistic Patchwork Breeches
(@Ingvar,35578,0,1,1,1,0,0,0), -- Overlaid Chain Spaulders
(@Ingvar,14002,100,0,-14002,1,0,0,0), -- referenced Ingvar`s Head for quest 11252
(@Ingvar,14003,100,0,-14003,1,0,0,0), -- referenced Ingvar`s Head for quest 11262
(@Ingvar,33470,4,0,1,7,0,0,0), -- Frostweave Cloth, chance by WowHead
(@Ingvar,43228,100,0,1,1,0,0,0); -- Stone Keeper`s Shard

(Internal) NOTE TO PUSHERS:

Before committing be sure you have last core/DB revision.
Always re-check on tip free IDs/GUIDs (use the IRC guid bot or https://github.com/TrinityCore/TC-Unused-Guid-Search)

** Various authors (Teacher, unholy, denyde, Nay)