Market Simulation (Part 24): Getting Started with SQL (VII)

Introduction

In the previous article, Market Simulation (Part 23): Getting Started with SQL (VI) several important points that must be understood about SQL were explained. This gives us the minimum foundation needed to work with databases. So, since we still have a great deal to discuss and the topic will be fairly complex, we will not waste time on an introduction. Let us go straight to the point.

Understanding the problem

Databases are nothing more than a set of records in a file. But to understand what we are trying to explain, let us use the following code:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL
17. );
18. 
19. INSERT INTO tb_Symbols (id, symbol) VALUES
20.     (2, 'PETR4'),
21.     (1, 'ITUB3'),
22.     (3, 'VALE3');
23. 
24. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
25.     ('2023-07-10', '22.00', 1),
26.     ('2023-07-11', '22.20', 1),
27.     ('2023-07-12', '22.40', 1),
28.     ('2023-07-13', '22.30', 1),
29.     ('2023-07-14', '22.60', 1),
30.     ('2023-07-10', '26.00', 2),
31.     ('2023-07-11', '26.20', 2),
32.     ('2023-07-12', '26.40', 2),
33.     ('2023-07-13', '26.30', 2),
34.     ('2023-07-14', '26.60', 2),
35.     ('2023-07-10', '62.00', 3),
36.     ('2023-07-11', '62.20', 3),
37.     ('2023-07-12', '62.40', 3),
38.     ('2023-07-13', '62.30', 3),
39.     ('2023-07-14', '62.60', 3);
40. 
41. SELECT tq.of_day AS 'Data da cotação',
42.        tq.price AS 'Preço Atual',
43.        ts.symbol AS 'Nome do Ativo'
44.     FROM tb_Quotes AS tq, tb_Symbols AS ts
45.     WHERE tq.fk_id = ts.id
46.     ORDER BY price DESC;

Code 01

Code 01 is probably familiar to you, since all the material used in it was described in detail in previous articles. Thus, when we run this code, we will get the following result:

Image 01

Good, this image shows exactly what we expected to see, since we created three symbols and added five quotes to each one, giving a total of 15 records. But now let us move on to the main problem. It occurs primarily when we try to delete something from the database. "But why would we start having problems when deleting records from the database? That does not make much sense." In essence, it does not. However, when using related tables, a problem arises that does not exist when using another schema design approach. One of these problems can be identified by running the program shown below:

1. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
2. 
3. SELECT tq.of_day AS 'Data da cotação',
4.        tq.price AS 'Preço Atual',
5.        ts.symbol AS 'Nome do Ativo'
6.     FROM tb_Quotes AS tq, tb_Symbols AS ts
7.     WHERE tq.fk_id = ts.id
8.     ORDER BY price DESC;

Code 02

You may think: "All right, but what is wrong with that? My goal was precisely to delete all PETR4 records." In fact, dear readers, when the first line is executed, SQL will delete the PETR4 records. That is exactly what happens, so when we execute the SELECT command on line 3, we get this result:

Image 02

This image proves that the command on line 01 worked. But do you really know what the command on line 01 did? And moreover: do you really know what was happening inside the database? Many would say: "Of course I know what happened." So, dear readers, I want you to answer the following question: does Code 01 create a relational database or not? If it does, where is this relationship established? If the answer is no, how can we guarantee this relationship? I understand that many people think they know how to work with databases simply because they know a few SQL commands. But everything is not as simple as it seems. Nor is it as difficult as many imagine. In any case, I want you to understand one thing:

Code 01 DOES NOT CREATE A RELATIONAL DATABASE.

"Of course it does. I can see it when I create a column intended to reference data from another table. What do you mean when you say the database is not relational? Are you crazy?" No, I will show you that the database is not relational. Look at the following image.

Image 03

Although we do not see all 30 possible combinations, we can see that there is no reference to the PETR4 symbol. However, the fk_id value equal to 2 is present, but this value is not included in the symbol identifier. This makes the database more prone to data consistency failures over time, and the reason for this will be explained a little later. Now let us understand exactly what the DELETE command does to the database. Since the database is not relational, the two tables are independent of each other, and only a simple reference is maintained between the data.

This scheme has its advantages, although it does not guarantee that we are actually creating a relational database. Now we will look at the real reason. As we know, id 2 is free. To understand this, we only need to look at the tb_Symbols table, as shown below.

Image 04

Knowing this information, we can place another symbol, for example BBDC4, into this free id. This is done as follows:

Image 05

Question: Is there anything wrong with this? In principle, no, because id 2 was essentially free. But this is where the problem arises. When data is added to the tb_Quotes table, invalid values may appear in our database. And it is very likely that we will notice this only after spending quite a lot of time entering data into the database. And the longer we wait, the less likely we are to notice that something is wrong. But suppose that, for some reason, immediately after adding BBDC4 to the tb_Symbols table, we decide to query the database, as shown in the following image.

Image 06

"Wait a minute. Why is that? I just added BBDC4 to the database, and is there already information for it?" Yes, dear readers, this is possible. This is the danger of doing something while thinking that we know what we are doing. Acting naively or with too much confidence often leads to problems like this, which may only become visible much later. Thus, we no longer have a way to verify the reliability of the data stored in the database. To solve this problem, we would have to change the way the database is built so that there is a relationship between the primary key and the foreign key. Thus, the same Code 01 should be executed as shown below:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. INSERT INTO tb_Symbols (id, symbol) VALUES
21.     (2, 'PETR4'),
22.     (1, 'ITUB3'),
23.     (3, 'VALE3');
24. 
25. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
26.     ('2023-07-10', '22.00', 1),
27.     ('2023-07-11', '22.20', 1),
28.     ('2023-07-12', '22.40', 1),
29.     ('2023-07-13', '22.30', 1),
30.     ('2023-07-14', '22.60', 1),
31.     ('2023-07-10', '26.00', 2),
32.     ('2023-07-11', '26.20', 2),
33.     ('2023-07-12', '26.40', 2),
34.     ('2023-07-13', '26.30', 2),
35.     ('2023-07-14', '26.60', 2),
36.     ('2023-07-10', '62.00', 3),
37.     ('2023-07-11', '62.20', 3),
38.     ('2023-07-12', '62.40', 3),
39.     ('2023-07-13', '62.30', 3),
40.     ('2023-07-14', '62.60', 3);
41.     
42. SELECT tq.of_day AS 'Data da cotação',
43.        tq.price AS 'Preço Atual',
44.        ts.symbol AS 'Nome do Ativo'
45.     FROM tb_Quotes AS tq, tb_Symbols AS ts
46.     WHERE tq.fk_id = ts.id
47.     ORDER BY price DESC;

Code 03

Please note that the only difference between the scripts is line 17. This is exactly where we ensure the actual relationship between the tables. "Hmm, but if the solution is that simple, why not create everything as shown in Code 03?" Well, dear reader, the problem is that when we create elements like those shown in Code 03, we end up making it harder to delete records from the database. To separate these issues, let us move on to a new topic.

Deleting records in related tables

To understand why a significant number of users prefer to use Code 01 rather than Code 03 to create databases, let us try to do the same thing as before. In other words, we are going to delete the PETR4 symbol and replace it with the BBDC4 symbol. We can try to do this using the example shown below.

Image 07

Please note that in Image 07 the highlighted point is trying to delete PETR4 from the tb_Symbols table. However, SQL tells us that this will not be possible. This is because the database was created using Code 03. Many people end up getting annoyed with SQL and looking for a solution, and the simplest one is to use Code 01, which sooner or later will still cause problems. Excellent. As seen in Image 07, SQL's response to the query is far from being an error. It indicates that there really is a relationship between the records in the tb_Symbols and tb_Quotes tables, which prevents the accidental deletion of any data.

To understand where the relationship is established, which is necessary in many cases, we will have to refer to the database diagram that we considered in the previous article. But since we only have two tables here, this can be understood easily. So let us get straight to the point.

Image 08

And so that there is no doubt, the code for Image 08 is shown below.

01. DELETE FROM tb_Quotes 
02.     WHERE fk_id = (SELECT ts.id FROM tb_Symbols AS ts WHERE ts.symbol = 'PETR4');
03. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
04. 
05. SELECT tq.of_day AS 'Data da cotação',
06.        tq.price AS 'Preço Atual',
07.        ts.symbol AS 'Nome do Ativo'
08.     FROM tb_Quotes AS tq, tb_Symbols AS ts
09.     WHERE tq.fk_id = ts.id
10.     ORDER BY price DESC;

Code 04

From this perspective, it seems that we are only making things more complicated, but in fact we are making them safer. Note that on line 01 of Code 04, we tell SQL to delete something from the tb_Quotes table. On line 02, we specify what should be deleted. Please note that this should be interpreted as follows: SQL must go to the tbQuotes table and delete all records for the PETR4 symbol, then go to the tb_Symbols table and delete the PETR4 symbol record. From this perspective, it seems exaggerated and looks as if nothing will come of it, but look at the result when we query the tb_Quotes table.

Image 09

"Wow. Did everything go as expected?" Now let us try to add BBDC4 and immediately after that see what is in the database. This can be seen in the following image.

Image 10

It is easy to see that in the marked area of Image 10, the fk_id value does not match BBDC4's id. However, analyzing such data in a large database can be quite confusing. But the point is not to focus on the query itself, but to understand what is inside the database. If we execute a relational query, which is what we do in practice, the result will be as follows:

Image 11

Apparently, nothing is shown here, which in fact would be the goal. The blue area shows the result of executing the SQL query. In the GREEN area we see the command that was executed. Thus, SQL did not find any occurrence of BBDC4's id in the old PETR4 id. In other words, the database remains consistent.

All right, that is great, but now let us talk about something more interesting. To do that, we will move on to a new topic.

Using triggers

What we are about to see should be used only if we are sure of what we are doing. Do not try to use it without first becoming familiar with the material from the previous sections. Otherwise, you will end up in a terrible situation with SQL. So now let us understand what this is about. Suppose there is a very complex database in terms of table structure. And this database uses a very specific relationship between these tables. As already shown in the previous topic, if we try to delete a record by its primary key, which is referenced by another table through a foreign key, we will not be able to do so until we first delete all existing references to that primary key.

The way to perform this task was described in the previous topic. When we have few tables, doing something like this is quite simple and clear, but if we have many tables, the situation becomes more complicated, since a DELETE command must be created for each table.

The idea of a trigger is to simplify such tasks so that only one DELETE command is used instead of several. This seems simple, and it is indeed easy to do, dear readers, but there is a problem. Each SQL implementation handles triggers differently. For example, SQL code intended for use with SQLite may have a different triggering mechanism than similar code in SQL Server or even MySQL. Although everything is based on SQL, the way triggers are handled can differ significantly between implementations. Even two SQLite implementations may handle triggers differently, since SQLite is open source and can therefore be modified for specific purposes.

One way to implement triggers in SQLite is shown below.

Image 12

A similar diagram can be seen at lang_createtrigger. There, in addition to this diagram, you can see a brief explanation of how it works. Understanding this schematic model is not difficult. It also greatly helps in understanding the sequence of commands needed to create a trigger.

"All right, but what is this whole complex system of arrows and text trying to tell us? I understand almost nothing." Don’t worry — this becomes easier with practice. Let us understand this without unnecessary stress. Please note that at the top of the image there is a circle. That is where the code starts. Each command is marked with a circle, and the arrows indicate which command should come next. To avoid making this boring and purely theoretical, let us look at how to create a trigger. Remember Code 03? To add a trigger to that code, we only need to create something like what is shown in Image 12. The result is this code:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TRIGGER tr_DeleteSymbol BEFORE DELETE ON tb_Symbols
13. BEGIN
14.     DELETE FROM tb_Quotes WHERE fk_id = OLD.id;
15. END;
16. 
17. CREATE TABLE IF NOT EXISTS tb_Quotes
18. (
19.     of_day NOT NULL,
20.     price NOT NULL,
21.     fk_id NOT NULL,
22.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
23. );
24. 
25. INSERT INTO tb_Symbols (id, symbol) VALUES
26.     (2, 'PETR4'),
27.     (1, 'ITUB3'),
28.     (3, 'VALE3');
29. 
30. INSERT INTO tb_Quotes (of_day, price, fk_id) VALUES
31.     ('2023-07-10', '22.00', 1),
32.     ('2023-07-11', '22.20', 1),
33.     ('2023-07-12', '22.40', 1),
34.     ('2023-07-13', '22.30', 1),
35.     ('2023-07-14', '22.60', 1),
36.     ('2023-07-10', '26.00', 2),
37.     ('2023-07-11', '26.20', 2),
38.     ('2023-07-12', '26.40', 2),
39.     ('2023-07-13', '26.30', 2),
40.     ('2023-07-14', '26.60', 2),
41.     ('2023-07-10', '62.00', 3),
42.     ('2023-07-11', '62.20', 3),
43.     ('2023-07-12', '62.40', 3),
44.     ('2023-07-13', '62.30', 3),
45.     ('2023-07-14', '62.60', 3);
46.     
47. SELECT tq.of_day AS 'Data da cotação',
48.        tq.price AS 'Preço Atual',
49.        ts.symbol AS 'Nome do Ativo'
50.     FROM tb_Quotes AS tq, tb_Symbols AS ts
51.     WHERE tq.fk_id = ts.id
52.     ORDER BY price DESC;
53. 

Code 05

Now note the difference between Codes 03 and 05. You probably noticed that the difference lies precisely in the trigger that we create. In principle, everything in Code 05 can be understood except one thing. Where did this OLD value come from? And how does this trigger actually work? All right, let us understand it. This OLD is directly related to SQLite. In other words, it depends entirely on the SQLite implementation. Other implementations may use a different name, so for more detailed information you need to consult the documentation.

But OLD means old. "In this case, we are talking about an identifier. But which identifier exactly? We do not pass any parameters to the trigger. How can we refer to something that we do not specify?" Stay calm. We have already said that this whole trigger system is a little confusing. Therefore, you first need to understand what we explained about working with simpler tables and systems. Triggers are the most advanced part of SQL programming. So do not rush.

To understand where this OLD value comes from, we first need to understand how the code works. So, after executing Code 05, we will delete the PETR4 symbol from the database and replace it with the BBDC4 symbol. But we will do this differently than ever before, precisely because we now have a trigger. To make this change, we use the code below.

1. DELETE FROM tb_Symbols WHERE symbol = 'PETR4';
2. INSERT INTO tb_Symbols(id, symbol) VALUES(2, 'BBDC4');
3. 
4. SELECT tq.of_day AS 'Data da cotação',
5.        tq.price AS 'Preço Atual',
6.        ts.symbol AS 'Nome do Ativo'
7.     FROM tb_Quotes AS tq, tb_Symbols AS ts
8.     WHERE tq.fk_id = ts.id
9.     ORDER BY price DESC;

Code 06

Thus, when executing Code 06, we will get the following result:

Image 13

"What? Why? I know you are tricking me. This is a joke." How I wish that were so, dear readers, but it is completely true because a trigger exists. When line 01 of Code 06 is executed, any record in the database that is in any way related to the PETR4 symbol will be deleted. And since the id with value two is now free, we can use the second line of Code 06 to add the BBDC4 symbol with this id value. That is why I insist once again: before trying to use triggers, first try to understand how basic SQL works. Otherwise, you will become completely confused by the code that will be executed.

Inserting data using triggers

So, if what we saw in the previous topic seemed a little difficult to understand, get ready, because now we will see something truly difficult to understand, namely inserting data using triggers. Before we start, we once again remind you, dear readers, that knowledge matures gradually, step by step. It is very important to first try to understand the basics before moving on to more complex matters, for which sufficient prior knowledge is truly necessary. Do not try to use anything until you really understand how everything works.

To understand how to insert data using triggers, we need to start with something simpler. Let us step back a little and first consider some more basic concepts. This is so that you do not begin to go deeper into something that may not yet have been properly explained. Once again, we remind you that everything we will consider here refers to the standard SQLite implementation. If in doubt, consult the documentation of the implementation you are using to understand how to configure what we will see here. So, let us start with the code below:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );

Code 07

I think that at this point everyone here understands what Code 07 does and what database will be created when we begin inserting records into the tables created with Code 07. But perhaps there are still some points that were not properly clarified in the previous topic, namely:

BEFORE CREATING THE DATABASE we need to define which triggers it will contain.

Judging by the information presented, you may have had the impression that triggers can be added to or even removed from a database at any time. But in reality, this is not exactly how it works. When SQL creates the file that will contain the database, everything necessary must be properly defined. In a sense, if this is not provided by the implementation, we will not be able to add or remove database objects from an already created database. An example of an implementation that allows elements to be added or removed is SQL Server. In it, you can add or remove stored logic at any time, since scripts are usually not tied to the database.

But when it comes to triggers, things are usually handled differently. Therefore, before creating random databases, you should try to study everything carefully. This way, you can avoid surprises in the future. Therefore, the next step is to add a trigger that will fire during the process of inserting records into the database. Thus, Code 07 becomes Code 08, which is shown below in full:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;
23. END;

Code 08

Now do not forget to look at Image 12: what is being created here in Code 08. In other words, as far as triggers are concerned, that same image displays the DELETE, INSERT, and UPDATE commands. Once again: you need to understand what is contained in the code.

The purpose of updating Code 07 to generate Code 08 is very simple. When any new symbol is added to the tb_Symbols table, this trigger automatically converts the record to uppercase so that the letters are displayed in uppercase. "Brother, are you kidding?" No; in a database it is usually necessary for data to be checked in some way and, if necessary, corrected.

What we do in Code 08, although it may seem trivial, makes sense in certain scenarios. To check whether Code 08 can achieve this goal, we will use the code below. Let us remind you once again that you can run Code 08 and, after quite a long time, run Code 09, and everything should work perfectly normally.

1. INSERT INTO tb_Symbols (id, symbol)
2.                 VALUES (1, 'vale3'),
3.                        (2, 'PetR4'),
4.                        (3, 'ITUB4');
5. SELECT * FROM tb_Symbols;

Code 09

Please note that we are entering values in both uppercase and lowercase. However, in the database, all records that are added using Code 09 should be written in capital letters. This can be seen when executing line 05, which produces the following:

Image 14

As if by magic, we can observe in the marked area that the values were converted. But before you think that this happens even without using triggers, we must remember that SQL does not change or correct string values. SQL will keep them exactly as they were originally written unless we use a trigger, such as the one created in Code 08, whose purpose is to modify or even update certain records in the database.

Now that we have seen the result, we want to understand how the trigger implemented in Code 08 works. This is because Code 09 does not mention it. Situations like this often make many beginning SQL users uneasy. At first glance, it may seem that the trigger should not fire, since we do not call it directly.

Let us now return to Code 08 to understand how the trigger works. Line 20 should be read exactly as it is written in Code 08. Do not try to interpret anything yet; simply read what is written on line 20. To help you, we will show how this should be done.

If it does not already exist, create a trigger named tr_InsertSymbol that must be executed AFTER data is inserted into the tb_Symbols table.

Please note that this is not difficult. The main thing is to pay attention to what we want to achieve. All right, lines 21 and 23 contain only SQL reserved words that denote the body of the procedure with which we want to work. For more detailed information, let us again refer to Image 12, since what is shown here is only an illustrative example. Now let us deal with line 22. It should be read in the same way as line 20. And again, to help you in this process, you should read it as follows:

Now update the tb_Symbols table so that symbol takes the value upper(NEW.symbol) when NEW.id is equal to id.

And again we have seen something here that depends on SQLite. This happens because the NEW keyword exists only in SQLite. When we talk about deleting records, we use the word "OLD". And now we use the word "NEW". In principle, this seems quite confusing. "And indeed, I am confused, dear author. I just cannot understand when to use NEW and when to use OLD. The reason is that we created a trigger named tr_InsertSymbol. Thus, when we add data to the database, we update the database, which, in principle, makes the values not new but old. This is madness!"

I know, dear readers. In fact, there are some details that at first can make the situation a little confusing, but as we use and study them, they will stop causing confusion and start making sense. Nevertheless, I will try to simplify the situation so that it becomes clear when to use NEW and when to use OLD. At least this is enough to confuse the reader less.

When performing a DELETE operation, the information being deleted ALREADY EXISTS in the database and will cease to exist. Therefore, we use OLD. When performing an INSERT operation, the information DOES NOT EXIST in the database and will be created, so we use the word NEW. The confusion is caused precisely by the fact that the trigger on line 20 of Code 08 indicates that the trigger should be executed AFTER the information is inserted into the database. However, it should be remembered that these data are new and did not previously exist in the database.

However, the situation becomes somewhat more complicated when the trigger is associated with the UPDATE command. In that case, we will have to think a little more broadly. So pay attention. The information that will replace information already existing in the database should be treated as NEW. However, the information that already exists in the database and is to be replaced should be treated as OLD. If you understand this, then you will be able to handle any trigger script that works with a standard SQLite database. We are talking about the standard version of SQLite because an implementation may handle this differently. In that case, try to study its documentation.

Choosing a path

Now that we know how to work with triggers for inserting records into the database while ensuring data integrity, we need to consider another equally important aspect. This is because database integrity is guaranteed only partially. "But why do you say only partially?" The reason lies in the UPDATE command, dear readers. Remember that the guarantee created is based on the INSERT command.

But there is a problem with the UPDATE command that we need to solve. Consider this example: what happens if we want to change something in the database using the UPDATE command? Since SQL does not know which rules to apply, any data entered using the UPDATE command will be accepted by SQL and will replace any previously checked data. To demonstrate this, look at the code below:

1. INSERT INTO tb_Symbols (id, symbol)
2.                 VALUES (1, 'vale3'),
3.                        (2, 'PetR4'),
4.                        (3, 'ITUB4');
5. 
6. UPDATE tb_Symbols SET symbol = 'iTub4' WHERE id = 3;
7.
8. SELECT * FROM tb_Symbols;

Code 10

Code 10 must be executed in a database created using Code 08. This is necessary to obtain the result shown below:

Image 15

In the marked area of the database, we can notice something rather strange. Something that should not be there, precisely because all values should be written in capital letters. However, SQL changed the record because of line 06 of Code 10, and as a result, the integrity of the database was violated. Note that all actions depend on the goal that must be achieved. There is no single formula. First we need to understand the problem, and only then look for ways to solve it. However, all of this must be defined and set up before the database begins receiving sensitive data.

So, suppose the following: once a record has been created, it can no longer be changed. This will allow us to solve the type of problems encountered earlier. However, if we allow the database to be updated, then the data inserted into an existing record must comply with certain rules. In that case, we will have to take another path to solve the problem with the UPDATE command.

But we can also allow the user to change the symbol name. However, by doing so, the user will have the illusion that the name has been changed. Inside the database, however, data will actually be copied with the creation of a new record. The old record belonged to a situation in which the user could not directly access the data. However, the database administrator will be able to restore all the data, since the original record remains untouched. This approach leads us to another solution that will also require implementation. In other words, each case is a separate situation. There is no single solution that fits everything.

So, to better illustrate the situation, let us see how the UPDATE command is handled when it allows the user to change the symbol name, but in such a way that the name ultimately remains within the same rules as when the INSERT command is executed. To do this, we could use a modification of the original script shown below:

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = NEW.symbol WHERE NEW.id = id;
23. END;
24. 
25. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol AFTER UPDATE ON tb_Symbols
26. BEGIN
27.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;    
28. END;

Code 11

"But wait a second. Now you are definitely joking. How can it be that the trigger code for the UPDATE command is almost an exact copy of the trigger for the INSERT command? Are you sure this will work? I have doubts, because if we perform an INSERT operation, the trigger on line 20 will fire. But since we use the UPDATE command on line 22, I assume that the trigger implemented on line 25 will also fire. And when the command on line 27 is executed, SQL will enter a loop. Honestly, I do not think this will work."

Well, if you really observed this execution sequence, dear readers, it means that you still have not understood how SQL works with triggers. But that is all right. Now, if we create a new database using Code 11 and then immediately try to run Code 10 again in this new database, look at what happens:

Image 16

Now let us understand the following: when the user requests the insertion of a record into the tb_Symbols table, SQL may insert the record or may not do so. If it inserts it, SQL will execute the trigger on line 20 of Code 11. This, in turn, will lead to the execution of line 22, which updates the data contained in the database. But line 22 will cause SQL to execute the trigger on line 25. Up to this point you were right; after executing line 27, we will again ask SQL to update the database.

However, this time we forcibly bring the contents of the record to a specific pattern that exactly matches what existed at the moment the record was created using the INSERT command. Thus, we managed to cover both the INSERT command and the UPDATE command. Although this is not the best way to explain it, it was the simplest way I found to explain how we can do this.

And the last thing I want to show is that if we want to prevent some records from being changed by the UPDATE command, a relatively simple way to do this is to use the code below when creating the database.

01. PRAGMA FOREIGN_KEYS = ON;
02. 
03. DROP TABLE IF EXISTS tb_Quotes;
04. DROP TABLE IF EXISTS tb_Symbols;
05. 
06. CREATE TABLE IF NOT EXISTS tb_Symbols
07. (
08.     id PRIMARY KEY,
09.     symbol NOT NULL UNIQUE
10. );
11. 
12. CREATE TABLE IF NOT EXISTS tb_Quotes
13. (
14.     of_day NOT NULL,
15.     price NOT NULL,
16.     fk_id NOT NULL,
17.     FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)
18. );
19. 
20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
21. BEGIN
22.     UPDATE tb_Symbols SET symbol = NEW.symbol WHERE NEW.id = id;
23. END;
24. 
25. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol AFTER UPDATE ON tb_Symbols
26. BEGIN
27.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;    
28. END;
29. 
30. CREATE TRIGGER IF NOT EXISTS tr_UpdateSymbol_Before BEFORE UPDATE ON tb_Symbols
31.     WHEN upper(NEW.symbol) != upper(OLD.symbol)
32. BEGIN
33.     SELECT RAISE(ABORT, 'ERROR: Unable to update this record...');
34. END;

Code 12

Here everything is a little different. But if we again use Code 10 on a database created with Code 12, we will see the following result:

Image 17

Now I want you to look carefully at Image 17 and try to understand exactly what Code 12 created so that, when Code 10 was executed, we obtained this result. Note that line 01 of Code 10 will execute successfully, thereby creating records in the database. However, when line 06 is executed, an error will occur, as shown in Image 17. This is where I want you to pay attention to the data shown in Image 17. Look at line 03 of the message. Now go back to Code 12, and you will see that it is exactly the same message as on line 33. How is this possible?

When trying to insert some data into the table, we will execute the trigger on line 20 immediately after inserting the record. This will cause line 22 to issue a command to update the record. All right, but before updating, the trigger on line 30 will be executed, and only after that will we execute the trigger on line 25. Please note that we have changed the way Code 11 works. This is exactly what the check on line 31 is for. Note that this check determines whether the record we are trying to update differs from or matches the one that already exists in the database. By performing this check, we ensure that the record will not be changed. This is because if the record differs, the trigger will fire, and this will lead to the execution of line 33. When SQL executes line 33, a message will be sent to whoever requested the update, explaining that the operation cannot be performed for some reason.

In some cases, this type of schema design can be useful. Try to study it, because this information may save you from future headaches.

Final thoughts

What I have shown here is just the foundation for many other SQL concepts you still need to study. I know that many people frown when SQL comes up, saying that studying and understanding SQL is foolish, that it is much easier to create files themselves to store data that could have been placed in a database, and so on. But I want to remind you that SQL is one of the most pleasant tools to use, and that it can help you with tasks that would otherwise require much more effort.

Many things that I see people doing with Python or even Excel could be solved much more easily if SQL were used. But because SQL is something that requires study and attention, most people prefer to rack their brains trying to combine data and information using classic programming. However, I assure you: learning SQL is truly worth it. Especially when you begin to understand how data can be related to each other.

Thus, we conclude our discussion of SQL. And you will need to continue the learning process begun here. In the next article, we will see how everything shown is applied in practice when SQL is combined with MQL5. You will see that, up to this point, we have only slightly scratched the surface of what we can actually do in MetaTrader 5.

File	Description
Experts\Expert Advisor.mq5	Demonstrates the interaction between Chart Trade and the Expert Advisor (Mouse Study is required for interaction).
Indicators\Chart Trade.mq5	Creates a window for configuring the order to be sent (Mouse Study is required for interaction).
Indicators\Market Replay.mq5	Creates controls for interacting with the replication/simulation service (Mouse Study is required for interaction).
Indicators\Mouse Study.mq5	Provides interaction between graphical controls and the user (required both for the replication system and for the real market).
Services\Market Replay.mq5	Creates and maintains the market replication/simulation service (the main file of the entire system).
Server.cpp (VS C++)	Creates and maintains a socket-based server developed in C++ (mini-chat version).
Code in Python\Server.py	Creates and maintains a Python socket-based link between MetaTrader 5 and Excel.
Indicators\Mini Chat.mq5	Allows a mini-chat to be implemented through an indicator (server use is required for operation).
Experts\Mini Chat.mq5	Allows a mini-chat to be implemented in an Expert Advisor (server use is required for operation).
Scripts\SQLite.mq5	Demonstrates the use of an SQL script with MQL5.
Files\Script 01.sql	Demonstrates the creation of a simple table with a foreign key.
Files\Script 02.sql	Shows how to add values to a table.