SQL 요약 트리거

개념

트리거는 사용자가 직접 호출하지 않는 특수한 유형의 저장 프로시저입니다. 트리거를 생성할 때 특정 테이블이나 열에 특정 유형의 데이터 수정이 이루어질 때 실행되도록 정의합니다.

트리거는 다른 테이블을 쿼리할 수 있으며 복잡한 SQL 문을 포함할 수 있습니다. 주로 복잡한 비즈니스 규칙이나 요구 사항을 준수하도록 하는 데 사용됩니다. 예를 들어 고객의 현재 계정 상태에 따라 새 주문을 삽입할 수 있는지 여부를 제어할 수 있습니다.
트리거를 사용하면 여러 테이블에서 행을 추가, 업데이트 또는 삭제할 때 해당 테이블 간에 정의된 관계가 유지되도록 참조 무결성을 강화할 수도 있습니다.

함수

1) 트리거는 데이터베이스의 관련 테이블을 통해 계단식 변경을 수행할 수 있습니다. 이러한 변경은 계단식 참조 무결성 제약 조건을 통해 보다 효율적으로 실행될 수 있습니다.

2) 트리거는 CHECK 제약 조건으로 정의된 것보다 더 복잡한 제약 조건을 적용할 수 있습니다. CHECK 제약 조건과 달리 트리거는 다른 테이블의 열을 참조할 수 있습니다. 예를 들어 트리거는 다른 테이블의 SELECT를 사용하여 삽입되거나 업데이트된 데이터를 비교할 수 있을 뿐만 아니라 데이터 수정, 사용자 정의 오류 메시지 표시 등의 다른 작업을 수행할 수도 있습니다.

3) 트리거는 비즈니스 규칙을 적용할 수도 있습니다

4) 트리거는 데이터 수정 전후의 테이블 상태를 평가하고 차이점에 따라 대응할 수도 있습니다.

실용 적용

트리거에는 많은 장점이 있지만 실제 프로젝트 개발에서는 특히 OOP 사고가 심화됨에 따라 트리거의 단점이 점차 부각되고 있습니다. 주로 다음과 같습니다.

1. 트리거가 너무 많으면 데이터 로직이 복잡해집니다

2. 데이터 작업이 상대적으로 암시적이고 조정 및 수정이 어렵습니다

3. 트리거의 기능은 코드 로직이나 트랜잭션의 대체 구현에서 점진적으로 변경됩니다. OO 사고에 더 가깝습니다.

권장사항:

트리거 사용 시 주의하세요.

구문

CREATE TRIGGER trigger_name 
ON {table_name | view_name} 
{FOR | After | Instead of } [ insert, update,delete ]
AS           
    sql_statement

트리거 유형

SQL Server에는 DML(데이터 조작 언어) 트리거와 DDL(데이터 정의 언어) 트리거라는 두 가지 일반적인 유형의 트리거가 포함되어 있습니다. DML 트리거는 INSERT, UPDATE 또는 DELETE 문이 지정된 테이블이나 뷰의 데이터를 수정할 때 사용할 수 있습니다. DDL은 주로 CREATE, ALTER 및 DROP으로 시작하는 다양한 DDL 문에 대한 응답으로 저장 프로시저를 실행합니다. DDL 트리거는 데이터베이스 작업 감사 및 제어와 같은 관리 작업에 사용될 수 있습니다.

일반적으로 언급되는 트리거는 DML 트리거입니다.

DML 트리거는 INSERT, UPDATE 및 DELETE 문에서 작동하며 테이블이나 뷰에서 데이터가 수정될 때 비즈니스 규칙을 적용하여 데이터 무결성을 확장하는 데 도움이 됩니다.

SQL Server2005 이후에 DDL 트리거가 추가되었습니다.

DDL 트리거는 이벤트에 대한 응답으로 저장 프로시저를 실행합니다. 그러나 DML 트리거와 달리 테이블이나 뷰에 대한 UPDATE, INSERT 또는 DELETE 문에 대한 응답으로 실행되지 않습니다. 대신 다양한 DDL(데이터 정의 언어) 이벤트에 대한 응답으로 실행됩니다. 이러한 이벤트는 주로 CREATE, ALTER 및 DROP 키워드로 시작하는 Transact-SQL 문에 해당합니다. DDL 스타일 작업을 수행하는 시스템 저장 프로시저도 DDL 트리거를 실행할 수 있습니다.

DDL 트리거 사용 시나리오:

데이터베이스 스키마에 대한 특정 변경을 방지합니다.

데이터베이스 스키마 변경에 대한 응답으로 데이터베이스에서 어떤 일이 발생하길 원합니다.

데이터베이스 스키마의 변경 사항이나 이벤트를 기록합니다.

여기에서는 DML 트리거에 대해서만 설명합니다. DML 트리거는 다음 범주로 구분됩니다.

1. 트리거 이후

트리거 이후에는 특정 작업 삽입, 업데이트, 삭제가 실행된 후에만 트리거가 트리거되어야 합니다. 테이블에 정의됩니다.

1) 트리거 삽입

2) 트리거 업데이트

3) 트리거 삭제

2. 트리거 대신

이는 정의된 작업(삽입, 업데이트, 삭제)을 실행하지 않고 트리거 자체만 실행한다는 의미입니다. 트리거 대신 테이블이나 뷰에서 정의할 수 있습니다.

삽입과 삭제 비교

触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。

具体应用

在触发器实际应用中，主要还是建立约束以及级联更新。在这里主要通过简单实例予以说明。

 1、触发器新增

原理：

 当触发INSERT触发器时，新的数据行就会被插入到触发器表和inserted表中。inserted表是一个逻辑表，它包含了已经插入的数据行的一个副本。inserted表包含了INSERT语句中已记录的插入动作。inserted表还允许引用由初始化INSERT语句而产生的日志数据。触发器通过检查inserted表来确定是否执行触发器动作或如何执行它。inserted表中的行总是触发器表中一行或多行的副本。

场景：增加学生信息时，要校验其年龄，暂定其年龄必须大于18，否则新增失败 

作用：校验约束

具体实例：

--触发器新增：只允许录取18岁以上学生
IF OBJECT_ID (N'TRIGER_Students_Insert', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Students_Insert;
GO
CREATE TRIGGER TRIGER_Students_Insert
ON Students
FOR INSERT
AS
    declare @age int
    select @age=COUNT(Students.ID) FROM Students INNER JOIN inserted ON Students.ID =inserted.ID    
    PRINT @age
    if(@age<18)
    begin
        raiserror(&#39;学生年龄必须要大于18哦&#39;,16,8)
        rollback tran
    end

执行insert:

INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(105,'李四',16,'BeiJing',11)

执行结果：

会直接异常，返回错误信息

消息 50000，级别 16，状态 8，过程 TRIGER_Students_Insert，第 10 行
学生年龄必须要大于18哦
消息 3609，级别 16，状态 1，第 1 行
事务在触发器中结束。批处理已中止。

2、触发器更新

原理：

可将UPDATE语句看成两步操作：即捕获数据前像(before image)的DELETE语句，和捕获数据后像(after image)的INSERT语句。当在定义有触发器的表上执行UPDATE语句时，原始行（前像）被移入到deleted表，更新行（后像）被移入到inserted表。

触发器检查deleted表和inserted表以及被更新的表，来确定是否更新了多行以及如何执行触发器动作。

可以使用IF UPDATE语句定义一个监视指定列的数据更新的触发器。这样，就可以让触发器容易的隔离出特定列的活动。当它检测到指定列已经更新时，触发器就会进一步执行适当的动作，例如发出错误信息指出该列不能更新，或者根据新的更新的列值执行一系列的动作语句。

场景：

专业信息ID修改，对应的学生信息中专业ID也相应进行修改

实例实现：

--更新触发器：更新专业ID时,同时更新学生的专业信息
IF OBJECT_ID (N'TRIGER_Majors_Update', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Majors_Update;
GO
CREATE TRIGGER TRIGER_Majors_Update
ON Majors
FOR UPDATE
AS
    IF UPDATE(ID)
    UPDATE Students Set MajorID=inserted.ID
    FROM Students,deleted,inserted
    WHERE Students.MajorID = deleted.ID

原始数据：

执行更新操作：

UPDATE Majors SET ID=12 WHERE ID=11

执行结果：

3、触发器删除

原理：

当触发DELETE触发器后，从受影响的表中删除的行将被放置到一个特殊的deleted表中。deleted表是一个逻辑表，它保留已被删除数据行的一个副本。deleted表还允许引用由初始化DELETE语句产生的日志数据。

使用DELETE触发器时，需要考虑以下的事项和原则：

当某行被添加到deleted表中时，它就不再存在于数据库表中；因此，deleted表和数据库表没有相同的行。

创建deleted表时，空间是从内存中分配的。deleted表总是被存储在高速缓存中。

为DELETE动作定义的触发器并不执行TRUNCATE TABLE语句，原因在于日志不记录TRUNCATE TABLE语句。

场景：学校某选修课取消。

处理逻辑：在删除课程的同时，需要删除该课程的选课信息。

触发器：

--删除触发器：删除课程时,同时删除该课程的选课信息
IF OBJECT_ID (N'TRIGER_Courses_Delete', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Courses_Delete;
GO
CREATE TRIGGER TRIGER_Courses_Delete
ON Courses
FOR DELETE
AS
    DELETE SC
    FROM SC,deleted     
    WHERE SC.CourseID = deleted.ID

原始数据：

执行课程删除操作：

DELETE FROM Courses WHERE ID=10

执行结果：

可以看到，删除课程的同时，选修课程10的选课记录也被删除。

4、Instead Of 触发器

用Instead Of触发器实现与实例3相同的功能，具体实现代码如下：

--Instead Of触发器：删除课程时,同时删除该课程的选课信息
IF OBJECT_ID (N'TRIGER_Courses_Instead_Delete', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Courses_Instead_Delete;
GO
CREATE TRIGGER TRIGER_Courses_Instead_Delete
ON Courses
Instead Of DELETE
AS
    declare @courseId int
    --获取要删除的课程ID
    SELECT @courseId=ID FROM deleted
    --删除选课信息
    DELETE FROM SC WHERE CourseID = @courseId
    --删除课程信息
    DELETE FROM Courses WHERE ID=@courseId

执行删除：

--测试用例DELETE FROM Courses WHERE ID=10

测试结果：

其测试结果与实例3相同。

本文测试用例脚本：

--数据准备
--学生信息表
IF OBJECT_ID (N'Students', N'U') IS NOT NULL
    DROP TABLE Students;
GO
CREATE TABLE Students(
    ID int primary key not null,
    Name nvarchar(50),
    Age int,
    City nvarchar(50),
    MajorID int
)


--专业信息表
IF OBJECT_ID (N'Majors', N'U') IS NOT NULL
    DROP TABLE Majors;
GO
CREATE TABLE Majors(
    ID int primary key not null,
    Name nvarchar(50)
)

--课程表
IF OBJECT_ID (N'Courses', N'U') IS NOT NULL
    DROP TABLE Courses;
GO
CREATE TABLE Courses(
    ID int primary key not null,
    Name nvarchar(50) not null
)

IF OBJECT_ID (N'SC', N'U') IS NOT NULL
    DROP TABLE SC;
GO
--选课表
CREATE TABLE SC(
    StudentID int not null,
    CourseID int not null,
    Score int    
)

/*
基础数据
*/

--专业信息
DELETE FROM Majors
INSERT INTO Majors(ID,Name) VALUES(10,'法律')
INSERT INTO Majors(ID,Name) VALUES(11,'美学')

--课程信息
DELETE FROM Courses
INSERT INTO Courses(ID,Name) VALUES (10,'太极拳')
INSERT INTO Courses(ID,Name) VALUES (11,'摄影入门')
INSERT INTO Courses(ID,Name) VALUES (12,'生命科学导论')

--学生信息
DELETE FROM Students
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,'Tom',20,'BeiJing',10)
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(103,'李明',20,'BeiJing',11)
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(104,'王涛',18,'ShangHai',11)


--选课信息
DELETE FROM SC
INSERT INTO SC(StudentID,CourseID) VALUES(101,10)
INSERT INTO SC(StudentID,CourseID) VALUES(101,11)
INSERT INTO SC(StudentID,CourseID) VALUES(102,12)



--触发器新增：只允许录取18岁以上学生
IF OBJECT_ID (N'TRIGER_Students_Insert', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Students_Insert;
GO
CREATE TRIGGER TRIGER_Students_Insert
ON Students
FOR INSERT
AS
    declare @age int
    select @age=COUNT(Students.ID) FROM Students INNER JOIN inserted ON Students.ID =inserted.ID    
    PRINT @age
    if(@age<18)
    begin
        raiserror(&#39;学生年龄必须要大于18哦&#39;,16,8)
        rollback tran
    end

--测试用例
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(105,'李四',16,'BeiJing',11)
SELECT * FROM Students

--更新触发器：更新专业ID时,同时更新学生的专业信息
IF OBJECT_ID (N&#39;TRIGER_Majors_Update&#39;, N&#39;tr&#39;) IS NOT NULL
    DROP TRIGGER TRIGER_Majors_Update;
GO
CREATE TRIGGER TRIGER_Majors_Update
ON Majors
FOR UPDATE
AS
    IF UPDATE(ID)
    UPDATE Students Set MajorID=inserted.ID
    FROM Students,deleted,inserted
    WHERE Students.MajorID = deleted.ID

--测试用例
UPDATE Majors SET ID=12 WHERE ID=11

SELECT * FROM Students
SELECT * FROM Majors

--删除触发器：删除课程时,同时删除该课程的选课信息
IF OBJECT_ID (N&#39;TRIGER_Courses_Delete&#39;, N&#39;tr&#39;) IS NOT NULL
    DROP TRIGGER TRIGER_Courses_Delete;
GO
CREATE TRIGGER TRIGER_Courses_Delete
ON Courses
FOR DELETE
AS
    DELETE SC
    FROM SC,deleted     
    WHERE SC.CourseID = deleted.ID

--测试用例
DELETE FROM Courses WHERE ID=10

--执行结果
SELECT * FROM Students
SELECT * FROM Courses
SELECT * FROM SC



--Instead Of触发器：删除课程时,同时删除该课程的选课信息
IF OBJECT_ID (N&#39;TRIGER_Courses_Instead_Delete&#39;, N&#39;tr&#39;) IS NOT NULL
    DROP TRIGGER TRIGER_Courses_Instead_Delete;
GO
CREATE TRIGGER TRIGER_Courses_Instead_Delete
ON Courses
Instead Of DELETE
AS
    declare @courseId int
    --获取要删除的课程ID
    SELECT @courseId=ID FROM deleted
    --删除选课信息
    DELETE FROM SC WHERE CourseID = @courseId
    --删除课程信息
    DELETE FROM Courses WHERE ID=@courseId

--测试用例
DELETE FROM Courses WHERE ID=10

--执行结果
SELECT * FROM Students
SELECT * FROM Courses
SELECT * FROM SC