SQL Server: Ausführliche Erklärung der Trigger

1. Übersicht

Ein Trigger ist eine spezielle gespeicherte Prozedur, die nicht explizit aufgerufen werden kann, sondern automatisch aktiviert wird, wenn ein Datensatz in die Tabelle eingefügt, aktualisiert oder gelöscht wird. Daher können Trigger verwendet werden, um komplexe Integritätsbeschränkungen für Tabellen zu implementieren.

2. Klassifizierung von Triggern

SQL Server2000 bietet zwei Arten von Triggern: „Anstatt“- und „Nach“-Trigger.

Jede Änderungsaktion (Einfügen, Aktualisieren und Löschen) einer Tabelle oder Ansicht kann einen „Statt von“-Trigger haben, und jede Änderungsaktion einer Tabelle kann mehrere „Nachher“-Trigger haben.

2.1 „Statt von“-Trigger

Der „Statt von“-Trigger wird ausgeführt, bevor das eigentliche „Einfügen“ durchgeführt wird. Zusätzlich zu Tabellen können „Statt“-Trigger auch für Ansichten verwendet werden, um die Aktualisierungsvorgänge zu erweitern, die die Ansicht unterstützen kann.

Der Trigger „Statt von“ ersetzt die auszuführende SQL-Anweisung, was bedeutet, dass die auszuführende SQL-Anweisung nicht „tatsächlich ausgeführt“ wird.

alter trigger trigger_学生_Delete
on 学生
instead of Delete
as
begin
    select 学号, 姓名 from deleted
end
 
delete from 学生 where 学号 = 4

Das obige Beispiel definiert „triggerStudent_Delete“-Trigger, der die zu löschenden Schüler aus der „delete“-Tabelle ausgibt. Nach dem Ausführen des „delete“-Vorgangs werden Sie feststellen, dass der Schüler mit „Studentennummer = 4“ aufgrund des „ „trigger“ nicht gelöscht wurde student Delete“ ersetzt die auszuführende Anweisung „delete from student where student number = 4“, aber der Student wird in „trigger student_Delete“ nicht tatsächlich gelöscht.

2.2 „After“-Trigger

Der „After“-Trigger wird ausgelöst, nachdem die Insert-, Update- oder Deleted-Anweisung ausgeführt wurde. „After“-Trigger können nur für Tabellen verwendet werden.

Der „After“-Trigger wird hauptsächlich zum Ändern anderer Tabellen verwendet, nachdem die Tabelle geändert wurde (nach Einfüge-, Aktualisierungs- oder Löschvorgängen)

3. Eingefügte und gelöschte Tabellen

SQL Server erstellt für jeden Trigger zwei dedizierte Tabellen: die eingefügte Tabelle und die gelöschte Tabelle.

Diese beiden Tabellen werden vom System verwaltet. Sie existieren im Speicher und nicht in der Datenbank und können als virtuelle Tabelle verstanden werden.

Die Struktur dieser beiden Tabellen ist immer dieselbe wie die Struktur der Tabelle, auf die der Trigger einwirkt.

Nach Abschluss der Triggerausführung werden auch die beiden mit dem Trigger verbundenen Tabellen gelöscht.

In der Tabelle „Gelöscht“ werden alle Zeilen gespeichert, die infolge der Ausführung einer Lösch- oder Aktualisierungsanweisung aus der Tabelle gelöscht werden sollen.

In der Inserted-Tabelle werden alle Zeilen gespeichert, die aufgrund der Ausführung der Insert- oder Update-Anweisung in die Tabelle eingefügt werden sollen.

4. Trigger-Ausführungsprozess

Wenn eine Einfügungs-, Aktualisierungs- oder Löschanweisung eine Einschränkung verletzt, wird die SQL-Anweisung daher nicht erfolgreich ausgeführt Der „Nachher“-Trigger wird ebenfalls nicht aktiviert.

Anstelle der Aktion, die ihn ausgelöst hat, kann ein „Statt von“-Trigger ausgeführt werden. Es wird ausgeführt, wenn die eingefügte Tabelle und die gelöschte Tabelle gerade erstellt wurden und keine anderen Vorgänge ausgeführt wurden. Da der „Statt von“-Trigger vor der Einschränkung ausgeführt wird, kann er eine gewisse Vorverarbeitung der Einschränkung durchführen.

5. Trigger erstellen

create trigger trigger_name
on  {table_name|view_name}
{After|Instead of} {insert|update|delete}
as 相应T-SQL语句

6. Trigger ändern:

alter trigger trigger_name
on  {table_name|view_name}
{After|Instead of} {insert|update|delete}
as 相应T-SQL语句

7. Trigger löschen:

drop trigger trigger_name

8. Vorhandene Trigger in der Datenbank anzeigen:

8.1 Anzeigen alle Trigger in der Datenbank

select * from sysobjects where xtype='TR'

8.2 Einen einzelnen Trigger anzeigen

exec sp_helptext '触发器名'

9. „Stattdessen“ verwandte Beispiele:

Zwei Tabellen: Studenten (Studentennummer int, Name varchar), Ausleihdatensätze (Studentennummer int, Buchnummer int)

Implementierungsfunktion: Wenn der Student beim Löschen der Studententabelle noch über einen (nicht zurückgegebenen) Ausleihdatensatz verfügt, kann dieser nicht gelöscht werden

alter trigger trigger_学生_Delete
on 学生
instead of Delete
as
begin
    if not exists(select * from 借书记录, deleted where 借书记录.学号 = deleted.学号)
        delete from 学生 where 学生.学号 in (select 学号 from deleted)
end

create trigger trigger_订单_insert
on 订单
after insert
as
    if (select 状态 from 商品, inserted where 商品.pid = inserted.pid)=1
    begin
        print 'the goods is being processed'
        print 'the order cannot be committed'
        rollback transaction --回滚，避免加入
    end

create trigger trigger_订单_insert2
on 订单
after insert
as
    update 商品 set 数量 = 数量 - inserted.数量
    from 商品, inserted
    where 商品.pid = inserted.pid

10.3 在“商品”表建立删除触发器，实现“商品”表和“订单”表的级联删除。

create trigger goodsdelete trigger_商品_delete
on 商品
after delete
as
    delete from 订单 where 订单.pid in (select pid from deleted)

10.4 在“订单”表建立一个更新触发器，监视“订单”表的“订单日期”列，使其不能被“update”.

create trigger trigger_订单_update
on 订单
after update
as
    if update(订单日期)
    begin
        raiserror('订单日期不能手动修改',10,1)
        rollback transaction
    end

10.5 在“订单”表建立一个插入触发器，保证向“订单”表插入的货品必须要在“商品”表中一定存在。

create trigger trigger_订单_insert3
on 订单
after insert
as
    if (select count(*) from 商品, inserted where 商品.pid = inserted.pid)=0
    begin
        print '商品不存在'
        rollback transaction
    end

10.6 “订单”表建立一个插入触发器，保证向“订单”表插入的货品信息要在“订单日志”表中添加

alter trigger trigger_订单_insert
on 订单
for insert
as
    insert into 订单日志 select inserted.Id, inserted.pid,inserted.数量 from inserted