Detailed explanation of SQL Server triggers

Triggers are special stored procedures that are automatically executed when inserting, updating, and deleting tables. Triggers are generally used on constraints with more complex check constraints. The difference between triggers and ordinary stored procedures is that triggers operate on a certain table. During operations such as update, insert, and delete, the system will automatically call and execute the corresponding trigger on the table. Triggers in SQL Server 2005 can be divided into two categories: DML triggers and DDL triggers. DDL triggers affect a variety of data definition language statements and are triggered. These statements include create, alter, and drop statements.

DML triggers are divided into:

1, after trigger (triggered after)

a, insert trigger

b, update trigger

c, delete trigger

2, instead of trigger (before) Trigger)

The after trigger requires that the trigger will be triggered only after a certain operation insert, update, or delete is performed, and it can only be defined on the table. Instead of a trigger, it means that it does not execute its defined operations (insert, update, delete) but only executes the trigger itself. Instead of triggers, you can define them on the table or on the view.

Triggers have two special tables: insert table (instered table) and delete table (deleted table). These two tables are logical tables and virtual tables. The system creates two tables in memory and will not store them in the database. Moreover, both tables are read-only, and data can only be read but not modified. The results of these two tables always have the same structure as the table to which the modified trigger applies. When the trigger completes its work, the two tables are deleted. The data in the Inserted table is the data after insertion or modification, while the data in the deleted table is the data before update or deletion.

Operations on tables

Inserted logical table

Deleted logical table

Increase records (insert)

Storage added records

None

Delete records (delete)

None

Storage deleted records

Modify records (update)

Storage updated records

Storage records before update

When updating data, you first delete the table record and then add a record. In this way, there will be updated data records in the inserted and deleted tables. Note that the trigger itself is a transaction, so some special checks can be performed on modified data in the trigger. If it is not satisfied, you can use transaction rollback to undo the operation.

Ø Create trigger

Syntax

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

# Create insert type trigger

--创建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;

insert trigger will add a newly inserted record in the inserted table.

# Create a delete type 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;

delete trigger will save the just deleted data in the deleted table when deleting data.

# Create an update type 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班';

The update trigger will save the pre-update data in the deleted table after updating the data, and save the updated data in the inserted table.

# update update column-level trigger

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 column-level trigger can use update to determine whether to update column records;

# instead of type trigger

instead of trigger means that its definition is not executed The operation (insert, update, delete) only executes the content of the trigger itself.

Create syntax

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

# Create instead of trigger

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;

# Display custom message raiseerror

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;

# Modify trigger

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

# Enable , disable trigger

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

# Query the created trigger information

--查询已存在的触发器
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';

# Example, verify the inserted data

   
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;

# Example, operation log

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;