SQL Server 트리거

트리거는 이전에 소개한 저장 프로시저와는 다른 특별한 유형의 저장 프로시저입니다. 트리거는 주로 이벤트에 의해 트리거되며 자동으로 호출되어 실행됩니다. 저장 프로시저는 저장 프로시저의 이름으로 호출될 수 있습니다.

Ø 트리거란 무엇인가요?

트리거는 테이블을 삽입, 업데이트, 삭제할 때 자동으로 실행되는 특수 저장 프로시저입니다. 트리거는 일반적으로 더 복잡한 검사 제약 조건이 있는 제약 조건에 사용됩니다. 트리거와 일반 저장 프로시저의 차이점은 트리거가 특정 테이블에서 작동한다는 것입니다. 업데이트, 삽입, 삭제 등의 작업 중에 시스템은 테이블에서 해당 트리거를 자동으로 호출하고 실행합니다. SQL Server 2005의 트리거는 DML 트리거와 DDL 트리거의 두 가지 범주로 나눌 수 있습니다. DDL 트리거는 다양한 데이터 정의 언어 문에 영향을 미치며 트리거되는 문에는 create, alter 및 drop 문이 포함됩니다.

DML 트리거는 다음과 같이 구분됩니다.

1. 트리거 이후(다음에 트리거됨)

트리거 삽입

b. 업데이트 트리거

c. 삭제 트리거

2. 트리거 대신(이전에 트리거됨)

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

트리거에는 삽입 테이블(삽입된 테이블)과 삭제 테이블(삭제된 테이블)이라는 두 가지 특수 테이블이 있습니다. 이 두 가지는 논리 테이블과 가상 테이블입니다. 시스템은 메모리에 두 개의 테이블을 생성하고 이를 데이터베이스에 저장하지 않습니다. 또한 두 테이블 모두 읽기 전용이므로 데이터를 읽을 수만 있고 수정할 수는 없습니다. 이 두 테이블의 결과는 항상 수정된 트리거가 적용되는 테이블과 동일한 구조를 갖습니다. 트리거가 작업을 완료하면 두 테이블이 삭제됩니다. Inserted 테이블의 데이터는 삽입 또는 수정 후의 데이터이고, 삭제된 테이블의 데이터는 업데이트 또는 삭제 전의 데이터입니다.

데이터 업데이트 시 테이블 레코드를 먼저 삭제한 후 레코드를 추가합니다. 이런 방식으로 삽입된 테이블과 삭제된 테이블에 업데이트된 데이터 레코드가 있게 됩니다. 트리거 자체는 트랜잭션이므로 트리거의 수정된 데이터에 대해 몇 가지 특별한 검사를 수행할 수 있습니다. 만족스럽지 않은 경우 트랜잭션 롤백을 사용하여 작업을 취소할 수 있습니다.

Ø 트리거 생성

구문

create trigger tgr_name
on table_name
with encrypion –加密触发器
    for update...
as
    Transact-SQL
    # 创建insert类型触发器
--创建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;

삽입 트리거는 새로 삽입된 레코드를 삽입된 테이블에 추가합니다.

# 삭제 유형 트리거 생성

--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触发器会在删除数据的时候，将刚才删除的数据保存在deleted表中。
 
    # 创建update类型触发器
--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班';     update触发器会在更新数据后，将更新前的数据保存在deleted表中，更新后的数据保存在inserted表中。
 
    # update更新列级触发器
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;

업데이트 열 수준 트리거는 업데이트를 사용하여 열 레코드 업데이트 여부를 결정할 수 있습니다.

# 대신 Trigger

대신 정의된 작업(삽입, 업데이트, 삭제)을 실행하지 않고 트리거 자체의 내용만 실행한다는 의미입니다.

구문 생성

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

# 트리거 대신 생성

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;

# 사용자 정의 메시지 표시 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;

# 트리거 수정

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

# 트리거 활성화 또는 비활성화

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

# 생성된 트리거 정보 조회

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

# 예, 삽입된 데이터 확인

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;

# 예시, 작업 로그

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;