市场模拟（第 24 部分）：SQL 入门（七）
36
0
概述
在上一篇文章“市场模拟（第 23 部分）：SQL 入门（六）”中，我们解释了关于 SQL 必须理解的几个重要点。这为我们提供了使用数据库所需的最基本知识。因此，鉴于我们仍有大量内容要讨论，且话题相当复杂，我们将不再浪费时间进行介绍。让我们直奔主题。
理解问题
数据库本质上就是存放在文件中的一组记录。但是为了理解我们想要解释的内容，让我们使用以下代码：
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;
代码 01
你很可能对代码 01 很熟悉，因为前文已经详细讲解过其中涉及的所有内容。因此，运行这段代码后，我们将得到以下结果：
图 01
很好，这张图片显示了我们预期看到的内容，因为我们创建了三个交易品种，并为每个交易品种添加了五笔报价，总共得到了 15 条记录。但现在让我们转向主要问题。它主要发生在我们试图从数据库中删除某些内容时。“但是，为什么从数据库中删除记录时会开始出现问题呢？这不太合理。”从本质上讲，不应如此。但是，在使用关联表时，会出现一个问题，而采用其他数据库设计方式时，这个问题通常不会出现。运行以下程序可以发现其中一个问题：
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;
代码 02
你可能会想：“好吧，但这有什么问题呢？我的目标正是删除所有 PETR4 记录。”事实上，亲爱的读者们，当执行第一行代码时，SQL 将删除 PETR4 记录。实际情况正是如此，因此，当我们在第 3 行执行 SELECT 命令时，会得到以下结果：
图 02
这张图片证明第 01 行的命令是有效的。但你真的知道第 01 行的命令做了什么吗？而且，你真的了解数据库内部发生了什么吗？很多人会说：“我当然知道发生了什么。”所以，亲爱的读者们，我想请你们回答以下问题：代码 01 是否创建了一个关系型数据库？如果是，那么这种关系是如何建立的？如果答案是否定的，我们如何保证这种关系？我明白，许多人认为自己知道如何使用数据库，仅仅是因为他们掌握了一些 SQL 命令。但一切并不像看上去那么简单。它也不像许多人想象的那么难。无论如何，我想让你明白一件事：
代码 01 不创建关系数据库。
图 03
虽然我们看不到全部 30 种可能的组合，但我们可以看到其中没有 PETR4 交易品种。然而，存在一个等于 2 的 fk_id 值，但该值并未包含在交易品种标识符中。这会使数据库随着时间的推移更容易出现数据一致性故障，稍后将对此进行解释。现在，让我们来准确理解 DELETE 命令对数据库的作用。由于数据库不是关系型的，这两个表是独立的，数据之间仅通过简单的引用进行连接。
这种设计方式有其优点，尽管它并不能保证我们实际上是在创建一个关系型数据库。现在我们将探讨真正的原因。我们知道，id 2 是空闲的。要理解这一点，我们只需查看 tb_Symbols 表，如下所示。
图 04
知道了这个信息后，我们可以将另一个交易品种（例如 BBDC4）写入这个空闲的 id 中。如下所示操作：
图 05
问题：这样做有什么问题吗？原则上没有，因为 id 2 本质上是空闲的。但问题就出在这里。当向 tb_Quotes 表中添加数据时，我们的数据库中可能会出现无效值。而且，我们很可能只有在花费了大量时间将数据输入数据库后，才会注意到这一点。我们等待的时间越长，就越不可能注意到出了问题。但是，假设出于某种原因，在将 BBDC4 插入到 tb_Symbols 表之后，我们立即决定查询数据库，如下图所示。
图 06
“等一下。为什么会这样？我刚把 BBDC4 加入数据库，怎么数据库里就已经有它的信息了？是的，亲爱的读者们，这是有可能的。这就是我们在自认为清楚自己在做什么的情况下行动的危险之处。行事天真或过于自信往往会导致此类问题，这些问题可能很久以后才会显现出来。因此，我们无法再验证数据库中存储的数据的可靠性。为了解决这个问题，我们必须改变数据库的构建方式，使主键和外键之间建立关系。因此，应执行如下所示的相同代码 01：
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;
代码 03
请注意，这两个脚本之间的唯一区别在于第 17 行。这正是我们确保表之间实际关联性的地方。“嗯，但如果解决方案这么简单，为什么不按照代码 03 所示来创建所有内容呢？”亲爱的读者，问题在于，当我们创建如代码03所示的元素时，会使得从数据库中删除记录变得更加困难。为了区分这些问题，让我们继续讨论一个新的话题。
删除相关表中的记录
为了理解为何众多用户更倾向于使用代码 01 而非代码 03 来创建数据库，让我们尝试像之前一样进行操作。换句话说，我们将删除 PETR4 交易品种，并用 BBDC4 交易品种替换它。我们可以尝试使用下面所示的示例来完成此操作。
图 07
请注意，在图 07 中，高亮部分显示的是一条试图从 tbSymbols 表中删除 PETR4 的操作但是，SQL 告诉我们这是不可能的。这是因为该数据库是使用代码 03 创建的。许多人最终对 SQL 感到厌烦，开始寻找解决方案，而最简单的解决方案就是使用代码 01，但迟早它仍会引发问题。非常好，如图 07 所示，SQL 给出的反馈其实并不应被视为错误。这表明 tb_Symbols 表和 tb_Quotes 表中的记录之间确实存在关联，从而防止意外删除任何数据。
为了理解关系是在何处建立的（这在许多情况下是必要的），我们必须参考上一篇文章中提到的数据库图表。但由于这里只有两张表，所以很容易理解。那么我们就直奔主题吧。
图 08
为了避免任何疑问，图 08 的代码如下所示。
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;
代码 04
从这个角度来看，我们似乎只是在把事情变得更加复杂，但实际上我们是在让事情变得更安全。请注意，在代码 04 的第 01 行，我们告诉 SQL 从 tb_Quotes 表中删除某些内容。在第 02 行，我们指定要删除的内容。请注意，这应作如下解释： SQL 必须进入 tb_Quotes 表并删除 PETR4 交易品种的所有记录，然后进入 tb_Symbols 表并删除 PETR4 交易品种记录。从这个角度来看，它似乎有些夸张，看起来好像不会有任何结果，但看看我们查询 tb_Quotes 表时的结果吧。
图 09
“哇。一切都按预期进行吗？”现在让我们尝试添加 BBDC4，然后立即查看数据库中的内容。这可以从下图看出。
图 10
很容易看出，在图 10 的标记区域中，fk_id 值与 BBDC4 的 id 不匹配。然而，在大型数据库中分析此类数据可能会让人感到非常困惑。但关键不在于关注查询本身，而在于理解数据库中的内容。如果我们执行一个关系查询（这是我们在实际操作中会做的），结果将如下：
图 11
显然，这里什么也没有显示，而这实际上正是我们的目标。蓝色区域显示的是执行 SQL 查询的结果。在绿色区域，我们可以看到已执行的命令。因此，SQL 没有在旧的 PETR4 id 中找到 BBDC4 的 id。换句话说，数据库保持了一致性。
好的，这很棒，但现在让我们来谈谈更有趣的事情吧。为此，我们将继续讨论一个新的话题。
使用触发器
我们即将看到的内容，只有在确信自己在做什么的情况下才能使用。在未熟悉前几节内容之前，请勿尝试使用。否则，你使用 SQL 时会陷入非常糟糕的境地。现在，让我们来了解一下这究竟是怎么回事。假设有一个在表结构方面非常复杂的数据库。这个数据库在这些表之间使用了非常特定的关系。如上一节所述，如果我们试图通过主键删除一条记录，而该主键通过外键被另一张表引用，那么在删除所有对该主键的现有引用之前，我们将无法执行此操作。
执行此任务的方法已在上一主题中进行了描述。当我们只有几个表时，执行这样的操作相当简单明了，但如果我们有很多表，情况就会变得更为复杂，因为必须为每个表创建一个 DELETE 命令。
触发器的设计理念是简化此类任务，以便仅使用一个 DELETE 命令，而不是多个。这看起来很简单，亲爱的读者，确实也很容易做到，但这里有个问题。每种 SQL 实现处理触发器的方式都不同。例如，用于 SQLite 的 SQL 代码可能具有与 SQL Server 甚至 MySQL 中的类似代码不同的触发机制。虽然一切都基于 SQL，但触发器的处理方式在不同的实现中可能存在显著差异。即使是两个不同的 SQLite 实现，对触发器的处理方式也可能不同，因为 SQLite 是开源的，因此可以根据特定目的进行修改。
下面展示了在 SQLite 中实现触发器的一种方法。
图 12
类似的图表可以在 lang_createtrigger 上看到。在那里，除了这个图表，你还可以看到关于它如何工作的简要说明。理解这个示意图模型并不难。它还有助于理解创建触发器所需的命令序列。
“好吧，但是这错综复杂的箭头和文字到底想表达什么呢？我几乎什么都不懂。”别担心 — 多练习就会变得容易。让我们在理解这一点时，不要施加不必要的压力。请注意，图片的顶部有一个圆圈。代码就是从这里开始的。每个命令都用一个圆圈标记，箭头指示接下来应该执行哪个命令。为了避免让这个话题变得枯燥且纯理论化，让我们来看看如何创建一个触发器。还记得代码 03 吗？要为该代码添加一个触发器，我们只需创建如图 12 所示的内容。结果代码如下：
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.
代码 05
请注意代码 03 和 05 之间的区别。你可能已经注意到，区别恰恰在于我们创建的触发器。原则上，代码 05 中的所有内容都可以理解，除了一件事。这个 OLD 是从哪里来的？这个触发器究竟是如何工作的？好的，让我们来理解一下。这个 OLD 与 SQLite 直接相关。换句话说，这完全取决于 SQLite 的实现方式。其他实现可能会使用不同的名称，因此如需更详细的信息，请查阅相关文档。
但 OLD 就是旧的意思。“在这种情况下，我们讨论的是一个标识符。但究竟是哪个标识符呢？我们不向触发器传递任何参数。我们怎能引用我们没有明确指定的事物呢？”保持冷静。我们之前已经说过，整个触发系统有点令人困惑。因此，你首先需要理解我们之前关于如何处理更简单的表格和系统所做的解释。触发器是 SQL 编程中最先进的部分。所以不要着急。
要了解这个 OLD 来自哪里，我们首先需要了解代码是如何工作的。因此，在执行代码 05 后，我们将从数据库中删除 PETR4 交易品种，并将其替换为 BBDC4 交易品种。但我们将采取与以往不同的方式，正是因为我们现在有了触发器。为了实现这一更改，我们使用以下代码。
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;
代码 06
因此，执行代码 06 时，我们将得到以下结果：
图13
“什么？为什么呢？我知道你在骗我。这是个笑话。”亲爱的读者们，我多么希望情况真是如此，但这完全是事实，因为存在触发器。当执行代码 06 的第 01 行时，数据库中与 PETR4 交易品种有任何关联的任何记录都将被删除。由于值为 2 的 id 现在空闲，我们可以使用代码 06 的第二行来插入具有此 id 值的 BBDC4 交易品种。因此，我再次强调：在尝试使用触发器之前，请先尝试理解基本的 SQL 工作原理。否则，你会对即将执行的代码感到完全困惑。
使用触发器插入数据
所以，如果我们在上一个主题中看到的内容看起来有点难以理解，那么请做好准备，因为接下来我们将看到一些真正难以理解的内容，即使用触发器插入数据。在我们开始之前，亲爱的读者们，我们再次提醒你们，知识是逐步积累、循序渐进的。在着手处理更复杂的问题之前，先尝试理解基础知识是非常重要的，因为处理这些问题确实需要足够的先验知识。在你真正了解所有东西是如何运作之前，不要尝试使用任何东西。
要理解如何使用触发器插入数据，我们需要从更简单的内容开始。让我们先退一步，先来考虑一些更基本的概念。这是为了防止你深入探讨一些可能尚未得到恰当解释的内容。我们再次提醒您，此处我们考虑的所有内容均指标准 SQLite 实现。如有疑问，请查阅您正在使用的实现文档，以了解如何配置此处将介绍的内容。那么，让我们从下面的代码开始：
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. );
代码 07
我认为此时此刻，这时大家应该都已经明白了代码 07 的作用，以及当我们开始向使用代码 07 创建的表中插入记录时将会创建什么样的数据库。但或许在上一话题中仍有一些观点未得到恰当澄清，具体来说：
在创建数据库之前，我们需要定义它将包含哪些触发器。
根据所提供的信息，你可能会认为触发器可以随时添加到数据库中，甚至可以从数据库中删除。但实际上，事情并非完全如此。当SQL创建包含数据库的文件时，必须正确定义所有必要的内容。从某种意义上说，如果实现中未提供此功能，我们将无法在已创建的数据库中添加或删除程序逻辑。SQL Server 就是一个允许添加或删除元素的实现示例。在此，您可以随时添加或删除已存储的逻辑，因为脚本通常不与数据库绑定。
但对于触发器，处理方式通常有所不同。因此，在随意创建数据库之前，你应该仔细研究所有相关内容。这样可以避免将来出现意外情况。因此，下一步是添加一个触发器，该触发器将在向数据库插入记录的过程中触发。因此，代码 07 变为代码 08，完整代码如下所示：
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;
代码 08
现在不要忘记看一下图 12：代码 08 中正在创建什么。换句话说，就触发器而言，同一幅图像显示了 DELETE、INSERT 和 UPDATE 命令。再次强调：你需要理解代码中包含的内容。
将代码 07 更新为代码 08 的目的非常简单。当向 tb_Symbols 表中添加任何新交易品种时，此触发器会自动将记录转换为大写，以便字母以大写形式显示。“你这是在开玩笑吗？”不；在数据库中，通常需要以某种方式检查数据，并在必要时进行更正。
我们在代码 08 中所做的工作，虽然看似琐碎，但在某些特定场景下却意义重大。为了检验代码 08 是否能够实现这一目标，我们将使用以下代码。我们再次提醒您，您可以运行代码 08，过一段时间后再运行代码 09，一切都应该能正常运行。
1. INSERT INTO tb_Symbols (id, symbol) 2. VALUES (1, 'vale3'), 3. (2, 'PetR4'), 4. (3, 'ITUB4'); 5. SELECT * FROM tb_Symbols;
代码 09
请注意，我们输入的值既包含大写字母也包含小写字母。但是，在数据库中，所有使用代码 09 插入的记录都应该用大写字母书写。执行第 05 行代码即可看出这一点，该代码会产生以下结果：
图14
就像施了魔法一样，我们可以在标记区域观察到数值已经转换。但在你认为即使不使用触发器也会发生这种情况之前，我们必须记住，SQL 不会更改或纠正字符串值。除非我们使用触发器（例如在代码 08 中创建的触发器），否则 SQL 将保持它们最初编写时的样子，触发器的目的是修改甚至更新数据库中的某些记录。
既然我们已经看到了结果，我们想要了解代码 08 中实现的触发器是如何工作的。这是因为代码 09 没有提及这一点。这种情况常常会让许多 SQL 初学者感到不安。乍一看，似乎触发器不应该被触发，因为我们没有直接调用它。
现在让我们回到代码 08，了解触发器是如何工作的。第 20 行应与代码 08 中的内容完全一致。先不要尝试去解读任何内容；只需阅读第 20 行所写的内容。为了帮助您，我们将演示如何完成这项任务。
如果该触发器尚不存在，则创建一个名为 tr_InsertSymbol 的触发器，该触发器必须在数据插入到 tb_Symbols 表之后执行。
现在更新 tb_Symbols 表，以便当 NEW.id 等于 id 时，symbol 取 upper(NEW.symbol) 的值。
我们再次看到，这里存在一些依赖于 SQLite 的问题。这是因为 NEW 关键字仅存在于 SQLite 中。当我们谈到删除记录时，我们会使用 “OLD” 这个词。现在我们使用 “NEW” 这个词。从理论上讲，这似乎相当令人困惑。“的确，亲爱的作者，我感到困惑。我就是搞不懂什么时候该用 NEW，什么时候该用 OLD。原因是我们创建了一个名为 tr_InsertSymbol 的触发器。因此，当我们向数据库插入数据时，我们更新了数据库，这在原则上使值不再是新的而是旧的。这简直是疯了！”
我理解，亲爱的读者们。事实上，有些细节初看可能会让人感到有些困惑，但随着我们使用和研究它们，它们将不再令人困惑，而是开始变得合情合理。不过，我会尽量简化情况，以便清楚地说明何时使用 NEW，何时使用 OLD。至少这足以让读者不那么困惑。
执行 DELETE 操作时，被删除的信息已存在于数据库中，删除后将不再存在。因此，我们使用 OLD。执行 INSERT 操作时，数据库中不存在该信息，需要创建新信息，因此我们使用 NEW 这个词。造成混淆的原因正是代码 08 第 20 行的触发器指示，该触发器应在信息插入数据库后执行。然而，我们应当记住，这些数据是新的，之前并不存在于数据库中。
但是，当触发器与 UPDATE 命令相关联时，情况就变得稍微复杂一些。在这种情况下，我们不得不放宽思路，从更广的角度去思考。所以请注意，将替换数据库中已有信息的信息应视为 NEW。但是，数据库中已存在且将被替换的信息应视为 OLD。如果你理解了这一点，那么你将能够处理任何适用于标准 SQLite 数据库的触发器脚本。我们这里讨论的是 SQLite 的标准版本，因为不同的实现可能会有不同的处理方式。如果是那样的话，试着研究一下它的文档。
选择道路
既然我们已经知道如何在确保数据完整性的同时，利用触发器将记录插入数据库，那么我们需要考虑另一个同样重要的方面。因为这样做只能部分保证数据库完整性。“但你为什么说只是部分地呢？”亲爱的读者们，原因就在于 UPDATE 命令。请记住，此安全措施基于 INSERT 语句。
但是 UPDATE 命令存在一个问题，我们需要解决。考虑以下示例：如果我们想使用 UPDATE 命令更改数据库中的某些内容，会发生什么情况？由于 SQL 不知道应应用哪些规则，因此使用 UPDATE 命令输入的任何数据都会被 SQL 接受，并会替换之前已检查过的任何数据。为了演示这一点，请看下面的代码：
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;
代码 10
必须在使用代码 08 创建的数据库中执行代码 10。这对于获得下图所示的结果是必要的：
图 15
在数据库的标记区域，我们可以注意到一些相当奇怪的情况。不应该存在的东西，正是因为所有值都应该用大写字母书写。但是，由于代码 10 的第 06 行，SQL 更改了记录，结果破坏了数据库的完整性。请注意，所有行动都取决于必须实现的目标。没有固定的公式。首先，我们需要理解问题，然后才能寻找解决办法。然而，在数据库开始接收敏感数据之前，必须先定义并设置好所有相关内容。
假设有以下情况：一旦创建了记录，就不能再更改了。这将使我们能够解决之前遇到的那类问题。然而，如果我们允许更新数据库，那么插入到现有记录中的数据必须符合某些规则。在这种情况下，我们必须另辟蹊径，通过 UPDATE 命令来解决该问题。
但我们也可以允许用户更改交易品种名称。但是这样做，用户会产生名称已更改的错觉。然而，在数据库内部，创建新记录时，数据实际上会被复制。旧记录属于用户无法直接访问数据的情况。然而，数据库管理员将能够恢复所有数据，因为原始记录保持不变。这种方法引领我们找到了另一个同样需要实现的解决方案。换句话说，每个案例都是一个独立的情况。没有一种解决方案能够适用于所有情况。
为了更好地说明这种情况，让我们来看一下当 UPDATE 命令允许用户更改交易品种名称，但更改后的名称最终仍与执行 INSERT 命令时遵循的规则相同时，该命令是如何处理的。为此，我们可以使用下面所示的原始脚本的修改版本：
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;
代码 11
“但是等一下。你肯定是在开玩笑吧。为什么 UPDATE 命令的触发代码几乎与 INSERT 命令的触发代码完全相同？你确定这样行得通吗？我对此表示怀疑，因为如果我们执行 INSERT 操作，第 20 行的触发器将会触发。但是由于我们在第 22 行使用了 UPDATE 命令，我假设在第 25 行实现的触发器也会被触发。当执行第 27 行的命令时，SQL 将进入循环。说实话，我觉得这行不通。”
亲爱的读者，如果你真的观察到了这个执行顺序，那就说明你还没有理解 SQL 是如何与触发器一起工作的。但这样也没关系。现在，如果我们使用代码 11 创建一个新数据库，然后立即尝试在这个新数据库中再次运行代码 10，看看会发生什么：
图 16
现在让我们了解一下：当用户请求将一条记录插入到 tb_Symbols 表中时，SQL 可能会插入该记录，也可能不会。如果插入成功，SQL 将执行代码 11 第 20 行的触发器。这反过来又会导致第 22 行运行，从而更新数据库中包含的数据。但是第 22 行会导致 SQL 执行第 25 行的触发器。到目前为止，你是对的；执行第 27 行后，我们将再次要求 SQL 更新数据库。
然而，这次我们强制将记录的内容调整为一种特定模式，该模式与使用 INSERT 命令创建记录时存在的模式完全匹配。因此，我们成功地涵盖了 INSERT 命令和 UPDATE 命令。虽然这不是最佳的解释方式，但这是我找到的阐述我们如何做到这一点的最简单方法。
最后我想展示的是，如果我们想防止某些记录被 UPDATE 命令更改，一个相对简单的方法是在创建数据库时使用以下代码。
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;
代码 12
这里的一切都有些不同。但是，如果我们在用代码 12 创建的数据库上再次使用代码 10，我们将看到以下结果：
图 17
现在，请你们仔细观察图 17，并尝试理解代码 12 究竟创建了什么，以至于当代码 10 执行时，我们得到了这个结果。请注意，代码 10 的第 01 行将成功执行，从而在数据库中创建记录。但是，当执行第 06 行时，将会发生错误，如图 17 所示。这就是我要你们注意图 17 中显示的数据的地方。请查看消息的第 3 行。现在回到代码 12，你会发现它与第 33 行的消息完全相同。这怎么可能？
在尝试向表中插入一些数据时，我们将在插入记录后立即执行第 20 行的触发器。这导致第 22 行发出更新记录的命令。好的，但在更新之前，会先执行第 30 行的触发器，之后才会执行第 25 行的触发器。请注意，我们已更改了代码 11 的工作方式。这正是第 31 行检查的目的所在。请注意，此检查旨在确定我们尝试更新的记录与数据库中已存在的记录是存在差异还是完全匹配。通过执行此检查，我们确保记录不会被更改。这是因为如果记录不同，触发器就会触发，从而导致第 33 行代码运行。当 SQL 执行第 33 行时，会向请求更新的用户发送一条消息，说明由于某种原因无法执行该操作。
在某些情况下，这种模式设计可能很有用。试着好好研究一下，因为这些信息或许能帮你避免以后的麻烦。
最后的想法
我在这里所展示的只是你仍需学习的许多其他 SQL 概念的基础。我知道，每当提到 SQL 时，很多人都会皱起眉头，说学习和理解 SQL 是愚蠢的，他们认为，自己创建文件来存储本可以放在数据库中的数据要容易得多，诸如此类。但我想提醒你，SQL 是最好用的工具之一，它能帮助你完成原本需要付出更多努力才能完成的任务。
我看到很多人用 Python 甚至 Excel 做的事情，如果用 SQL 来做，其实会容易得多。但由于 SQL 需要学习和关注，大多数人更倾向于绞尽脑汁地使用经典编程来组合数据和信息。但我可以向你保证：学习 SQL 绝对值得。尤其是当你开始理解数据之间是如何相互关联的时候。
至此，我们对 SQL 的讨论就结束了。你需要继续进行从这里开始的学习过程。在下一篇文章中，我们将探讨当 SQL 与 MQL5 结合使用时，如何将文中所示内容应用于实践。你会发现，到目前为止，我们仅仅触及了 MetaTrader 5 功能的冰山一角。| 文件 | 描述 |
|---|---|
| Experts\Expert Advisor.mq5 | 演示 Chart Trade 与 EA 之间的交互（交互需要 Mouse Study）。 |
| Indicators\Chart Trade.mq5 | 创建一个窗口，用于配置要发送的订单（需要 Mouse Study 才能进行交互） |
| Indicators\Market Replay.mq5 | 创建用于与市场回放/模拟服务交互的控件（交互需要 Mouse Study）。 |
| Indicators\Mouse Study.mq5 | 提供图形控件与用户之间的交互（市场回放系统和真实市场都需要）。 |
| Services\Market Replay.mq5 | 创建并维护市场回放/模拟服务，这是整个系统的主要文件。 |
| Server.cpp（VS C++） | 创建并维护一个用 C++ 开发的套接字服务器（迷你聊天版本）。 |
| Code in Python\Server.py | 在 MetaTrader 5 和 Excel 之间创建并维护 Python 套接字连接。 |
| Indicators\Mini Chat.mq5 | 允许通过指标实现迷你聊天（操作需要服务器）。 |
| Experts\Mini Chat.mq5 | 允许在 EA 中实现迷你聊天功能（运行需要服务器支持）。 |
| Scripts\SQLite.mq5 | 演示如何从 MQL5 运行 SQL 脚本。 |
| Files\Script 01.sql | 演示如何创建带有外键的简单表。 |
| Files\Script 02.sql | 演示如何向表中插入值。 |
本文由MetaQuotes Ltd译自葡萄牙语
原文地址： https://www.mql5.com/pt/articles/13059
注意: MetaQuotes Ltd.将保留所有关于这些材料的权利。全部或部分复制或者转载这些材料将被禁止。
本文由网站的一位用户撰写，反映了他们的个人观点。MetaQuotes Ltd 不对所提供信息的准确性负责，也不对因使用所述解决方案、策略或建议而产生的任何后果负责。
您应该了解的MQL5向导技巧（第七十六部分）：利用AO形态与包络通道进行监督学习建模
新手在交易中的10个基本错误
从基础到中级：函数指针
