sql 触发器未触发_SQL触发器–综合指南-白红宇

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

阅读量：2528 次

发布时间：2019-05-11

本文共 5225 字，大约阅读时间需要 17 分钟。

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 with the columns as follows:

最初，我们使用带有列的创建一个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_valueBefore INSERTON InfoFOR EACH ROWSET 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 – to insert the data into the table.

创建触发器后，我们执行DML- 以将数据插入表中。

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 command.

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