sql 触发器未触发_SQL触发器–综合指南

柔光的暖阳◎ 2023-02-28 06:11 88阅读 0赞

sql 触发器未触发

Hey, folks! In this article, we will be focusing on **SQL Triggers** in detail.

嘿伙计！ 在本文中，我们将重点关注**SQL触发器** 。

## 什么是SQL触发器？ **(****What is a SQL Trigger?****)** ##

`SQL Trigger` is a **stored procedure/function** that gets executed on the encounter of certain actions in the database.

`SQL Trigger`是一个**存储过程/函数** ，在遇到数据库中的某些操作时执行。

Triggers can be considered as a piece of code or a function that gets triggered or executed upon certain events occuring in the database values.

触发器可以被视为一段代码或一个函数，该函数或函数可以在数据库值中发生某些事件时触发或执行。

--------------------

## 需要SQL触发器 **(****Need of SQL Trigger****)** ##

Let us understand the Need of a SQL Trigger with the help of an example.

让我们借助示例了解SQL触发器的需求。

Consider School/College management. Usually, whenever the teachers arrange any kind of Parent-Teachers meet, they send an email to the registered email address of the parents from the database. It is a tedious process to send invitation emails to all the candidates.

考虑学校/学院的管理。 通常，每当老师安排任何形式的家长教师见面时，他们都会从数据库中发送电子邮件到家长的注册电子邮件地址。 向所有候选人发送邀请电子邮件是一个繁琐的过程。

Now, if we replace the entire process with Triggers, that is, we write a stored procedure, it will send a mail on the entry of a new record in the database upon some specific event.

现在，如果我们将整个过程替换为Triggers，也就是说，我们编写了一个存储过程，则它将在某个特定事件发生时在数据库中新记录的条目上发送邮件。

This **saves a lot of time** and leads to **reusability** of the code.

这样**可以节省大量时间，**并导致代码的**可重用性** 。

--------------------

## SQL触发器的语法 **(****Syntax of SQL Trigger****)** ##

As mentioned above, SQL Trigger is a sequence of events that leads to an definite output value depending upon the pre-defined action mentioned.

如上所述，SQL触发器是一系列事件，这些事件取决于所提到的预定义动作导致确定的输出值。

Create Trigger Trigger-Name
    (Before | After)  [ Insert | Update | Delete]
    on [Table_Name]
    [ for each row | for each column ]
    [Trigger-body ]

*  `Create Trigger`: This statement creates a Trigger for an action to be triggered at specific interval of time.
    
    `Create Trigger` ：此语句为要在特定时间间隔触发的动作创建触发器。
 *  `Before`: If we mentioned ‘before’ in the source code, the Trigger executes itself and updates the values before performing the DML operations specified.
    
    `Before` ：如果我们在源代码中提到“之前”，则触发器将执行自身并更新值，然后执行指定的DML操作。
 *  `After`: If we mentioned ‘before’ in the source code, the Trigger executes itself and updates the values after performing the DML operations specified.
    
    `After` ：如果我们在源代码中提到“之前”，则触发器将执行自身并在执行指定的DML操作后更新值。
 *  `[ Insert | Update | Delete ]`: These are the DML operations that can be used alongside the Trigger.
    
    `[ Insert | Update | Delete ]` `[ Insert | Update | Delete ]` ：这些是可以与触发器一起使用的DML操作。
 *  `[ for each row | for each column ]`: The triggers executes itself either row-wise or column-wise.
    
    `[ for each row | for each column ]` `[ for each row | for each column ]` ：触发器将自己按行或按列执行。
 *  `Trigger-body`: The code to be executed of the trigger is mentioned within this block.
    
    `Trigger-body` ：在此块中提到了要执行触发器的代码。

--------------------

## 通过示例实现SQL触发器 **(****Implementing SQL Trigger through examples****)** ##

Having understood the Working and Syntax of SQL Triggers, let us now dive into the implementation of the same in a stepwise manner.

了解了SQL触发器的工作原理和语法之后，现在让我们逐步研究一下SQL触发器的实现。

Initially, we create a SQL table using [SQL Create query ][SQL Create query]with the columns as follows:

最初，我们使用带有列的[SQL Create查询][SQL Create query]创建一个SQL表，如下所示：

*  item\_id
    
    item\_id
 *  Price(per item)
    
    价格（每件）
 *  Quantity(per item)
    
    数量（每件）
 *  Total\_Cost
    
    总计花费

create table Info(item_id integer, Price integer, Quantity integer, Total_Cost integer);

Now, we create a SQL Trigger for the Table — ‘Info’ created above.

现在，我们为表创建一个SQL触发器-上面创建的“信息”。

CREATE TRIGGER Get_value
    Before INSERT
    ON Info
    FOR EACH ROW
    SET new.Total_Cost = new.Price*new.Quantity;

We have created a Trigger ‘Get\_value’ as a **BEFORE INSERT Trigger** i.e. the Trigger would execute itself and update the data values before the data gets saved to the database.

我们已经创建了一个触发器“ Get\_value”作为**INSERT INSERT触发器，**即该触发器将执行自身并在将数据保存到数据库之前更新数据值。

After the creation of the Trigger, we perform DML – [INSERT operation][] to insert the data into the table.

创建触发器后，我们执行DML- [INSERT操作][INSERT operation]以将数据插入表中。

Note: As we want to calculate the Total\_Cost, we have entered zero in its place during INSERT query execution.

注意：由于我们要计算Total\_Cost，因此在执行INSERT查询时已在其位置输入了零。

Having inserted the records, the Trigger performs the execution of its body. And replaces the zeros of Total\_cst column by their calculated values.

插入记录后，触发器将执行其主体。 并用其计算值替换Total\_cst列的零。

insert into Info(item_id, Price,Quantity,Total_Cost) values(1, 150,30,0);
    insert into Info(item_id, Price,Quantity,Total_Cost) values(2, 100,23,0);
    insert into Info(item_id, Price,Quantity,Total_Cost) values(3, 250,5,0);

Finally, we display the updated data values using [SQL SELECT][] command.

最后，我们使用[SQL SELECT][]命令显示更新的数据值。

select * from Info;

**Output:**

**输出：**

![SQL Trigger Before INSERT Query][]

**SQL Trigger Before INSERT Query** **INSERT查询之前SQL触发器**

--------------------

## SQL触发器的优点 **(****Advantages of SQL Trigger****)** ##

*  SQL Trigger easily **identifies changes made in the database**.
    
    SQL触发器可以轻松**识别数据库中所做的更改** 。
 *  It **maintains the integrity** of the data.
    
    它**维护**数据**的完整性** 。

--------------------

## SQL触发器的局限性 **(****Limitations of SQL Trigger****)** ##

The most important and probably the only disadvantage of Triggers is the problem of **Troubleshooting**.

触发器的最重要也是唯一的缺点是**故障排除**问题。

Triggers executes automatically in the database, which makes it difficult to troubleshoot and resolve.

触发器在数据库中自动执行，这使故障排除和解决变得困难。

--------------------

## 结论 **(****Conclusion****)** ##

By this, we have come to the end of this topic. Please feel free to comment below, in case you come across any doubt.

至此，我们到了本主题的结尾。 如果您有任何疑问，请在下面发表评论。

For more such posts related to SQL, please do visit [SQL JournalDev][].

有关与SQL有关的更多此类帖子，请访问[SQL JournalDev][] 。

--------------------

## 参考资料 **(****References****)** ##

*  [SQL Triggers — Documentation][SQL Triggers _ Documentation]
    
    [SQL触发器-文档][SQL Triggers _ Documentation]

> 翻译自: [https://www.journaldev.com/41347/sql-triggers][https_www.journaldev.com_41347_sql-triggers]

sql 触发器未触发

[SQL Create query]: https://www.journaldev.com/24795/sql-create-table
[INSERT operation]: https://www.journaldev.com/18270/sql-insert-query
[SQL SELECT]: https://www.journaldev.com/18262/sql-select
[SQL Trigger Before INSERT Query]: /images/20230209/1e16743d675c4e3aac40671e1d2c37da.png
[SQL JournalDev]: https://www.journaldev.com/sql
[SQL Triggers _ Documentation]: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
[https_www.journaldev.com_41347_sql-triggers]: https://www.journaldev.com/41347/sql-triggers