WhysSql Template VI

[Ryan F. Mercer]

Update (Jan 2): Still working on this, just been busy for the holidays.

Ultimate Party​

This tutorial explains how to:

• Install the database browser.
• Install the modding tools.
• Add mod variables.
• Add dummy types.
• Create custom triggers.
• And more.

The true beauty of modding is that you never need to know everything at once. No matter how new and confusing something might appear, there is always a next step. One need only focus on the specific question in front of them and then explore what is possible. Within every modder is an eternal desire to experiment and discover. And with each little answer comes a small wonder that leads to whatever comes next. Join me, so that we might explore together. I have assembled these tools for you.

The WhysSql Template streamlines and enhances the Civ6 SQL environment, allows greater interoperability of mods, and provides developer tools for debugging, available to all SQL files.

To learn more, explore the Table of Contents below. The blue arrows are clickable buttons for navigating this tutorial.

 

[Ryan F. Mercer]

Features​

Print​

Variables​

Dummys & Restrict​

Trace & Source​

Disableable Triggers​

Initialize & Finalize​

Tutorials​

For Dummies​

SQLite ​

Template ​

Custom Trigger​

Demonstrator​

Miscellaneous​

About The Author​

Technical​

Troubleshooting​

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template allows users to easily print notices, warnings, and errors, as well as a trigger execution trace. This provides visibility into the workings of the code, allowing the user to identify any issues and where they occur. Additionally, an error terminates the script execution.

-- notice, warning, error.
INSERT INTO Whys_Print(Type, Message) SELECT  0, 'This is a notice.';
INSERT INTO Whys_Print(Type, Message) SELECT  1, 'This is a warning.';
INSERT INTO Whys_Print(Type, Message) SELECT -1, 'This is an error.'; -- terminates script.

Whys_Print:

Output to the print table automatically includes a timestamp, the mod, and the file from where the print occurred.

Whys_Print:

The trace output is indented according to the execution depth of the triggers, allowing the user to easily identify the source of the output and in what order the triggers were executed. In the example below, note the trigger named WhysResoHarvCtrl_BEF_INS_WhysResoHarvCtrl_Conv. This trigger occurs before an item is inserted into the table named WhysResoHarvCtrl_Conv. Within that trigger, six additional triggers are executed. Once complete, it is then followed by WhysResoHarvCtrl_AFT_INS_WhysResoHarvCtrl_Conv. That trigger occurs after the item is inserted, completing the process.

Whys_Print:

Notices and warnings can be disabled for specific triggers, both for your own mod and any other mod that uses the WhysSql Template.

-- disable in all triggers.
UPDATE Whys_Trig SET DoNotice = 0, DoWarning = 0;

-- disable in all triggers of a mod.
UPDATE Whys_Trig SET DoNotice = 0, DoWarning = 0
WHERE Mod IS 'AnyMod';

-- disable in all triggers of a file.
UPDATE Whys_Trig SET DoNotice = 0, DoWarning = 0
WHERE File IS 'AnyFile';

-- disable in named trigger.
UPDATE Whys_Trig SET DoNotice = 0, DoWarning = 0
WHERE Name IS 'AnyTrigger';

While the trace itself can not be disabled, the output of any contents of specified triggers can be truncated. Truncated triggers identify themselves as "[truncated]" in their trace output and will not display any notices, warnings, or traces from within the truncated trigger. In the following example, note the trigger WhysResoHarvCtrl_BEF_INS_WhysResoHarvCtrl_Conv from the earlier example. The six inner triggers are now hidden.

-- truncate output.
UPDATE Whys_Trig SET DoOutput = 0
WHERE Name IS 'WhysResoHarvCtrl_BEF_INS_WhysResoHarvCtrl_Conv';

Whys_Print:

Output can also be made dependent on the current debug mode, allowing it to be hidden except when debugging.

-- notice on debug.
INSERT INTO Whys_Print(Type, Message) SELECT 0, 'This is a debugging notice.'
WHERE (SELECT Debug FROM Whys_Run) >= 1;

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template allows users to easily create variables for their mod, readable by other mods. Variable names only need to be unique to the mod, allowing each mod to create variables by the same name.

Whys_Var:

Read:

SELECT Value FROM Whys_Var
WHERE Mod IS '__system' AND Name IS 'defaultPrefix';

Write:

INSERT INTO Whys_Var(Mod, Name, Value)
SELECT 'MyMod', 'defaultPrefix', 'MM_';

For convenience, Whys_myVar is provided for interacting with your own variables, without specifying the mod name.

Write:

INSERT INTO Whys_myVar(Name, Value)
SELECT 'defaultPrefix', 'MM_';

Read:

SELECT Value FROM Whys_myVar
WHERE Name IS 'defaultPrefix';

Variables can be set as constants, which can only be set on mod initialization and then can not be changed. Otherwise, variables can be added, set, or removed at any time, but only by your own mod. Variables can also be shared, so other mods can change the value, but not delete the variable itself.

Whys_Var:

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template allows users to easily manage dummy types, created by your own mod or any other mod that uses the WhysSql Template. This allows the user to filter any dummy types while performing actions on tables that may contain them.

Write:

-- add dummy type.
INSERT INTO Types(Type, Kind)
SELECT 'TECH_MY_DUMMY', 'KIND_TECH';

-- add dummy tech.
INSERT INTO Technologies(TechnologyType, Name, Cost, EraType)
SELECT 'TECH_MY_DUMMY', 'LOC_TECH_MY_DUMMY', 1000, 'ERA_ANCIENT';

-- register dummy.
INSERT INTO Whys_Dummy(Mod, Type)
SELECT 'MyMod', 'TECH_MY_DUMMY';

Read:

-- filter all dummy types.
SELECT TechnologyType FROM Technologies
WHERE TechnologyType NOT IN (SELECT Type FROM Whys_Dummy);

-- filter dummy types by mod.
SELECT TechnologyType FROM Technologies
WHERE TechnologyType NOT IN (SELECT Type FROM Whys_Dummy WHERE Mod IS 'OtherMod');

Whys_Dummy:

For convenience, Whys_myDummy is provided for interacting with your own dummys, without specifying the mod name.

-- register dummy.
INSERT INTO Whys_myDummy(Type)
SELECT 'TECH_MY_DUMMY';

-- filter my dummys.
SELECT TechnologyType FROM Technologies
WHERE TechnologyType NOT IN (SELECT Type FROM Whys_myDummy);

If another mod deletes items from a table without first checking the dummy table, it might delete your dummy, and then your own mod will fail. To prevent this, dummys and any other type you want protected, can be added to the restrict table. This ensures that any attempt to delete your dummy will cause the other mod to fail instead. The restrict table has a column for each kind of type in the game.

Whys_Restr:

-- restrict technology type.
INSERT INTO Whys_Restr(Mod, Type, TechnologyType)
SELECT 'MyMod', 'TECH_MY_DUMMY', 'TECH_MY_DUMMY';

-- restrict building type.
INSERT INTO Whys_Restr(Mod, Type, BuildingType)
SELECT 'MyMod', 'BUILDING_MY_DUMMY', 'BUILDING_MY_DUMMY';

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template allows users to easily track the traceback of trigger execution, and determine the triggering source of each trigger in the sequence. Triggers are added to the traceback as execution depth increases, and then removed as they are completed.

Whys_Trace: (traceback)

WhysResoHarvCtrl_BEF_INS_WhysResoHarvCtrl_Conv:

-- returns 'WhysResoHarvCtrl_AFT_INS_Resource_Harvests'.
SELECT SourceTrigger FROM Whys_Run;

WhysResoHarvCtrl_BEF_INS_TechnologyPrereqs:

-- returns 'WhysResoHarvCtrl_BEF_INS_WhysResoHarvCtrl_Conv'.
SELECT SourceTrigger FROM Whys_Run;

-- returns 'WhysResoHarvCtrl_VI'.
SELECT SourceMod FROM Whys_Run;

-- returns 'WhysResoHarvCtrl_VI.sql'
SELECT SourceFile FROM Whys_Run;

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template allows users to easily disable and re-enable triggers at runtime, for your own mod or any other mod that uses the WhysSql Template.

-- disable trigger.
UPDATE Whys_Trig SET Enabled = 0
WHERE Name IS 'AnyTrigger';

However, it's important to understand that disabling a trigger does not prevent an insert, update, or delete action on the table the trigger is attached to. It disables the trigger only and the action will occur without it. While there are scenarios where disabling a trigger can be useful, it should generally be avoided.

The WhysSql Template also allows triggers to be recursion limited. Recursion is when a trigger's operation retriggers itself in the process. If handled improperly, this can lead to an infinite loop, which will cause the script to fail. For this reason, triggers using the WhysSql Template are limited to zero recursion by default. Thus if a trigger triggers itself, the repeat action will still occur, but it will do so as if the trigger has been disabled. The WhysSql Template allows the user to increase and decrease the recursion limit as needed. The most common scenario for doing so is when an update trigger needs to update the table again in response to the original update.

-- increment recursion limit.
UPDATE Whys_Trig SET RecurMax =
    (SELECT RecurMax FROM Whys_Trig WHERE Name IS 'AnyTrigger') +1
WHERE Name IS 'AnyTrigger';

-- decrement recursion limit.
UPDATE Whys_Trig SET RecurMax =
    (SELECT RecurMax FROM Whys_Trig WHERE Name IS 'AnyTrigger') -1
WHERE Name IS 'AnyTrigger';

The current recursion count can be seen in the trace output for each trigger in the Whys_Print table. It appears in parentheses at the head of the trigger name.

Whys_Print:

It also appears as its own column in the Whys_Trace table, allowing the user to easily retrieve the recursion count of the previous trigger.

Whys_Trace:

-- returns recursion count of previous trigger.
SELECT SourceRecur FROM Whys_Run;

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template requires mods to be registered before accessing many of the features. Mod registration relies on custom initialization-triggers that ensure interoperability with other mods. These triggers must be attached to the Whys_Mod table and then registered before initialization.

-- create initialization-triggers.
CREATE TRIGGER MyMod_BEF_INS_Whys_Mod__init
BEFORE INSERT ON Whys_Mod BEGIN ... END;
CREATE TRIGGER MyMod_AFT_INS_Whys_Mod__init
AFTER INSERT ON Whys_Mod BEGIN ... END;

-- register initialization-triggers.
INSERT INTO Whys_Trig(Mod, File, Initialize, Name)
SELECT 'MyMod', 'MyMod.sql', 1, 'MyMod_BEF_INS_Whys_Mod__init';
INSERT INTO Whys_Trig(Mod, File, Initialize, Name)
SELECT 'MyMod', 'MyMod.sql', 1, 'MyMod_AFT_INS_Whys_Mod__init';

-- initialize.
INSERT INTO Whys_Mod(Name) SELECT 'MyMod';

Once initialized, mods appear in the Whys_Mod table. It includes a Chronology column, indicating the order mods were registered.

Whys_Mod:

Optionally, custom finalization-triggers may also be registered. These ensure proper cleanup of all database modifications made by your mod.

-- create finalization-triggers.
CREATE TRIGGER MyMod_BEF_DEL_Whys_Mod__final
BEFORE DELETE ON Whys_Mod BEGIN ... END;
CREATE TRIGGER MyMod_AFT_DEL_Whys_Mod__final
AFTER DELETE ON Whys_Mod BEGIN ... END;

-- register finalization-triggers.
INSERT INTO Whys_Trig(Mod, File, Finalize, Name)
SELECT 'MyMod', 'MyMod.sql', 1, 'MyMod_BEF_DEL_Whys_Mod__final';
INSERT INTO Whys_Trig(Mod, File, Finalize, Name)
SELECT 'MyMod', 'MyMod.sql', 1, 'MyMod_AFT_DEL_Whys_Mod__final';

Are finalization-triggers in the Civ6 SQL environment useful?

¯\_(ツ)_/¯   ...it makes reinitialization possible.​

The current state of each mod can be found in the Whys_State table. It indicates if a mod is initialized or finalized, as well as intermediate states such as pre-initializing, initializing, reinitializing, and finalizing. Knowing these intermediate states can be useful for your own triggers.

Whys_State:

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

...


Top

Contents

 

[Ryan F. Mercer]

Spoiler 🚩 :

Civ6 uses a database for storing its game data. This data includes a list of units and their stats, a list of technologies and their prerequisites, and a list of city names for different civs, just for a few examples. A lot of Civ6 modding can be accomplished by tweaking or replacing these lists or their values, such as increasing the movement of a unit, or changing the names of cities. XML can be used to make such changes and is sufficient when the changes are minor and straightforward. SQL can accomplish the same tasks, but with far greater power and flexibility, improving mod interoperability.

The Civ6 database is powered by SQLite, a lightweight database application that is directly integrated into Civ6. SQLite is free and you can use it for managing your own database if you want. For Civ6 modding however, all you need is an SQLite browser. Its purpose is to function as a development and debugging tool. It allows the user to inspect the database, observe the changes made to it by your code, and test different SQL commands to measure their impact.

Click the link below to download the SQLite database browser for the Windows 64-bit platform, then double-click the downloaded file to install. If you need it for a different platform or want to ensure you're using the latest version, then click the following link to the download page.

SQLite Browser (version: 3.13.1)​

Downloads Page​

Once installed, run the browser and either click Open Database from the toolbar, or select File → Open Database from the menu bar. Then navigate to the DebugGameplay.sqlite file and double-click to open it. For this author, the file path is as follows, but your own path may differ.

  C:\Users\<user>\AppData\Local\Firaxis Games\Sid Meier's Civilization VI\Cache\DebugGameplay.sqlite​

AppData is a hidden folder. If it is not visible, then from the File Explorer menu bar, select View → Show → Hidden items.​

This database file is generated by Civ6 whenever you start or load a game. It is not the actual in-game database, but rather a copy provided for testing. Any changes made to it by the browser will not affect the game or game saves, and reloading a game will regenerate the file.

Below the browser toolbar are four tabs: Database Structure, Browse Data, Execute SQL, Edit Pragmas. The Database Structure tab displays all of the tables and triggers that have been added to the database. Within each table is a list of the column names for that table. Table columns are similar to a spreadsheet, with a value in each column for each item listed in the table. A trigger is similar to a function that automatically executes when a change is made to a table's data. Within the list of triggers you should see OnTypeInsert. Nearly everything in Civ6 is defined by a type, and this trigger executes whenever a new type is inserted into the Types table. It ensures that each type name is converted into a unique number, for efficient identification.

Click the Browse Data tab and directly below it will appear a dropdown list labeled "Table". Click the dropdown list, press the letter 't' on your keyboard, then select "Types" from the list. Types include units, buildings, technologies, and so much more. The table contains a "Hash" column and it displays the unique number of each type.

Now click the Execute SQL tab. Below it is another tool bar, and below that is a tab labeled "SQL 1". The tab contains a textbox that allows the user to enter SQL commands. The first button on the left side of the toolbar creates new tabs, each with their own textbox. Near the middle of the toolbar is the "Execute all" button. It executes any SQL in the textbox for the selected tab. Copy and paste the following code into the textbox and click Execute all.

INSERT INTO Types(Type, Kind) SELECT 'TECH_TEST', 'KIND_TECH';

The code attempts to add a new type to the game, named "TECH_TEST", but the OnTypeInsert trigger fails. A red error message appears below the textbox: "no such function: Make_Hash". This is because the Make_Hash function is built into Civ6 and is only available in-game. It can not be run from the browser and thus a unique number can not be assigned. The solution is to replace the trigger with one that generates a random number instead. While a random number could potentially conflict with an existing hash, the number range is so large that it is extremely unlikely.

Click the button on the left side of the toolbar to create a new tab. Copy and paste the following code into the empty textbox and then execute.

DROP TRIGGER OnTypeInsert;
CREATE TRIGGER IF NOT EXISTS OnTypeInsert
AFTER INSERT ON Types
BEGIN
	UPDATE Types
	SET Hash = random()
	Where Type = NEW.Type;
END;

Now return to the "SQL 1" tab and reattempt to execute the command. The error is gone! Click the Browse Data tab, go to the Types table, and scroll to the bottom. The new type "TECH_TEST" appears at the end of the list.

Notice the "Undo" button on the right side of the top toolbar. It will undo the most recent change made to the database and allows the user to keep backing up to before any changes were made.

Testing your SQL code in Civ6 requires relaunching the game from the desktop, every time you make a change. This can be time consuming, thus the Execute SQL tab is a good place to test anything you're not sure about. However, it's important to understand that not every command that works in the SQLite browser necessarily works in Civ6. This is likely due to Civ6 using an older version of SQLite.

When your SQL code fails in-game due to a syntax error, an unrecognized command, or something else fundamental, an error message appears in the database log file. For this author, the file path is as follows, but your own path may differ.

  C:\Users\<user>\AppData\Local\Firaxis Games\Sid Meier's Civilization VI\Logs\Database.log​

The log file can contain a lot of unrelated messages, so look for anything labeled "[Gameplay]". Unfortunately, the gameplay error messages do a poor job of elaborating. In particular, it can not tell you the line number for a syntax error and instead will describe a character next to where it occurred, such as a parenthesis, comma, or a keyword. This can become maddening when working on a large file, so only making small changes between tests can be key to locating a problem. This may represent the single greatest obstacle to Civ6 SQL modding.

Additionally, when relaunching Civ6, it is important to first close the database file in the SQLite browser. Otherwise, the log will complain that the database was busy, and your changes will not appear in the database. If you see a red "X" on the right side of the top toolbar in the browser, then the database is still open.

Also, before exiting Civ6, it is important not to exit to the main menu too quickly, or it will fail to generate a new copy of the database. Allow two to three seconds of game play before exiting to the main menu. It is best to always exit to the main menu before exiting to the desktop.


Top

Contents

 

[Ryan F. Mercer]

Spoiler :

Welcome to Civ 6 SQL modding!

The first thing you’ll need is the Sid Meier’s Civilization VI Development Tools. If you already have it installed, then open ModBuddy and start a new Empty Mod project named "MySql_VI". Otherwise, follow the install instructions below.

Spoiler Install Development Tools :

You can find the Development Tools in the Steam library by searching for "Civilization VI". You will see two search results under the TOOLS header. One is labeled "Tools" and the other "Assets", though you may need to widen the search results window to tell them apart. Install and launch the tools only. The assets will not be used in this portion of the tutorial.

Launching the tools will open a menu with "ModBuddy" at the top of the list. This is your code editor that you will use to write your SQL. Lower down in the list is "FireTuner". It is primarily used as a text output screen that provides technical information while the program runs. It is not necessary for this tutorial.

Click the ModBuddy button to get started.

You can either create a new project from the menu bar at the top of the window, by selecting File → New → Project, or by clicking New Mod in the content window. A new window will open with a list of available templates to choose from. Select Empty Mod from the list. Below the list is the "Name" field, with a default name of "EmptyMod1". Rename it to "MySql_VI" and click OK. You will have the opportunity to create a mod with your own unique name later. For now, use this name for demonstration purposes. A window will open with a "Title" field at the top, and a default title of "My Custom Mod". Rename the title to "MySql_VI" and click Finish.

From the ModBuddy menu bar, select Tools → Options, and an Options window will open with a list of menu items on the left. Double-click "Text Editor" in the list to reveal additional items below it. Then double-click "All Languages" to reveal additional items below that. On the right, check the box for "Line numbers". Then select "Scroll Bars" from the list on the left. Under the "Behavior" header on the right, click the "Use map mod for vertical scroll bar", then click OK. This will make your project files easier to navigate. You may also want to close the "Properties" interface on the right side of the ModBuddy window.

On the left side of your ModBuddy window is an interface with the header "Solution Explorer". If you’ve closed it by accident, you can reopen it from the menu bar by selecting View → Solution Explorer. It lists all of your project files, as well as any other files you may have opened. Your mod automatically includes a file named MySql_VI.Art.xml. It is unnecessary for this tutorial and can be ignored.

Now click the link below to download the WhysSql_VI.zip file and save it to your desktop. Double-click the zip file to open a folder view of its contents. Drag the WhysSql_VI folder to your desktop, then delete the zip file.

WhysSql_VI.zip​

In the Solution Explorer, right-click on MySql_VI and select Add → New Folder from the menu. Name the folder "WhysSql_VI", then right-click on the folder and select Add → Existing Item. A file selection window will open. Navigate to your desktop and double-click the WhysSql_VI folder to open it. Select the file it contains and click Add. Now delete the WhysSql_VI folder from your desktop.

While this file is now a part of your project, it is not currently a part of your mod when your mod is loaded in game. In the Solution Explorer, right-click on MySql_VI again and select Properties from the menu. The project properties window gives you access to a variety of vital configuration settings. On the left side is a list of menu options. Click In-Game Actions at the bottom and then click the Add Action button on the right. An "UpdateDatabase" action will be added to the list box by default. You may want to full screen the window at this point if you haven’t already. Click on the action in the list box to select it, and a new interface will appear on the right. Near the top is the "Type" field, which is a dropdown list of available actions. It is already set to the action you need. Below it are four sections that open and close when their circular arrow buttons are clicked. The bottom section is labeled "Files", which is open by default and should be the only section open. Now click the Add button to the right of it to designate which project file to use when your mod is loaded. A new window will open with a "File" field at the top. It is a dropdown list of available project files. Click the down-arrow to the right of it, select WhysSql_VI.sql from the list, then click OK. Now click the circular arrow button to open the Custom Properties section above. Click the Add button to the right and a new window will open with a "Name" field at the top and a "Value" field below it. In the name field type "LoadOrder" and in the value field type "1". Click OK, then click the circular arrow again to close the Custom Properties.

This support file provides the full functionality of the WhysSql Template, allowing you to use all of its features to easily build a more robust and interoperable mod. However, your mod isn’t currently doing anything. For that, you will need another database-script, which runs your custom code when your mod is loaded. In the Solution Explorer, right-click MySql_VI and select Add → New Item from the menu. A new window will open with a list of available templates to choose from. Select Database (SQL) from the list. Below the list is the "Name" field, with a default name of "GameData1.sql". Rename it to "MySql_VI.sql" and click Add.

Now return to the project properties window. If you accidentally closed it, right-click on MySql_VI in the Solution Explorer and select Properties again, then click In-Game Actions if it’s not already selected. Add a new action and leave the type as UpdateDatabase. In the Files section, click Add, select MySql_VI.sql from the dropdown list, and click OK. Then close the project properties window.

Delete the contents of your custom MySql_VI.sql file, then select and copy the following code.

Spoiler Code :

--==============================================================================
/* FILE    : <undefined_script_file>
   VERSION : <undefined_script_version>
   AUTHOR  : <undefined_script_author>
   LICENSE : CC BY-NC-SA 4.0
Creative Commons: This license requires that reusers give credit to the creator.
It allows reusers to distribute, remix, adapt, and build upon this file in any
medium or format, for noncommercial purposes only.  If others modify or adapt
this file, they must license the modified file under identical terms.

--==================================================
-- File Description
--==================================================
To use this file, add it to your mod project as an In-Game 'UpdateDatabase'
action with custom property: name "LoadOrder", value ( > WhysSql_VI.sql).

<undefined_script_description>

--==================================================
-- File Contents
--==================================================
To navigate this file, line select a name below and do a find (ctrl+f).
To return here, do a find for triple-backtick (```).
Including a double-hyphen at the end of names prevents partial matches.

For Debugging--
<undefined_mod_prefix>BEF_INS_Whys_Mod__init--
<undefined_mod_prefix>AFT_INS_Whys_Mod__init--
<undefined_mod_prefix>BEF_DEL_Whys_Mod__final--
<undefined_mod_prefix>AFT_DEL_Whys_Mod__final--
Register & Initialize--

--==================================================
-- For Debugging--
--==================================================
-- Initial 'Execute SQL' for database testing outside of Civ6.  Provides
-- stand-in Make_Hash() function that returns a random number instead of a hash.
-- While it is technically possible for the random number to conflict with an
-- existing hash, it is *extremely* unlikely.  This helpful solution has been
-- provided by Deliverator at CivFanatics.com.

DROP TRIGGER OnTypeInsert;
CREATE TRIGGER IF NOT EXISTS OnTypeInsert
AFTER INSERT ON Types
BEGIN
	UPDATE Types
	SET Hash = random()
	Where Type = NEW.Type;
END;
-- optional.
PRAGMA foreign_keys = ON;
PRAGMA recursive_triggers = ON;

*/

-- notice.
INSERT INTO Whys_Print(Mod, File, Type, Message)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 0, ''
	||'Loading file "<undefined_script_file>".'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

--==============================================================================
-- Before trigger.
--
-- Manages initialization of this mod.  Only executes when new Name is this mod.
-- Provides trace.
--
-- @see  Whys_BEF_INS_Whys_Mod.
--
CREATE TRIGGER IF NOT EXISTS <undefined_mod_prefix>BEF_INS_Whys_Mod__init--
BEFORE INSERT ON Whys_Mod WHEN

	-- enabled & recursion filter.
	(SELECT DoTrigger FROM Whys_Filt WHERE Trigger IS
			'<undefined_mod_prefix>BEF_INS_Whys_Mod__init')

	-- allow.
	AND
	(	-- own Name.
		NEW.Name IS '<undefined_mod_name>'
	)

----------------------------------------------------
BEGIN

	-- start.
	INSERT INTO Whys_Run(Trigger)
	SELECT '<undefined_mod_prefix>BEF_INS_Whys_Mod__init';

	-- trace.
	INSERT INTO Whys_Print(Type, Message) SELECT 2, ''
		||'NEW'
		||'( '||IFNULL(NEW.Name, 'NULL')
		||', '||IFNULL(NEW.Iteration, 'NULL')
		||', '||IFNULL(NEW.Author, 'NULL')
		||', '||IFNULL(NEW.Credit, 'NULL')
		||' ).'
	WHERE (SELECT DoOutput FROM Whys_Run);

	-- notice, forced.
	INSERT INTO Whys_Print(Type, Message) SELECT 0, ''
		||'Pre-'||(CASE WHEN EXISTS(SELECT 1 FROM Whys_State WHERE Mod IS NEW.Name)
				THEN 're' ELSE '' END)||'initializing mod "'||NEW.Name||'".';

	-- pre-reinitializing.
	UPDATE Whys_State SET IsPreIniting = 1, IsIniting = 0, IsReIniting = 1
		, IsInit = 0, IsFinaling = 0, IsFinal = 0
	WHERE EXISTS(SELECT 1 FROM Whys_State WHERE Mod IS NEW.Name)
		AND Mod IS NEW.Name;

	-- pre-initializing.
	INSERT INTO Whys_State(Mod) SELECT NEW.Name
	WHERE NOT EXISTS(SELECT 1 FROM Whys_State WHERE Mod IS NEW.Name);

	--==================================================
	-- Custom Initialization (before-insert)
	--==================================================

	-- do-nothing.

	--==================================================

	-- finish.
	DELETE FROM Whys_Run;

END;
----------------------------------------------------

-- notice.
INSERT INTO Whys_Print(Mod, File, Type, Message)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 0, ''
	||'Created trigger '
	||'<undefined_mod_prefix>BEF_INS_Whys_Mod__init.'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

--==============================================================================
-- After trigger.
--
-- Manages initialization of this mod.  Disables initialize-triggers for this
-- mod.  Performs setup.
--
-- @see  <undefined_mod_prefix>BEF_INS_Whys_Mod__init.
--
CREATE TRIGGER IF NOT EXISTS <undefined_mod_prefix>AFT_INS_Whys_Mod__init--
AFTER INSERT ON Whys_Mod WHEN

	-- enabled & recursion filter.
	(SELECT DoTrigger FROM Whys_Filt WHERE Trigger IS
			'<undefined_mod_prefix>AFT_INS_Whys_Mod__init')

	-- allow.
	AND
	(	-- own Name.
		NEW.Name IS '<undefined_mod_name>'
	)

----------------------------------------------------
BEGIN

	-- start.
	INSERT INTO Whys_Run(Trigger)
	SELECT '<undefined_mod_prefix>AFT_INS_Whys_Mod__init';

	-- trace.
	INSERT INTO Whys_Print(Type, Message) SELECT 2, ''
		||'NEW'
		||'( '||IFNULL(NEW.Name, 'NULL')
		||', '||IFNULL(NEW.Iteration, 'NULL')
		||', '||IFNULL(NEW.Author, 'NULL')
		||', '||IFNULL(NEW.Credit, 'NULL')
		||' ).'
	WHERE (SELECT DoOutput FROM Whys_Run);

	-- notice, forced.
	INSERT INTO Whys_Print(Type, Message) SELECT 0, ''
		||(CASE WHEN (SELECT IsReIniting FROM Whys_State WHERE Mod IS NEW.Name) IS 1
				THEN 'Rei' ELSE 'I' END)||'nitializing mod "'||NEW.Name||'".';

	-- initializing/reinitializing.
	UPDATE Whys_State SET IsPreIniting = 0, IsIniting = 1
	WHERE Mod IS NEW.Name;

	-- disable initialize-triggers.
	UPDATE Whys_Trig SET Enabled = 0 WHERE Mod IS NEW.Name AND Initialize IS 1;

	--==================================================
	-- Custom Initialization (after-insert)
	--==================================================

	-- add triggers, can not REPLACE.
	DELETE FROM Whys_Trig WHERE Mod IS NEW.Name
		AND Initialize IS NOT 1 AND Finalize IS NOT 1;
	--INSERT INTO Whys_Trig(Name)
	--VALUES
	--	  ('<undefined_mod_prefix><undefined_trigger_name>')
	--	, ('<undefined_mod_prefix><undefined_trigger_name>')
	--  ;

	-- notice.
	INSERT INTO Whys_Print(Type, Message) SELECT 0, ''
		||(CASE WHEN (SELECT IsReIniting FROM Whys_State WHERE Mod IS NEW.Name) IS 1
				THEN 'Reset' ELSE 'Added' END)||' triggers.'
	WHERE (SELECT DoNotice FROM Whys_Run);

	-- add mod info.
	UPDATE Whys_Mod SET
		  Iteration = 1
		, Author = ''
				||'<undefined_mod_author>'
		, Credit = ''
				||'<undefined_mod_credit>'
		--
	WHERE Name IS NEW.Name;

	-- add variables, can not REPLACE.
	DELETE FROM Whys_myVar WHERE IsPersist IS NOT 1;
	INSERT INTO Whys_myVar(IsConst, IsShared, Name, Value)
	VALUES
		  (1, 0, 'defaultPrefix', '<undefined_mod_prefix>')
		--, (1, 0, '<undefined_name>', '<undefined_value>')
		;

	-- notice.
	INSERT INTO Whys_Print(Type, Message) SELECT 0, ''
		||'Added variables.'
	WHERE (SELECT DoNotice FROM Whys_Run);

	-- modify database.
	-- do-nothing.

	--==================================================

	-- confirm.
	INSERT INTO Whys_Print(Type, Message) SELECT 3, ''
		||'Insert successful.'
	WHERE (SELECT DoConfirm FROM Whys_Run);

	-- finish.
	DELETE FROM Whys_Run;

END;
----------------------------------------------------

-- notice.
INSERT INTO Whys_Print(Mod, File, Type, Message)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 0, ''
	||'Created trigger '
	||'<undefined_mod_prefix>AFT_INS_Whys_Mod__init.'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

--==============================================================================
-- Before trigger.
--
-- Manages finalization of this mod.  Only executes when old Name is this mod.
-- Provides trace.
--
-- @see  Whys_BEF_DEL_Whys_Mod.
--
CREATE TRIGGER IF NOT EXISTS <undefined_mod_prefix>BEF_DEL_Whys_Mod__final--
BEFORE DELETE ON Whys_Mod WHEN

	-- enabled & recursion filter.
	(SELECT DoTrigger FROM Whys_Filt WHERE Trigger IS
			'<undefined_mod_prefix>BEF_DEL_Whys_Mod__final')

	-- allow.
	AND
	(	-- own Name.
		OLD.Name IS '<undefined_mod_name>'
	)

----------------------------------------------------
BEGIN

	-- start.
	INSERT INTO Whys_Run(Trigger)
	SELECT '<undefined_mod_prefix>BEF_DEL_Whys_Mod__final';

	-- trace.
	INSERT INTO Whys_Print(Type, Message) SELECT 2, ''
		||'OLD'
		||'( '||IFNULL(OLD.Name, 'NULL')
		||', '||IFNULL(OLD.Iteration, 'NULL')
		||', '||IFNULL(OLD.Author, 'NULL')
		||', '||IFNULL(OLD.Credit, 'NULL')
		||' ).'
	WHERE (SELECT DoOutput FROM Whys_Run);

	-- notice, forced.
	INSERT INTO Whys_Print(Type, Message) SELECT 0, ''
		||'Finalizing mod "'||OLD.Name||'".';

	-- finalizing.
	UPDATE Whys_State SET IsIniting = 0, IsReIniting = 0, IsInit = 0
		, IsFinaling = 1, IsFinal = 0
	WHERE Mod IS OLD.Name;

	--==================================================
	-- Custom Finalization (before-delete)
	--==================================================

	-- do-nothing.

	--==================================================

	-- finish.
	DELETE FROM Whys_Run;

END;
----------------------------------------------------

-- notice.
INSERT INTO Whys_Print(Mod, File, Type, Message)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 0, ''
	||'Created trigger '
	||'<undefined_mod_prefix>BEF_DEL_Whys_Mod__final.'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

--==============================================================================
-- After trigger.
--
-- Manages finalization of this mod.  Disables finalize-triggers for this mod.
-- Provides confirm.
--
-- @see  <undefined_mod_prefix>BEF_DEL_Whys_Mod__final.
--
CREATE TRIGGER IF NOT EXISTS <undefined_mod_prefix>AFT_DEL_Whys_Mod__final--
AFTER DELETE ON Whys_Mod WHEN

	-- enabled & recursion filter.
	(SELECT DoTrigger FROM Whys_Filt WHERE Trigger IS
			'<undefined_mod_prefix>AFT_DEL_Whys_Mod__final')

	-- allow.
	AND
	(	-- own Name.
		OLD.Name IS '<undefined_mod_name>'
	)

----------------------------------------------------
BEGIN

	-- start.
	INSERT INTO Whys_Run(Trigger)
	SELECT '<undefined_mod_prefix>AFT_DEL_Whys_Mod__final';

	-- trace.
	INSERT INTO Whys_Print(Type, Message) SELECT 2, ''
		||'OLD'
		||'( '||IFNULL(OLD.Name, 'NULL')
		||', '||IFNULL(OLD.Iteration, 'NULL')
		||', '||IFNULL(OLD.Author, 'NULL')
		||', '||IFNULL(OLD.Credit, 'NULL')
		||' ).'
	WHERE (SELECT DoOutput FROM Whys_Run);

	-- disable finalize-triggers.
	UPDATE Whys_Trig SET Enabled = 0 WHERE Mod IS OLD.Name AND Finalize IS 1;

	--==================================================
	-- Custom Finalization (after-delete)
	--==================================================

	-- do-nothing.

	--==================================================

	-- confirm.
	INSERT INTO Whys_Print(Type, Message) SELECT 3, ''
		||'Delete successful.'
	WHERE (SELECT DoConfirm FROM Whys_Run);

	-- finish.
	DELETE FROM Whys_Run;

END;
----------------------------------------------------

-- notice.
INSERT INTO Whys_Print(Mod, File, Type, Message)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 0, ''
	||'Created trigger '
	||'<undefined_mod_prefix>AFT_DEL_Whys_Mod__final.'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

--==============================================================================
-- Register & Initialize--
--

-- register initialize-trigger (before-insert).
INSERT INTO Whys_Trig(Mod, File, Initialize, Name)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 1, ''
	||'<undefined_mod_prefix>BEF_INS_Whys_Mod__init'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

-- register initialize-trigger (after-insert).
INSERT INTO Whys_Trig(Mod, File, Initialize, Name)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 1, ''
	||'<undefined_mod_prefix>AFT_INS_Whys_Mod__init'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

-- notice.
INSERT INTO Whys_Print(Mod, File, Type, Message)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 0, ''
	||'Registered initialize triggers.'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

-- register finalize-trigger (before-delete).
INSERT INTO Whys_Trig(Mod, File, Finalize, Name)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 1, ''
	||'<undefined_mod_prefix>BEF_DEL_Whys_Mod__final'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

-- register finalize-trigger (after-delete).
INSERT INTO Whys_Trig(Mod, File, Finalize, Name)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 1, ''
	||'<undefined_mod_prefix>AFT_DEL_Whys_Mod__final'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

-- notice.
INSERT INTO Whys_Print(Mod, File, Type, Message)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 0, ''
	||'Registered finalize triggers.'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

-- notice.
INSERT INTO Whys_Print(Mod, File, Type, Message)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 0, ''
	||'Loaded file "<undefined_script_file>" successfully.'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

-- initialize.
INSERT INTO Whys_Mod(Name) SELECT '<undefined_mod_name>'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

--==============================================================================

Paste the code to your MySql_VI.lua file and then press the ctrl + home keys to go to the top. There you will see "<undefined_script_file>", as well as some other undefined labels. These will be replaced in a moment.

On line 5, there is a Creative Commons license that should appeal to most modders, and a detailed explanation is given beneath it. Below is a short list of alternative Creative Commons licenses that you might prefer. If you wish to explore all of the possible options, then click the link below to use the license wizard.

Spoiler Licenses :

More Restrictive:

   LICENSE : CC BY-NC-ND 4.0
Creative Commons: This license requires that reusers give credit to the creator.
It allows reusers to copy and distribute this file in any medium or format in
unadapted form and for noncommercial purposes only.

Less Restrictive:

   LICENSE : CC BY-NC 4.0
Creative Commons: This license requires that reusers give credit to the creator.
It allows reusers to distribute, remix, adapt, and build upon this file in any
medium or format, for noncommercial purposes only.

Least Restrictive:

   LICENSE : CC BY 4.0
Creative Commons: This license requires that reusers give credit to the creator.
It allows reusers to distribute, remix, adapt, and build upon this file in any
medium or format, even for commercial purposes.

No Restrictions:

   LICENSE : CC0 1.0 Universal
Creative Commons: This license is dedicated to the public domain.  It allows
reusers to distribute, remix, adapt, and build upon the material in any medium
or format, even for commercial purposes.

Creative Commons License Wizard​

Next, scroll down to view the "File Contents" section. While your mod is still small, this only offers documentation of what is in the file and in what order. Thus it may be tempting to ignore it and not properly update it as the file contents change. But once your mod reaches significant size, it can become difficult to navigate and find what you’re looking for. When used properly, the File Contents provide a vital means of moving about your file with a quick and easy select and search. This method of navigation will be used throughout this tutorial.

Within the File Contents section, you will see the following text.

Register & Initialize--​

Click the line number to the left of it to select the entire line. Now press the ctrl + f keys to do a find. A search box will open in the top right corner. At the top of the box is the search-text input. At the bottom is a dropdown menu. Make sure the dropdown menu has the "Current Document" option selected, then click the "Find next" arrow button to the right of the search-text. This will take you to that location in the file. In this case, it’s where your mod is registered and initialized. Except for replacing the undefined labels, most modders will never need to change anything in this section and should leave it alone. Click the find-next in the search box again to quickly return to the File Contents.

Scroll to the top of the page, select the "<undefined_script_file>" label, then do a find. In the search box, there is a down arrow to the left of the search-text input. Click it to open the replacement-text input, which will appear directly beneath the search-text input. In the replacement-text input, type "MySql_VI.sql". Be sure to include the file extension. To the right of the input are two buttons. The one on the left is the "Replace next" button and the one on the right is the "Replace all" button. Click the replace-all and a results window will open. It should say, "15 occurrence(s) replaced." Click OK.

Return to the file contents and select the "<undefined_mod_prefix>" label. Do a find and replace the text with "My_". The results window will say, "29 occurrence(s) replaced."

The mod prefix is important for identifying your custom tables and triggers, and it should be unique to prevent potential conflict with other mods. The prefix can only use alphanumeric and underscore characters, and can not begin with a digit. It is strongly recommended that it be ended with an underscore.

Scroll down until you see an "<undefined_mod_name>" label. Select it and replace the text with "MySql_VI". The results window will say, "30 occurrence(s) replaced."

Return to the file contents, line select the text "My_BEF_INS_Whys_Mod__init--", then do a find and click find-next. This will take you to your first initialization trigger, which executes before your mod is inserted into the Whys_Mod table. This trigger handles the pre-initialization process for your mod. Scroll down and you will see a text block with the header "Custom Initialization (before-insert)". This provides an uncluttered space for adding your own custom pre-initialization code, but most modders will never need to change anything in this section and should leave it alone.

Next, close the search box in the top right corner. If you ever need to return to the File Contents section, you can always do a find for triple-backtick. The backtick looks like a backwards apostrophe and the key is typically found to the left of the 1 key on your keyboard. Do a find for triple-backtick now. You should be at the File Contents.

Select "My_AFT_INS_Whys_Mod__init--" and do a find. This will take you to your second initialization trigger, which executes after your mod is inserted into the Whys_Mod table. This trigger handles the actual initialization and setup of your mod. Scroll down and you will see a text block with the header "Custom Initialization (after-insert)". This is the core of your mod, where you make changes to the existing database, define your mod variables, and register any additional triggers.

Return to the File Contents and navigate to the "My_BEF_DEL_Whys_Mod__final--" trigger. This is your first finalization trigger, which executes before your mod is deleted from the Whys_Mod table. Scroll down and you will see a text block with the header "Custom Finalization (before-delete)". Ideally, it should handle the cleanup of your mod, reverting the database to what it was before your mod was initialized. However, this isn't particularly necessary in the Civ6 SQL environment and most modders can choose to ignore it.

Now navigate to the "My_AFT_DEL_Whys_Mod__final--" trigger. This is your second finalization trigger, which executes after your mod is deleted from the Whys_Mod table. This trigger handles the post-finalization process for your mod, and if you scroll down, you will see a text block with the header "Custom Finalization (after-delete)". Again, this isn't particularly necessary in the Civ6 SQL environment and most modders can choose to ignore it.

Finally, navigate back to your "My_AFT_INS_Whys_Mod__init--" trigger and scroll to the bottom of the Custom Initialization block. You will see the comment "modify database", and under that the comment "do-nothing". Below it, paste the following code.

	-- notice.
	INSERT INTO Whys_Print(Type, Message) SELECT 0, ''
		||'>>> Hello World! <<<'
	WHERE (SELECT DoNotice FROM Whys_Run);

This will print the "Hello World" message to the Whys_Print table when your mod is loaded in-game.

Click Build in the menu bar and select Build Solution. This is a necessary step that will save and compile your code, making it usable in game. You will need to rebuild your code each time you make changes to it, in order for those changes to take effect. You will also need to relaunch Civ6 from the desktop, and be sure to first close the database if it is open in the SQLite browser.

Now launch Civilization VI and click the Additional Content button in the main menu, then click Mods in the sub menu. Ensure your mod, and only your mod, is currently enabled. Return to the main menu and start a new single player game. Once the game is loaded, click the Begin Game button and then exit to the main menu. Note, it is important not to exit too quickly, or the game will fail to generate a new copy of the database. Allow two to three seconds of game play before exiting to the main menu. It is best to always exit to the main menu before exiting to the desktop.

Go to the SQLite browser and open the database file. Then click the Browse Data tab and navigate to the Whys_Print table. Scroll to the bottom and you should see the following output.

>>> Hello World! <<<​

Congratulations! You’re a Civ6 SQL modder.


Continue to the next section to learn how to add custom triggers to your script.


Top

Contents

 

[Ryan F. Mercer]

Spoiler :

This section of the tutorial is a continuation of the previous section and demonstrates how to add a custom trigger to your script. The following section then provides a demonstrator mod and an explanation of its code.

In your MySql_VI.sql script, navigate to your My_BEF_INS_Whys_Mod__init trigger. Scroll up and above it is a notice with the message "Loading file". Below the notice but before the trigger, paste the following code.

Spoiler Code :

--==============================================================================
-- Persistent table.
--
-- <undefined_table_description>
--
CREATE TABLE IF NOT EXISTS <undefined_mod_prefix><undefined_table_name>--
	( <undefined_primary_column> TEXT NOT NULL
	, <undefined_regular_column> ANY
	, PRIMARY KEY(<undefined_primary_column>)
	);

-- notice.
INSERT INTO Whys_Print(Mod, File, Type, Message)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 0, ''
	||'Created table '
	||'<undefined_mod_prefix><undefined_table_name>.'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

This adds a custom table to your script. The undefined labels will be replaced in a moment. This table has two columns. A primary column that only allows a text value, and a regular column that allows any kind of value. Because the first column is primary, each text value must be unique for each table row.

Below the table definition is a notice with the message "Created table". It includes the mod name and script file. Within triggers, these aren't required for print messages, but this notice is in the main space of your script, thus the mod name and script file must be included. The "WHERE" clause at the end of the notice ensures that the message is only printed once, even if your script file is unintentionally loaded multiple times. It does this by checking if your mod is already initialized.

Below the notice, paste the following code.

Spoiler Code :

--==============================================================================
-- Before trigger.
--
-- <undefined_trigger_description>
--
CREATE TRIGGER IF NOT EXISTS <undefined_mod_prefix><undefined_before_trigger>--
BEFORE INSERT ON <undefined_mod_prefix><undefined_table_name> WHEN

	-- enabled & recursion filter.
	(SELECT DoTrigger FROM Whys_Filt WHERE Trigger IS
			'<undefined_mod_prefix><undefined_before_insert_trigger>')

	-- allow.
	AND
	(	-- all.
		1
	)

----------------------------------------------------
BEGIN

	-- start.
	INSERT INTO Whys_Run(Trigger)
	SELECT '<undefined_mod_prefix><undefined_before_trigger>';

	-- trace.
	INSERT INTO Whys_Print(Type, Message) SELECT 2, ''
		||'NEW'
		||'( '||IFNULL(NEW.<undefined_primary_column>, 'NULL')
		||', '||IFNULL(NEW.<undefined_regular_column>, 'NULL')
		||' ).'
	WHERE (SELECT DoOutput FROM Whys_Run);

	-- proceed.
	-- do-nothing.

	-- finish.
	DELETE FROM Whys_Run;

END;
----------------------------------------------------

-- notice.
INSERT INTO Whys_Print(Mod, File, Type, Message)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 0, ''
	||'Created trigger '
	||'<undefined_mod_prefix><undefined_before_trigger>.'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

This adds a custom trigger to your script. It will be attached to your custom table and it will execute each time a new table row is inserted. Specifically, it will execute immediately before the data is actually added to the table.

Below the trigger, paste the following code.

Spoiler Code :

--==============================================================================
-- After trigger.
--
-- <undefined_trigger_description>
--
CREATE TRIGGER IF NOT EXISTS <undefined_mod_prefix><undefined_after_trigger>--
AFTER INSERT ON <undefined_mod_prefix><undefined_table_name> WHEN

	-- enabled & recursion filter.
	(SELECT DoTrigger FROM Whys_Filt WHERE Trigger IS
			'<undefined_mod_prefix><undefined_after_trigger>')

	-- allow.
	AND
	(	-- all.
		1
	)

----------------------------------------------------
BEGIN

	-- start.
	INSERT INTO Whys_Run(Trigger)
	SELECT '<undefined_mod_prefix><undefined_after_trigger>';

	-- trace.
	INSERT INTO Whys_Print(Type, Message) SELECT 2, ''
		||'NEW'
		||'( '||IFNULL(NEW.<undefined_primary_column>, 'NULL')
		||', '||IFNULL(NEW.<undefined_regular_column>, 'NULL')
		||' ).'
	WHERE (SELECT DoOutput FROM Whys_Run);

	-- proceed.
	-- do-nothing.

	-- confirm.
	INSERT INTO Whys_Print(Type, Message) SELECT 3, ''
		||'Insert successful.'
	WHERE (SELECT DoConfirm FROM Whys_Run);

	-- finish.
	DELETE FROM Whys_Run;

END;
----------------------------------------------------

-- notice.
INSERT INTO Whys_Print(Mod, File, Type, Message)
SELECT '<undefined_mod_name>', '<undefined_script_file>', 0, ''
	||'Created trigger '
	||'<undefined_mod_prefix><undefined_after_trigger>.'
WHERE (SELECT IsInit FROM Whys_State
		WHERE Mod IS '<undefined_mod_name>') IS NULL;

This adds another custom trigger to your script. It will also be attached to your custom table and it will also execute each time a new table row is inserted. Specifically, it will execute immediately after the data is added to the table, but only if the action succeeded at adding the data. For example, if the text value for the primary column attempts to duplicate an existing value in that column, then the action will fail because the text is not unique. In that case, not only will the after-trigger not execute, but any changes made by the before-trigger will be automatically undone.

Now scroll up to your custom table. Replace "<undefined_table_name>" with "CustomTable" (4 occurrences). Replace"<undefined_mod_prefix>" with "My_" (12 occurrences). Replace "<undefined_primary_column>" with "PrimaryCol" (4 occurrences). Replace "<undefined_regular_column>" with "RegularCol" (3 occurrences).

Below that, replace "<undefined_mod_name>" with "MySql_VI" (6 occurrences). Replace "<undefined_script_file>" with "MySql_VI.sql" (3 occurrences).

Scroll down to your custom before-trigger. Replace "<undefined_before_trigger>" with "BEF_INS_My_CustomTable" (4 occurrences). Then scroll down to your custom after-trigger. Replace "<undefined_after_trigger>" with "AFT_INS_My_CustomTable" (4 occurrences).

Go to the File Contents, select and copy the following text, then paste it between "For Debugging--" and "My_BEF_INS_Whys_Mod__init--". This will update the contents to reflect the new code.

CREATE TABLE IF NOT EXISTS My_CustomTable--
My_BEF_INS_My_CustomTable--
My_AFT_INS_My_CustomTable--

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

...


Top

Contents

 

[Ryan F. Mercer]

Spoiler :

I am a Civ modding enthusiast with no special relationship with Firaxis or any associated entity. I began developing the WhysSql Template on about August 1st 2025. All of the programming is my own work and was built from scratch. I developed it to enhance the Civ SQL modding environment and make it more user friendly. It was designed organically from first principles. I have made it freely available for noncommerical use in hopes of widespread adoption. This will lead to better mods, better modders, and a more active modding community.

Special Thanks:

• Deliverator – for their initial 'Execute SQL' for database testing outside of Civ6.
• whoward69 – for their Circumventing mod load order issues with database triggers tutorial, explaining how to use triggers to improve mod interoperability.

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

This author uses a version ID for their work. The ID is composed not only of the version number, but also the build date, and is preceded by a format indicator. The version number is always a whole number and only increments when a build is no longer backwards compatible with the previous build. Example below.

Spoiler Change Log :

None.

Spoiler Deprecated :

None.

Top

Contents

 

[Ryan F. Mercer]

...reserved...

 

[Ryan F. Mercer]

...reserved...

 

[Ryan F. Mercer]

...reserved...

 

[Ryan F. Mercer]

...reserved...

 

[Ryan F. Mercer]

...reserved...