基于CTE设计的多行记录更新触发器应用研究

The Application Research on Multiple Records Update Trigger Based on CTE Design

使用SQL Server触发器可以级联更新数据库中相关表记录,现有文献中,对SQL Server触发器及应用案例的描述,多数只针对数据表中一个记录更新所激发的触发器,不能适于多行记录更新激发的触发器。在SQL Server触发器的更新语句中使用CTE,可以减少中间数据和临时数据表的存储管理,设计更简练,执行效率更高。本文通过SQLServer开发实例,改进算法,使用CTE,设计了针对多行记录更新的DML触发器,级联更新相关数据表,实现数据完整性,适用性和实用性较强。

The records of related tables are cascading updated by using SQL Server 仕 igger in the database. In the existing literature, a record in the table instead of multiple records is generally updated for the cases of SQL Server triggers and the application. The CTE is used to update statement in the SQL Server triggers, which can reduce the storage and management of intermediate data and temporary tables. It results in the more concise design and the higher execution efficiency. In this paper, DML trigger with multiple records update is designed by using develop-ment examples of SQLServer, improved algorithm and the CTE. It can cascade update related data table, guarantee data integrity and have strong applicability and practicability.