SQL-Zusammenfassungsauslöser-PHP开发-php.cn

Konzept

Ein Trigger ist eine spezielle Art einer gespeicherten Prozedur, die nicht direkt vom Benutzer aufgerufen wird. Wenn Sie einen Trigger erstellen, definieren Sie ihn so, dass er ausgeführt wird, wenn eine bestimmte Art von Datenänderung an einer bestimmten Tabelle oder Spalte vorgenommen wird.

Trigger können andere Tabellen abfragen und komplexe SQL-Anweisungen enthalten. Sie dienen in erster Linie dazu, die Einhaltung komplexer Geschäftsregeln oder -anforderungen durchzusetzen. Sie können beispielsweise anhand des aktuellen Kontostands des Kunden steuern, ob neue Bestellungen eingegeben werden dürfen.
Trigger können auch verwendet werden, um die referenzielle Integrität zu erzwingen, sodass beim Hinzufügen, Aktualisieren oder Löschen von Zeilen in mehreren Tabellen die zwischen diesen Tabellen definierten Beziehungen erhalten bleiben.

Funktion

1) Trigger können kaskadierende Änderungen durch verwandte Tabellen in der Datenbank erreichen; diese Änderungen können durch kaskadierende referenzielle Integritätsbeschränkungen effizienter ausgeführt werden;

2) Trigger können komplexere Einschränkungen erzwingen als die mit CHECK-Einschränkungen definierten. Im Gegensatz zu CHECK-Einschränkungen können Trigger auf Spalten in anderen Tabellen verweisen. Beispielsweise kann ein Trigger ein SELECT aus einer anderen Tabelle verwenden, um eingefügte oder aktualisierte Daten zu vergleichen und andere Vorgänge auszuführen, etwa das Ändern von Daten oder das Anzeigen benutzerdefinierter Fehlermeldungen.

3) Trigger können auch Geschäftsregeln erzwingen

4) Trigger können auch den Tabellenstatus vor und nach der Datenänderung auswerten und basierend auf den Unterschieden Gegenmaßnahmen ergreifen.

Praktische Anwendung

Obwohl Trigger viele Vorteile haben, werden in der tatsächlichen Projektentwicklung, insbesondere mit der Vertiefung des OOP-Denkens, die Nachteile von Triggern nach und nach hervorgehoben, hauptsächlich:

1. Zu viele Trigger machen die Datenlogik komplex

2. Datenoperationen sind relativ implizit und schwer anzupassen und zu ändern

3 Die Funktion von Triggern ändert sich allmählich in der Codelogik oder der alternativen Implementierung in Transaktionen entspricht eher dem OO-Denken.

Empfehlung:

Seien Sie vorsichtig bei der Verwendung von Auslösern.

Syntax

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

Nach dem Login kopieren

Triggertypen

SQL Server umfasst zwei allgemeine Arten von Triggern: Data Manipulation Language (DML)-Trigger und Data Definition Language (DDL)-Trigger. DML-Trigger können verwendet werden, wenn eine INSERT-, UPDATE- oder DELETE-Anweisung Daten in einer angegebenen Tabelle oder Ansicht ändert. DDL löst gespeicherte Prozeduren als Reaktion auf verschiedene DDL-Anweisungen aus, die hauptsächlich mit CREATE, ALTER und DROP beginnen. DDL-Trigger können für Verwaltungsaufgaben wie die Überwachung und Steuerung von Datenbankvorgängen verwendet werden.

Der allgemein genannte Trigger ist der DML-Trigger.

DML-Trigger wirken auf INSERT-, UPDATE- und DELETE-Anweisungen und helfen bei der Durchsetzung von Geschäftsregeln, indem sie die Datenintegrität erweitern, wenn Daten in einer Tabelle oder Ansicht geändert werden.

DDL-Trigger wurden nach SQL Server2005 hinzugefügt.

DDL-Trigger lösen gespeicherte Prozeduren als Reaktion auf Ereignisse aus. Im Gegensatz zu DML-Triggern werden sie jedoch nicht als Reaktion auf eine UPDATE-, INSERT- oder DELETE-Anweisung für eine Tabelle oder Ansicht ausgelöst. Stattdessen werden sie als Reaktion auf verschiedene DDL-Ereignisse (Data Definition Language) ausgelöst. Diese Ereignisse entsprechen hauptsächlich Transact-SQL-Anweisungen, die mit den Schlüsselwörtern CREATE, ALTER und DROP beginnen. Gespeicherte Systemprozeduren, die Vorgänge im DDL-Stil ausführen, können auch DDL-Trigger auslösen.

DDL-Trigger-Nutzungsszenarien:

Um bestimmte Änderungen am Datenbankschema zu verhindern.

Möchten Sie, dass als Reaktion auf eine Änderung im Datenbankschema etwas in der Datenbank passiert.

Zum Protokollieren von Änderungen oder Ereignissen im Datenbankschema.

Hier sprechen wir nur über DML-Trigger. DML-Trigger sind in die folgenden Kategorien unterteilt:

1. Nach-Trigger

Nach-Trigger erfordert, dass der Trigger erst ausgelöst werden kann, nachdem eine bestimmte Operation „Einfügen“, „Aktualisieren“ und „Löschen“ ausgeführt wurde auf dem Tisch definiert werden.

1) Trigger einfügen

2) Trigger aktualisieren

3) Trigger löschen

2. Statt Trigger

Statt Trigger bedeutet, dass es nicht seine definierten Operationen (Einfügen, Aktualisieren, Löschen) ausführt, sondern nur den Trigger selbst ausführt. Anstelle von Triggern können Sie diese auch in der Tabelle oder in der Ansicht definieren.

Vergleich zwischen eingefügt und gelöscht

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

SQL-Zusammenfassungsauslöser

具体应用

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

1、触发器新增

原理：

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

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

作用：校验约束

具体实例：

--触发器新增：只允许录取18岁以上学生
IF OBJECT_ID (N&#39;TRIGER_Students_Insert&#39;, N&#39;tr&#39;) 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

Nach dem Login kopieren

执行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 行
事务在触发器中结束。批处理已中止。

Nach dem Login kopieren

2、触发器更新

原理：

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

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

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

场景：

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

实例实现：

--更新触发器：更新专业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

Nach dem Login kopieren

原始数据：

SQL-Zusammenfassungsauslöser

执行更新操作：

UPDATE Majors SET ID=12 WHERE ID=11

执行结果：

SQL-Zusammenfassungsauslöser

3、触发器删除

原理：

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

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

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

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

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

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

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

触发器：

--删除触发器：删除课程时,同时删除该课程的选课信息
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

Nach dem Login kopieren

原始数据：

SQL-Zusammenfassungsauslöser

执行课程删除操作：

DELETE FROM Courses WHERE ID=10

执行结果：

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

SQL-Zusammenfassungsauslöser

4、Instead Of 触发器

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

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

Nach dem Login kopieren

执行删除：

--测试用例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&#39;TRIGER_Students_Insert&#39;, N&#39;tr&#39;) 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

Nach dem Login kopieren