SQL 语言:存储过程和触发器

简介: SQL 语言:存储过程和触发器

基本概述

存储过程,类似于高阶语言的函数或者方法,包含SQL语句序列,是可复用的语句,保存在数据库中,在服务器中执行。特点是复用,提高了效率,安全性。

触发器(Trigger)是数据库中一种特殊的存储过程,它可以在数据表上定义特定的事件(如插入、更新或删除操作)发生时自动执行。触发器的主要作用是实现数据的完整性约束、审计、日志记录等功能。在SQL语言中,触发器的使用非常广泛,它可以帮助我们更好地管理和维护数据库。


触发器的主要特点如下:

  1. 触发器是一种特殊类型的存储过程,它与普通的存储过程不同之处在于,触发器不能被直接调用,而是在满足特定条件时自动执行。
  2. 触发器可以关联到一个或多个数据表,当这些表的数据发生变化时,触发器会自动执行。这种关联关系是通过触发器的定义来实现的。
  3. 触发器可以分为多种类型,如DML触发器(针对INSERT、UPDATE、DELETE操作)、DDL触发器(针对CREATE、ALTER、DROP等操作)和LOGON触发器(针对用户登录操作)。不同类型的触发器可以实现不同的功能。
  4. 触发器可以访问被修改的数据行,这使得我们可以在触发器中对数据进行更复杂的处理。例如,我们可以在插入新数据时检查数据的有效性,或者在更新数据时自动更新与之相关的其他数据表。
  5. 触发器可以级联执行。当一个触发器执行时,它可以触发另一个触发器的执行。这种级联关系可以帮助我们实现更复杂的业务逻辑。
  6. 触发器可以提高数据库的性能。通过在触发器中实现一些复杂的业务逻辑,我们可以减少客户端程序的负担,从而提高整个系统的性能。
  7. 触发器可以提高数据的一致性。通过在触发器中实现数据的完整性约束,我们可以确保数据的准确性和一致性。
  8. 触发器可以实现数据库的审计和日志记录功能。通过在触发器中记录数据的变化情况,我们可以追踪数据库的操作历史,从而便于分析和排查问题。
  9. 触发器是一种特殊类型的存储过程,是通过事件触发而执行的,而存储过程可以通过存储过程名称而被直接调用。

触发器数据库对象,当创建一个触发器时必须指定:1> 名称;2> 在其上定义触发器的表;3> 触发器将何时激发;4> 指明触发器执行时应做的动作。其名称必须遵循标识符的命名规则,数据库像存储普通数据那样存储触发器。触发器可以引用当前数据库以外的对象,但只能在当前数据库中创建触发器。尽管不能在临时表或系统表上创建触发器,但是触发器可以引用临时表。


触发动作实际上是一系列SQL语句,可以有两种方式:


(1) 对被事件影响的每一行(FOR EACH ROW),每一元组执行触发过程,称为行级触发器。


(2) 对整个事件只执行一次触发过程(FOR EACH STATEMENT),称为语句级触发器。该方式是触发器的默认方式。

创建触发器

触发器主要包括两个方面:指明触发器的触发事件,指明触发器执行的动作。

触发事件包括表中行的插入、删除和修改,即执行 INSERT、DELETE、UPDATE 语句。 在修改操作 (UPDATE)中,还可以指定特定的属性或属性组的修改为触发条件。事件的触发还有两个相关的时间:BEFORE 和 AFTER。BEFORE 触发器是在事件发生之前触发,AFTER 触发器是在事件发生之后触发。创建触发器语句格式如下:

CREATE TRIGGER <触发器名> [{BEFORE|AFTER}]
{[DELETE|INSERT|UPDATE OF[列名清单]]}
ON 
[REFERENCING <临时视图名>]
[FOR EACH ROW|FOR EACH STATEMENT]
[WHEN <触发条件>]
BEGIN
<触发动作>
END [触发器名];

参数说明:


BEFORE:指示 DBMS 在执行触发语句之前激发触发器。

AFTER:指示 DBMS在执行触发语句之后激发触发器。

DELETE:指明是 DELETE 触发器,每当一个DELETE 语句从表中删除一行时激发触发器。

INSERT:指明是 INSERT 触发器,每当一个INSERT语句向表中插入一行时激发触发器

UPDATE:指明是 UPDATE 触发器,每当 UPDATE 语句修改由 OF 子句指定的列值时,激发触发器。如果忽略 OF 子句,每当 UDPATE 语句修改表的任何列值时,DBMS 都将激发触发器。

REFERENCING <临时视图名>:指定临时视图的别名 。在触发器运行过程中,系统会生成两个临时视图,分别存放被更新值(旧值)和更新后的值(新值)。对于行级触发器, 默认临时视图名分别是 OLD 和 NEW;对于语句级触发器,默认临时视图名分别是 OLD-TABLE 和 NEW-TABLE。一旦触发器运行结束,临时视图就不在。

WHEN <触发条件>:指定触发器的触发条件。当满足触发条件时,DBMS 才激发触发器。触发条件中必须包含临时视图名,不包含查询。

示例1. 银行数据库关系模式如下:

            Account (Account-no, branch-name, balance)

            Loan (Loan-no, branch-name, amount)

            Depositor (customer-name, Account-no)

账户关系模式 Account 中的属性 Account-no 表示账号 ,branch-name 表示支行名称,balance 表示余额。贷款关系模式 Loan 中的属性 Loan-no 表示贷款号,branch-name 表示支行名称,amount 表示金额。存款关系模式 Depositor 中的属性 customer-name 表示存款人姓名。SQL-99 创建触发器如下所示:

CREATE TRIGGER overdraft_trigger AFTER UPDATE ON Account
REFERENCING NEW ROW AS nrow
FOR EACH ROW 
WHEN nrow.balance < 0 
BEGIN ATOMIC
INSERT INTO borrower
(SELECT customer-name,Account-no
FROM Depositor
WHERE nrow.account-no = Depositor.account-no);
INSERT INTO loan VALUES (nrow.account-no,branch-name,-nrow.balance);
UPDATE account SET balance = 0
WHERE account.account-no = nrow.account-no;
END;

When语句指定一个条件nrow.balance<0。仅对满足条件的元组才会执行余下的触发器;BEGIN ATOMIC 子句用来将多行SQL语句集成为一个复合语句,该子句中的两条 INSERT INTO 语句执行了在borrower和loan关系中建立新的贷款业务;UPDATE语句用来将账户余额清零;


示例2. 仓库管理数据库中有如下关系,请创建一个重新订购商品的触发器。

          inventory(item,level) :表示某种商品在仓库中的现有量

          minlevel(item,level) :表示某种商品在仓库中存有的最小量

          reorder(item,amount) :表示某种商品小于最小量的时候要订购的数量

          orders(item,amount):表示某种商品被定购的量
CREATE TRIGGER reorder_trigger AFTER UPDATE OF amount on inventory
REFERENCING OLD ROW AS orow,NEW ROW AS nrow
FOR EACH ROW 
WHEN nrow.level <= (SELECT level
                    FROM minlevel
                    WHERE minlevel.item = orow.item)
AND orow.level > (SELECT level
                  FROM minlevel
                  WHERE minlevel.item = orow.item)
BEGIN 
INSERT INTO orders
(SELECT item,amount
FROM reorder
WHERE reorder.item = orow.item)
END;

示例3. 若修改某商品的库存时,使得库存值小于或等于其最小库存值,则向采购表插入一条记录,要求采购的数量是该商品最小库存值的两倍再加上10。

CREATE TRIGGER 采购-trigger AFTER UPDATE ON 商品 [AFTER UPDATE OF 库存 ON 商品]
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN nrow.库存<=nrow.最小库存
BEGIN
INSERT INTO 采购(商品号,采购数量)
VALUES(nrow.商品号,nrow.最小库存*2+10)
END;

更改和删除触发器

使用系统命令 ALTER TRIGGER 更改指定的触发器的定义,语法如下:

ALTER TRIGGER <触发器> [{BEFORE|AFTER}] {[DELETE|INSERT|UPDATE OF [列名清单]]}
ON 表名|视图名
AS 
BEGIN 
 SQL STATEMENTS
END;

使用 DROP TRIGGER <触发器>[,…n],其中,n 表示可以指定多个触发器的占位符。

总结

触发器是数据库中一种非常重要的功能,它可以帮助我们实现数据的完整性约束、审计、日志记录等功能。通过合理地使用触发器,我们可以提高数据库的性能和数据的一致性,从而更好地管理和维护数据库。然而,触发器的使用也需要谨慎,因为不当的使用可能会导致性能问题和数据不一致的问题。因此,在使用触发器时,我们需要充分了解其原理和使用方法,以确保其能够发挥最大的作用。

相关文章
|
1月前
|
SQL Java 编译器
SQL 语言:嵌入式 SQL 和动态 SQL
SQL 语言:嵌入式 SQL 和动态 SQL
31 4
|
1月前
|
SQL 存储 数据库
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】(下)
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】
32 1
|
1月前
|
SQL 存储 数据库
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】(中)
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】
40 1
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(2)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(1)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
14天前
|
SQL 自然语言处理 关系型数据库
PolarDB自然语言到SQL语言转义
PolarDB自然语言到SQL语言转义
|
1月前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
1月前
|
存储 SQL 关系型数据库
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(3)
MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】
|
1月前
|
SQL 存储 数据库
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】(上)
爆肝!一看就懂的《SQL 语言知识体系》【建议收藏】
30 3
|
1月前
|
SQL 存储 数据建模
SQL 语言:对象关系数据模型
SQL 语言:对象关系数据模型
22 3