Ausführliche Erläuterung der SQL Server-Trigger

Eine spezielle gespeicherte Prozedur, die automatisch ausgeführt wird, wenn ein Trigger etwas in eine Tabelle einfügt, aktualisiert oder löscht. Trigger werden im Allgemeinen für Einschränkungen mit komplexeren Prüfeinschränkungen verwendet. Der Unterschied zwischen Triggern und gewöhnlichen gespeicherten Prozeduren besteht darin, dass Trigger auf einer bestimmten Tabelle ausgeführt werden. Bei Vorgängen wie Aktualisieren, Einfügen und Löschen ruft das System automatisch den entsprechenden Trigger für die Tabelle auf und führt ihn aus. Trigger in SQL Server 2005 können in zwei Kategorien unterteilt werden: DML-Trigger und DDL-Trigger wirken sich auf eine Vielzahl von Anweisungen in der Datendefinitionssprache aus und werden ausgelöst.

DML-Trigger sind unterteilt in:

1. Nach-Trigger (ausgelöst nach)

b 🎜>c. Löschauslöser

2. Anstelle von Auslöser (ausgelöst vor)

Der Nachauslöser erfordert nur einen Auslöser, nachdem eine bestimmte Operation ausgeführt wurde auf dem Tisch definiert. Anstelle eines Triggers bedeutet dies, dass er 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.

Trigger haben zwei spezielle Tabellen: Tabelle einfügen (eingefügte Tabelle) und Tabelle löschen (gelöschte Tabelle). Diese beiden sind logische Tabellen und virtuelle Tabellen. Das System erstellt zwei Tabellen im Speicher und speichert sie nicht in der Datenbank. Darüber hinaus sind beide Tabellen schreibgeschützt und die Daten können nur gelesen, aber nicht geändert werden. Die Ergebnisse dieser beiden Tabellen haben immer die gleiche Struktur wie die Tabelle, für die der geänderte Trigger gilt. Wenn der Trigger seine Arbeit beendet, werden die beiden Tabellen gelöscht. Die Daten in der eingefügten Tabelle sind die Daten nach dem Einfügen oder Ändern, während die Daten in der gelöschten Tabelle die Daten vor dem Aktualisieren oder Löschen sind.

Operationen an Tabellen

Logische Tabelle eingefügt

Logische Tabelle gelöscht

Datensätze hinzufügen (einfügen)

Speicher hinzugefügte Datensätze

Keine

Datensatz löschen (löschen)

Keine

Gelöschten Datensatz speichern

Datensatz ändern (aktualisieren)

Speicherung aktualisiert Datensätze

Vorab aktualisierte Datensätze speichern

Wenn Sie Daten aktualisieren, löschen Sie zuerst den Tabellendatensatz und fügen Sie dann einen Datensatz hinzu. Auf diese Weise werden in den eingefügten und gelöschten Tabellen aktualisierte Datensätze vorhanden sein. Beachten Sie, dass der Trigger selbst eine Transaktion ist, sodass einige spezielle Prüfungen für geänderte Daten im Trigger durchgeführt werden können. Wenn dies nicht der Fall ist, können Sie den Vorgang mithilfe des Transaktions-Rollbacks rückgängig machen.

Ø Trigger erstellen

Syntax

create trigger tgr_name
on table_name
with encrypion –加密触发器
    for update...
as
    Transact-SQL

# Einfügetyp-Trigger erstellen

--创建insert插入类型触发器
if (object_id('tgr_classes_insert', 'tr') is not null)
    drop trigger tgr_classes_insert
go
create trigger tgr_classes_insert
on classes
    for insert --插入触发
as
    --定义变量
    declare @id int, @name varchar(20), @temp int;
    --在inserted表中查询已经插入记录信息
    select @id = id, @name = name from inserted;
    set @name = @name + convert(varchar, @id);
    set @temp = @id / 2;   
    insert into student values(@name, 18 + @id, @temp, @id);
    print '添加学生成功！';
go
--插入数据
insert into classes values('5班', getDate());
--查询数据
select * from classes;
select * from student order by id;

Einfügetrigger fügt einen neu eingefügten Datensatz zur eingefügten Tabelle hinzu.

# Erstellen Sie einen Löschtyp-Trigger

--delete删除类型触发器
if (object_id('tgr_classes_delete', 'TR') is not null)
    drop trigger tgr_classes_delete
go
create trigger tgr_classes_delete
on classes
    for delete --删除触发
as
    print '备份数据中……';   
    if (object_id('classesBackup', 'U') is not null)
        --存在classesBackup，直接插入数据
        insert into classesBackup select name, createDate from deleted;
    else
        --不存在classesBackup创建再插入
        select * into classesBackup from deleted;
    print '备份数据成功！';
go
--
--不显示影响行数
--set nocount on;
delete classes where name = '5班';
--查询数据
select * from classes;
select * from classesBackup;

Der Löschtrigger speichert die gerade gelöschten Daten in der gelöschten Tabelle.

# Erstellen Sie einen Update-Typ-Trigger

--update更新类型触发器
if (object_id('tgr_classes_update', 'TR') is not null)
    drop trigger tgr_classes_update
go
create trigger tgr_classes_update
on classes
    for update
as
    declare @oldName varchar(20), @newName varchar(20);
    --更新前的数据
    select @oldName = name from deleted;
    if (exists (select * from student where name like '%'+ @oldName + '%'))
        begin
            --更新后的数据
            select @newName = name from inserted;
            update student set name = replace(name, @oldName, @newName) where name like '%'+ @oldName + '%';
            print '级联修改数据成功！';
        end
    else
        print '无需修改student表！';
go
--查询数据
select * from student order by id;
select * from classes;
update classes set name = '五班' where name = '5班';

Der Update-Trigger speichert die Daten vor dem Update in gelöschter Form. In der Tabelle werden die aktualisierte Daten werden in der eingefügten Tabelle gespeichert.

# Update Update-Trigger auf Spaltenebene

if (object_id('tgr_classes_update_column', 'TR') is not null)
    drop trigger tgr_classes_update_column
go
create trigger tgr_classes_update_column
on classes
    for update
as
    --列级触发器：是否更新了班级创建时间
    if (update(createDate))
    begin
        raisError('系统提示：班级创建时间不能修改！', 16, 11);
        rollback tran;
    end
go
--测试
select * from student order by id;
select * from classes;
update classes set createDate = getDate() where id = 3;
update classes set name = '四班' where id = 7;

Update-Trigger auf Spaltenebene kann Update verwenden, um zu bestimmen, ob Spaltendatensätze aktualisiert werden sollen;

# statt Typ Trigger

statt Trigger bedeutet, dass es seine definierten Operationen (Einfügen, Aktualisieren, Löschen) nicht ausführt, sondern nur den Inhalt des Triggers selbst ausführt.

Syntax erstellen

create trigger tgr_name
on table_name
with encryption
    instead of update...
as
    T-SQL

# Erstellen statt auslösen

if (object_id('tgr_classes_inteadOf', 'TR') is not null)
    drop trigger tgr_classes_inteadOf
go
create trigger tgr_classes_inteadOf
on classes
    instead of delete/*, update, insert*/
as
    declare @id int, @name varchar(20);
    --查询被删除的信息，病赋值
    select @id = id, @name = name from deleted;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name;
    --先删除student的信息
    delete student where cid = @id;
    --再删除classes的信息
    delete classes where id = @id;
    print '删除[ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] 的信息成功！';
go
--test
select * from student order by id;
select * from classes;
delete classes where id = 7;

# Benutzerdefinierten Meldungs-Raiseerror anzeigen

if (object_id('tgr_message', 'TR') is not null)
    drop trigger tgr_message
go
create trigger tgr_message
on student
    after insert, update
as raisError('tgr_message触发器被触发', 16, 10);
go
--test
insert into student values('lily', 22, 1, 7);
update student set sex = 0 where name = 'lucy';
select * from student order by id;

# Auslöser ändern

alter trigger tgr_message
on student
after delete
as raisError('tgr_message触发器被触发', 16, 10);
go
--test
delete from student where name = 'lucy';

# Trigger aktivieren oder deaktivieren

--禁用触发器
disable trigger tgr_message on student;
--启用触发器
enable trigger tgr_message on student;

# Die erstellten Triggerinformationen abfragen

--查询已存在的触发器
select * from sys.triggers;
select * from sys.objects where type = 'TR';
 
--查看触发器触发事件
select te.* from sys.trigger_events te join sys.triggers t
on t.object_id = te.object_id
where t.parent_class = 0 and t.name = 'tgr_valid_data';
 
--查看创建触发器语句
exec sp_helptext 'tgr_message';

# Beispiel, eingegebene Daten überprüfen

   
if ((object_id('tgr_valid_data', 'TR') is not null))
    drop trigger tgr_valid_data
go
create trigger tgr_valid_data
on student
after insert
as
    declare @age int,
            @name varchar(20);
    select @name = s.name, @age = s.age from inserted s;
    if (@age < 18)
    begin
        raisError(&#39;插入新数据的age有问题&#39;, 16, 1);
        rollback tran;
    end
go
--test
insert into student values(&#39;forest&#39;, 2, 0, 7);
insert into student values(&#39;forest&#39;, 22, 0, 7);
select * from student order by id;

# Beispiel, Betriebsprotokoll

if (object_id('log', 'U') is not null)
    drop table log
go
create table log(
    id int identity(1, 1) primary key,
    action varchar(20),
    createDate datetime default getDate()
)
go
if (exists (select * from sys.objects where name = 'tgr_student_log'))
    drop trigger tgr_student_log
go
create trigger tgr_student_log
on student
after insert, update, delete
as
    if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))
    begin
        insert into log(action) values('updated');
    end
    else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
    begin
        insert into log(action) values('inserted');
    end
    else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
    begin
        insert into log(action) values('deleted');
    end
go
--test
insert into student values('king', 22, 1, 7);
update student set sex = 0 where name = 'king';
delete student where name = 'king';
select * from log;
select * from student order by id;