SQL Server: 트리거에 대한 자세한 설명

1. 개요

트리거는 명시적으로 호출할 수 없지만 테이블에 레코드가 삽입, 업데이트 또는 삭제될 때 자동으로 활성화되는 특수 저장 프로시저입니다. 따라서 트리거를 사용하여 테이블에 복잡한 무결성 제약 조건을 구현할 수 있습니다.

2. 트리거 분류

SQL Server2000은 "대신" 트리거와 "이후" 트리거라는 두 가지 유형의 트리거를 제공합니다.

테이블 또는 뷰의 각 수정 작업(삽입, 업데이트 및 삭제)에는 "대신" 트리거가 있을 수 있으며, 테이블의 각 수정 작업에는 여러 개의 "이후" 트리거가 있을 수 있습니다.

2.1 "대신" 트리거

"대신" 트리거는 실제 "삽입"이 수행되기 전에 실행됩니다. 테이블 외에도 뷰에서 "대신" 트리거를 사용하여 뷰가 지원할 수 있는 업데이트 작업을 확장할 수도 있습니다.

"대신" 트리거는 실행될 SQL 문을 대체합니다. 즉, 실행될 SQL 문이 "실제로 실행"되지 않는다는 의미입니다.

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

위의 예에서는 다음과 같이 정의합니다. "delete" 테이블에서 삭제할 학생을 출력하는 "triggerStudent_Delete" 트리거. "delete" 작업을 실행한 후 "student number = 4"인 학생이 트리거로 인해 삭제되지 않은 것을 확인할 수 있습니다. 학생 삭제'는 '학생 번호 = 4인 학생에서 삭제' 문을 대체하여 실행되지만 '학생 삭제 트리거'에서는 실제로 학생이 삭제되지 않습니다.

2.2 "이후" 트리거

"이후" 트리거는 삽입, 업데이트 또는 삭제 문이 실행된 후에 실행됩니다. "이후" 트리거는 테이블에서만 사용할 수 있습니다.

"이후" 트리거는 주로 테이블이 수정된 후(삽입, 업데이트 또는 삭제 작업 후) 다른 테이블을 수정하는 데 사용됩니다.

3. 삽입 및 삭제된 테이블

SQL Server는 각 트리거에 대해 두 개의 전용 테이블(삽입 테이블과 삭제 테이블)을 만듭니다.

이 두 테이블은 시스템에 의해 유지 관리되며 데이터베이스가 아닌 메모리에 존재하며 가상 테이블로 이해될 수 있습니다.

이 두 테이블의 구조는 항상 트리거에 의해 작동되는 테이블의 구조와 동일합니다.

트리거 실행이 완료되면 해당 트리거와 관련된 2개의 테이블도 함께 삭제됩니다.

삭제된 테이블에는 삭제 또는 업데이트 문을 실행한 결과 테이블에서 삭제될 모든 행이 저장됩니다.

Insert 테이블은 Insert 또는 Update 문 실행으로 인해 테이블에 삽입될 모든 행을 저장합니다.

4. 트리거 실행 프로세스

Insert, update 또는 delete 문이 제약 조건을 위반하면 SQL 문이 성공적으로 실행되지 않습니다. "이후" 트리거도 활성화되지 않습니다.

트리거를 실행한 작업 대신 '대신' 트리거를 실행할 수 있습니다. 삽입된 테이블과 삭제된 테이블이 방금 생성되고 다른 작업이 발생하지 않은 경우 실행됩니다. "대신" 트리거는 제약 조건 이전에 실행되므로 제약 조건의 일부 전처리를 수행할 수 있습니다.

5. 트리거 생성

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

6. 트리거 수정:

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

drop trigger trigger_name

select * from sysobjects where xtype='TR'

exec sp_helptext '触发器名'

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

10.1 "Order" 테이블에 트리거를 생성합니다. "Order" 테이블에 주문 레코드를 삽입할 때 "Product" 테이블의 제품 상태 "Status"가 1(정렬 중)인지 확인합니다. 주문은 "주문" 테이블에 추가될 수 없습니다.

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

이 예에서는 "pid"가 상품 코드입니다.

엄밀히 말하면 이 예의 if 판단은 부정확합니다. "Order" 테이블에 한 번에 하나의 레코드가 삽입되면 이 판단에는 문제가 없으며, 여러 레코드가 동시에 삽입되면 "select status"는 여러 행을 반환합니다.

10.2 주문이 추가될 때 "제품" 테이블의 해당 제품 기록에서 재고를 줄이려면 "주문" 테이블에 삽입 트리거를 만듭니다.

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