ms-sql trigger 에 대한 내용

** Trigger (트리거)**

트리거는 성능향상을 위한 도구라기 보다는, 비즈니스 규칙을
효과적으로 데이터베이스 서버 계층에 구현하게 해 주는 기능.
수동으로 여러 가지 작업을 하나의 트랜잭션으로 묶어서 실행
해야하거나, 함께 이루어져야 할 일련의 작업들을 논리적으로
묶어야 할 필요가 있을 때는 트리거보다 더 좋은 대안은 없다.
사실 트리거를 쓰면 간단하게 해결될 것을, 응용 프로그래밍
차원에서 엄청난 코딩량으로 승부하거나, 아니면 서버에서
처리하더라도 복잡한 SQL 문을 사용해서 그것도 약간은
허술한 논리로 구현하는 경우가 너무 많다.


1) 트리거 사용 이유
트리거란 “자동으로 실행되도록 정의된 특수한 저장 프로시저”이다.
실제로 트리거는 특수한 형태의 저장 프로시저이다.
트리거 내에 정의할 수 있는 명령문에 적용되는 규칙은 저장프로시저와
같다. 트리거는 사전적인 의미로 방아쇠라는 뜻이고, 연쇄반응이라는
뜻도 가지고 있다. 즉 어떤 트랜잭션이 일어나면 거기에 반응해서,
다른 명령을 실행하게 하는 기능을 하는 것이 트리거이다.
트리거는 Insert, Update, Delete 문에 대한 응답으로 자동 호출된다.
다른 명령에 대해서는 반응하지 않는다. 이러한 트리거는 언제 사용하며,
트리거를 사용해야 하는 이유는 무엇인가? 일단 그 이유는 다음과 같다.

1] 업무 규칙(Business Rule)을 보장
2] 업무처리 자동화
3] 데이터 무결성 강화( >= Check Constraint )

트리거를 사용하면, 비즈니스 룰을 서버 계층에서 처리할 수 있으며,
트랜잭션에 의해 자동으로 다른 명령을 일으킴으로써 업무 처리를
자동화할 수 있다. 사용자가 중간에 개입하지 않아도 구현된 규칙대로
알아서 연속적으로 실행된다는 것이다. 트리거는 또한 CHECK 제약
조건보다 더 강화된 형태로 데이터의 무결성을 강화할 수 있다.


2) 트리거가 할 수 있는 일
트리거는 INSERT,UPDATE,DELETE 작업에 대한 반응으로 발생되는데,
이 때 트리거 내에서 할 수 있는 작업은 다음과 같다.
트리거내의 명령문들 또한 저장 프로시저와 마찬가지로 일련의 배치로 수행된다.

1] 해당DB의 모든 테이블에 대한 수정작업을 할 수 있다.
2] 참조 무결성을 위반하는 수정을 허용하지 않거나 롤백 함으로써,
시도한 데이터 수정(트랜잭션)을 취소한다.
3] CHECK 제약 조건으로 정의된 것보다 더 복잡한 제약 조건을 강제로
실행할 수 있다.
4] 데이터 수정 전후의 테이블 상태의 차이점을 찾아내고 이 차이점에 따라
작업을 수행할 수 있게 한다.


3) check 제약과의 비교

트리거는 무결성 강화 측면에서 check 제약과 자주 비교되곤 한다.
트리거는 check 제약이 할 수 있는 모든 제약을 지켜줄 수 있기 때문이다.
트리거는 그리고, check 제약보다 더 강화되고 나은 무결성 강화방법이다.
트리거는 check 제약과 어떤 점이 다른가. 다음을 보자.

1] 더 복잡한 제한을 적용할 수 있다
2] 트리거는 체크제약의 모든 기능을 제공한다
3] 트리거는 다른 테이블의 컬럼 값 확인도 가능하다
4] 사용자 정의 오류메시지 사용가능


use dbsys
go
-- 트리거 생성
create trigger trg_cust_ins
on cust for insert
as
select top 1 * from cust
order by cid desc
go

-- insert 시 트리거 작동 확인
insert cust(cname, ctel, csex) values ('트리거', '(051)000-9999', '남')
select * from cust
sp_help cust

여기서는 CID 컬럼의 특성을 이용했다. CID 컬럼은 자동증가 컬럼이다.
초기값 1부터 시작해서 1씩 자동으로 증가하고 사용자는 이 값을
수동으로 입력하지 않는다. 그렇다면 현재 입력한 데이터의 CID 값은
모든 CID 값 중 가장 큰 값이라는 추측을 할 수 있으며,
그러한 특성을 이용해서 CID 컬럼을 내림차순 정렬해서 제일 위의 값
하나만을 불러오는 형식으로 해결한 것이다.
그러나 이 방법은 문제점을 안고 있다.
우선 SET IDENTITY_INSERT 옵션을 ON 으로 설정해서 중간의 빈
데이터를 입력할 때는 데이터가 출력되지 않을 것이라는 점이
있으며, 성능 면으로 볼 때도 실행계획을 작성해야 하거나,
디스크를 직접 엑세스하여 데이터를 검색하는 방법을 사용한다는 것이다.
이 문제는 INSERTED 라는 특수한 테이블을 사용하면 해결이 된다.


1) INSERTED, DELETED 테이블

두 테이블은 트리거 내에서만 메모리에 존재하는 특별한 테이블이다.
트리거 밖에서는 사용될 수 없다.

INSERTED 테이블은 INSERT 작업 시 일어난 트리거 내에서, 메모리에
방금 입력한 데이터만을 갖고 있는 테이블이다. 이 테이블을 사용 시에는
디스크를 엑세스하지 않고 메모리를 엑세스함으로써 비교할 수 없는
성능상의 이점을 제공한다.

drop trigger trg_cust_ins
go

create trigger trg_cust_ins
on cust for insert
as
select * from inserted
go

insert cust(cname, ctel, csex) values('홍길동', '(051)000-0000', '남')
go

위와 같은 방법을 쓰면, 데이터 규칙과 상관 없이 끄때 그때 입력한
값만을 사용해서 제어할 수 있으며, 성능 또한 디스크를 직접 엑세스하지 않고
메모리에서 I/O를 일으키기 때문에 훨씬 향상된다.

-- 트리거 내에서 입력한 데이터가 있는지 여부를 확인한 다음, 있다면 입력한
-- 데이터를 출력하고 없으면 아무것도 실행하지 않는 트리거 예제
create table trg_ed
(id int, name char(6))
go

select * from trg_ed

create trigger trg_show_insert
on trg_ed for insert
as
if exists (select * from inserted)
begin
select * from inserted
end
go

insert trg_ed values(1, 'john')

트리거는 기본설정으로, 입력이 성공하지 않았다면 INSERT 트리거는 일어나지
않는다. 그러므로 입력이 실패했다면 위의 트리거가 발생할 것이고 어차피
IF 문에서 TRUE 값을 돌려줄 것이기 때문에 “SELECT * FROM INSERTED"는 실행된다.


create trigger trg_del_ed
on trg_ed for delete
as
select * from deleted
go

delete trg_ed where id = 1
select * from trg_ed

보통 입력할 때에는 한 행씩 입력작업이 일어나게 된다. 일반적인 응용프로그램
사용 시에는 대부분 그렇다. "SELECT.. INTO..." 구문이나, “INSERT...SELECT..."구문
같이 대량으로 데이터가 입력되는 경우는 일반적인 응용프로그램에서는 자주
일어나는 작업이 아니다.
그러나 삭제 시에는 여러 행의 데이터가 한꺼번에 삭제되는 경우가 많다.
여러 데이터가 하나의 ”DELETE" 문으로 삭제되었다면, SQL Server 에 할당된
사용 가능 메모리만큼 DELETED 테이블의 데이터를 가질 수 있다.
DELETE 작업을 실행했을 때, 삭제된 데이터를 이용해 제어를 하고자 할때,
DELETED 테이블을 사용할 수 있다. 역시 메모리에 존재하며, 지운 데이터만
존재하므로 디스크를 엑세스하는것과는 비교되지 않을 정도로 빠른 엑세스를 하게 한다.


create table trg
(id int, name varchar(10))
go

insert trg(id, name) values (1, 'sam')
go
select * from trg

create trigger trg_update
on trg for update, insert, delete
as
if exists (select * from deleted)
begin
select * from deleted
print 'data was deleted successfully'
end
if exists (select * from inserted)
begin
select * from inserted
print 'data was inseted successfully'
end
go
-- drop trigger trg_update

update trg
set id = 2, name = 'mark'
where id = 1
go
select * from trg

“ UPDATE = DELETE + INSERT "
UPDATED 테이블은 존재하지 않으며, DELETED, INSERTED 두 테이블을 사용할 수 있으므로,
오히려 UPDATE 에 대한 좀 더 세부적인 제어가 가능해진다.


create trigger trg_notice_todirector
on trg for update
as
declare @prev varchar(255), @next varchar(255), @docu varchar(255)
set @prev = (select convert(varchar(10), id) + '-' + name from deleted)
set @next = (select convert(varchar(10), id) + '-' + name from inserted)
set @docu = 'net send dbsys ' + @prev + ' 가 ' + @next + ' 로 변경되었습니다.'
exec master..xp_cmdshell @docu
go

update trg
set id = 3, name = 'barbie'
go

--drop trigger trg_notice_todirector


2) UPDATE 트리거

이번에는 DELETED 와 INSERTED 테이블을 이용해서, 데이터 수정에 대한 기록을
기록 테이블에 입력하는 예제로 들어본다. 목적테이블은 고객 (CUST)테이블이며,
기록될 대상 테이블은 아래와 같다.

-- 고객테이블에 데이터 변경이 일어나면 무조건,
-- 바뀌기전 데이터와 바뀐후 데이터를, 기록테이블에 저장하는 트리거
create trigger trg_cust_update_history
on cust for update
as
-- 수정 후 데이터 입력
insert cust_history(cid, cname, ctel, csex, ceid, cdate)
select cid, cname, ctel, csex, eid, getdate()
from cust
where cid = (select cid from inserted)
-- 수정 전 데이터 입력
insert cust_history(cid, cname, ctel, csex, ceid, cdate)
select cid, cname, ctel, csex, eid, getdate()
from deleted
go

select * from cust

현재 CUST_HISTORY 테이블은 존재하지 않는다. 그러나 트리거 생성이
잘 되는 이유는 “지연된 이름 확인” 규칙 때문이다. 개체가 존재하지
않아도 트리거는 잘 만들어지며, 에러는 실제 실행 시에 일어난다.

create table cust_history
(hid int primary key identity, cid int, cname varchar(10), ctel char(14), csex char(2),
ceid int, cdate datetime)
go

update cust
set cname = '조규찬', eid = 10
where cid = 1
go

select * from cust_history


** UPDATE 트리거에서만 할 수 있는, 특정 컬럼에 대한 수정조건 설정
create table black_list
(id int primary key identity(1,1), name sysname, c_date datetime)
go

-- 연봉컬럼을 수정하려 했다면, 그 작업을 취소하고,
-- 블랙리스트 테이블에 그 사용자와 작업시간을 입력하는 트리거 생성
create trigger trg_upd_sal
on emp for update
as
if update(sal)
begin
rollback
insert black_list values (user_name(), getdate())
end
go

-- select user_name() : 소유자 표시

update emp
set sal = 300000
where eid = 1
go

select * from black_list
select * from emp where eid = 1

-- drop table black_list
-- drop trigger trg_upd_sal

예상대로 트랜잭션은 롤백되었으며, 시도했던 기록이
BLACK_LIST 테이블에 입력되었다.

그런데, ROLLBACK TRANSACTION 문은 BEGIN TRANSACTION 문과
호응하도록 되어있다. 그러나 이 트리거 내에는 BEGIN TRANSACTION 문을
실행한 곳이 없다. 그리고 SET IMPLICIT_TRANSACTIONS 도 현재 ON 으로
설정된 상태가 아니다.


4) 트리거 내의 트랜잭션 제어

트리거를 호출했을 때는, SET IMPLICIT_TRANSACTIONS ON을 실행했든 하지 않았든,
암시적 트랜잭션이 적용된다. 즉 암시적 트랜잭션 옵션에 관계없이 트리거 내에서는
트랜잭션이 진행중이라는 것을 의미한다.
이 암시적 트랜잭션은 트리거가 종료될 때 같이 COMMIT 된다.
특정 조건에 따라 얼마든지 ROLLBACK을 할 수 있다. COMMIT 문은 따로 써줄 필요없다.

트리거 내에서 ROLLBACK 문이 설정되면 다음과 같은 규칙이 적용된다.
1] 트리거에 의해 만들어진 트랜잭션을 포함하여, 해당 시점까지의 모든 데이터
수정 내용이 롤백된다. 트리거 내에서 만들어진 트랜잭션이 있다면 그것은
이미 암시적 트랜잭션이 진행 중에 설정된 것이므로 중첩트랜잭션이 된다.
create trigger trg_tran
on cust for delete
as
begin tran
select @@trancount
rollback tran
go
-- @@trancount : 현재 연결에서 사용 중인 트랜잭션 수를 반환합니다.

delete cust where cid = 1
go
select * from cust where cid =1
go

2] ROLLBACK 문 뒤에 어떤 명령문이 트리거 내의 배치에 있다면, 이 남은 명령문들은
계속 실행된다. 이 남은 명령문들이 데이터를 변경하면, 그 수정내용은 롤백되지
않고 변경이 적용된다. 이것은 트리거 내에서 롤백 시에 조심해야 할 일이다.
그리고, 배치 내에서 트리거를 발생시킨 명령문 뒤의 명령문은 하나도 실행되지
않는다는 일반적인 규칙이 적용된 것이다.

create proc up_deltrigger
as
declare cur_for_deltrg cursor for
select name from sysobjects where type = 'TR'
open cur_for_deltrg
declare @trgname sysname
fetch next from cur_for_deltrg into @trgname
while (@@fetch_status <> -1)
begin
exec('drop trigger ' + @trgname)
fetch next from cur_for_deltrg into @trgname
end
print '데이터베이스 내의 모든 트리거가 삭제되었습니다.'
deallocate cur_for_deltrg
go

exec up_deltrigger
go
select * from sysobjects where xtype = 'TR'
--drop proc up_deltrigger


create trigger trg_tran
on cust for delete
as
declare @cid table(id int) --데이터 타입 table
insert @cid(id) select cid from deleted
begin tran
select @@trancount
rollback tran
select * from @cid
go

delete cust where cid =1
go
select * from cust where cid = 1

--롤백은 처리되었지만, 그 이후의 SELECT 문은 실행되는 것을 확인할 수 있다.

3] 트리거 내의 ROLLBACK 명령은 해당 배치에서 선언하고 열린 모든 커서를
닫고 할당을 취소한다. 여기에는 트리거를 발생시킨 저장 프로시저에서
선언되고 열린 커서가 포함된다. 트리거를 발생시킨 일괄 처리 전의
일괄 처리에서 선언된 커서는 닫히기만 한다.
단, STATIC 또는 INSENSITIVE 커서는 다음과 같은 경우 열려 있다.

- CURSOR_CLOSE_ON_COMMIT이 OFF로 설정된 경우
- 정적 커서가 동기화되거나 비동기 커서로 완전히 채워진 경우

4] 트리거에서 부분 롤백을 수행하려면 SAVE TRANSACTION 문을 사용하면 된다.
SAVE TRANSACTION 문은 항상 자동 커밋 모드에서 호출된다.

4) 처리작업 자동화 (업무규칙 작성)
트리거는 특정 업무의 처리 작업을 자동화할 수 있다. 재고 시스템에서
업데이트 트리거를 사용하면 언제 재고 수준이 재주문 시점에 도달하는지
검색하여 공급자에게 자동으로 주문할 수 있게 해 줄 수 있다.
또 공장에서 프로세스를 기록하는 데이터베이스에 트리거를 사용하면,
프로세스가 정의된 안전 수준을 초과할 때 운영자에게 전자 메일을 보내거나
무선 호출할 수도 있다.


5) 생성 시 고려사항

1] 하나의 테이블에는 여러 개의 트리거가 있을 수 있다.
2] 트리거는 현재 DB 밖의 개체를 참조할 수 있지만, 생성은 현재 DB에서만 가능하다.
-- 생성불가
use pubs
go
create trigger trg_pubs
on dbsys..cust for delete
as
select * from deleted
go
-- as 절 이하에서 참조가능
use dbsys
go
create trigger trg_dbsys
on cust for delete
as
select * from pubs..employee
go
-- 삭제된 행이 없어도 트리거 출력됨
delete cust where cid = 20

3] 생성권한은 테이블 소유자에게 있으며, 다른 사용자에게 넘길 수 없다.
exec sp_changeobjectowner 'trg_dbsys', 'dbsystrg_user'

4] 임시 테이블이나 시스템 테이블에서 트리거를 만들 수는 없다.
--시스템 테이블에 기반하지 못한다.
create trigger trg_message_notice
on sysobjects for insert
as
select * from inserted
go

--임시 테이블에도 생성 불가
create table #temp(tid int primary key, tname varchar(20))
go
create trigger trg_temp
on #temp for insert
as
select * from inserted
go

5] Delete나 UPDATE 작업으로 정의된 외부키가 있는 테이블에는,  
Instead Of Delete  및  Instead Of Update  트리거를 정의할 수 없다.

6] Truncate Table 문은 로깅되지 않으므로, DELETE 트리거를 발생시킬 수 없다.

create table trunc_01 (tid int, tname varchar(10))
go
insert trunc_01(tid, tname) values(1, 'aaa')
insert trunc_01(tid, tname) values(2, 'bbb')
go
--select * from trunc_01
create trigger trg_trunc
on trunc_01 for delete
as
select * from deleted
go
truncate table trunc_01 -- delete 트리거는 발생하지 않는다.
select * from trunc_01


7] WRITETEXT 문은 INSERT 또는 UPDATE 트리거를 시작할 수 없다.

8] 테이블은 주어진 유형의 Instead Of 트리거를 하나만 가질 수 있다

9] View 를 참조하는 트리거는 만들 수 없다.
create view v_emp
as
select ename, job, edate from emp
where deptno = 10
go
create trigger trg_on_view
on v_emp for insert
as
select '뷰에 트리거가 만들어지나?'
go

10] 순서 지정
하나의 테이블에 여러 트리거가 설정되어 있을 때는 그 발생되는 순서를
지정할 수 있다. 그러나 전부 설정할 수 있는 것이 아니고, 첫 번째와
마지막 트리거만을 지정할 수 있을 뿐이다.
create trigger trg_order1
on emp for update
as
select * from emp where eid=1
go
create trigger trg_order2
on emp for update
as
select * from emp where eid=2
go
create trigger trg_order3
on emp for update
as
select * from emp where eid=3
go
create trigger trg_order4
on emp for update
as
select * from emp where eid=4
go
create trigger trg_order5
on emp for update
as
select * from emp where eid=5
rollback tran
go

exec sp_settriggerorder @triggername = 'trg_order5', @order='first', @stmttype='update'
go
exec sp_settriggerorder @triggername = 'trg_order1', @order='last', @stmttype='update'
go

update emp
set sal = 1000000
where eid = 1
go

-- 첫번재 트리거인 trg_order5에서 rollback이 지정되어 더이상 다른 트리거 실행 안됨.

11) 트리거 내에서 다음 문들은 실행되지 않는다.

- ALTER DATABASE
- CREATE DATABASE
- DISK INIT
- DISK RESIZE
- DROP DATABASE
- LOAD DATABASE
- LOAD LOG
- RECONFIGURE
- RESTORE DATABASE
- RESTORE LOG

12) 트리거를 삭제해도 트리거가 정의된 테이블 및 데이터는 영향받지 않는다.
또한 테이블이 삭제되면 해당 테이블에 설정된 모든 트리거도 삭제된다.
이것은 인덱스와도 비슷하다. 트리거를 삭제할 수 있는 기본권한은 트리거가
설정된 소속 테이블의 소유주에게 있다.

13) 트리거 비활성화
특정 트리거를 삭제하지 않고 비활성화를 할 수 있다.
exec sp_helptrigger emp

alter table emp
disable trigger trg_order1

alter table emp
disalbe trigger all


3. 다중 행 고려사항
하나의 행이 변경되어 트리거가 발생될 때는 같은 규칙이 적용되지만, 여러 행이 변경되어
트리거를 발생시킬 때는 고려해야 할 사항이 있다.
여러 행이 영향을 받는 경우는 다음 세 가지이다.

- INSERT...SELECT.. 문 실행 시
- UPDATE 시
- DELETE 시

여러 행이 변경되어서 트리거를 발생시킬 때라도, 트리거는 단 한번만 발생한다.
예를 들어 DELETE 문을 실행해서 10개의 데이터가 삭제되었다고 하더라도,
DELETE 트리거는 한 번만 발생하게 된다는 뜻이다.

create table goods
(gid int primary key identity, goods varchar(30), gtot int)
go

insert goods(goods, gtot) values ('새우버거',0)
insert goods(goods, gtot) values ('징거버거',0)
insert goods(goods, gtot) values ('더블와퍼',0)
go

create table sales
(sid int primary key identity, sqty int,
sgid int foreign key references goods(gid))
go

create table sales_source
(sid int primary key identity, sqty int,
sgid int foreign key references goods(gid))
go

insert sales_source(sqty, sgid) values (3,1)
insert sales_source(sqty, sgid) values (4,2)
insert sales_source(sqty, sgid) values (5,3)
insert sales_source(sqty, sgid) values (3,1)
insert sales_source(sqty, sgid) values (4,2)
insert sales_source(sqty, sgid) values (5,3)
go

select * from goods
select * from sales_source
select * from sales

햄버거 판매점포라고 생각을 해보자. 상품의 종류에는 세 가지 종류가 있고,
상품에 대한 정보는 GOODS 테이블에 입력되어 있다. 그리고 GOODS 테이블에서,
각 상품별 총 판매개수를 저장하는 GTOT 컬럼값은 현재 각각 0이다.
현재 판매기록 테이블인 SALES 테이블은 만들어져 있지만, 입력된 데이터는 없다.
판매건이 없다는 얘기다.
SALES_SOURCE 테이블은 원 판매 테이블이다. 이 테이블은 사실 SALES
테이블의 다른 버전이다. 같은 판매 테이블인데, 지사 판매테이블이라고 보면 되겠다.

이 SALES_SOURCE 테이블의 데이터는 SALES 테이블(본사 판매테이블로 생각하자)로
입력될 데이터 들이다. 이 때 우리는 INSERT...SELECT... 절을 사용해서
원 판매 테이블인 SALES_SOURCE 테이블에서 SALES 테이블로 데이터를
입력하게 될 것이다. 이것은 한번에 6개의 데이터를 입력하는 상황이 된다.
여기에 먼저 데이터가 없는 빈 테이블인 SALES 테이블에 데이터를 입력해 보자.

create trigger trg_tot_update
on sales for insert
as
update goods
set gtot = gtot + sqty
from inserted
where goods.gid = inserted.sgid
go

insert sales(sqty, sgid) select sqty, sgid from sales_source
go

select * from goods
select * from sales_source
select * from sales

SALES 테이블에는 6개의 데이터가 입력이 된다. 그러나 GOODS 테이블의
데이터는 변경은 되었으되 제대로 변경되지 않았다. 원래 총 판매개수가
각각 0,0,0 개 이었던 것이 현재는 결과창에서 보듯, 3,4,5 로 바뀐 것이다.
그러나 SALES 테이블을 확인해 보라. 판매 총 합계는 각각 6,8,10 개가 팔렸는데,
총 합계는 한 번씩 밖에는 더해지지 않은 것이다. 위의 방법은 다중행 처리 시에는
맞지 않는 방법이다. 단일 행 처리에서만 가능한 방법인 것이다.

-- drop trigger trg_tot_update
create trigger trg_tot_update1
on sales for insert
as
update goods
set gtot = gtot + (select sum(sqty) from inserted where goods.gid = sgid)
where goods.gid in (select sgid from inserted)
go

insert sales(sqty, sgid) select sqty, sgid from sales_source
go

select * from goods
select * from sales
select * from sales_source

현재 GOODS 테이블의 GTOT 컬럼은 위의 쿼리 INSERT...SELECT... 절
실행으로 인해서, 각각 3,4,5 라는 값을 가지고 있다. 여기에 다시
한번 INSERT..SELECT 절을 실행하니, 이번에는 각각 6,8,10 이라는
총 판매개수를 더해서 9,12,15라는 통계를 보여주고 있다.
이제야 제대로 트리거가 작동한 것이다.
위의 트리거 내의 Update 문의 SELECT 문은 상관 쿼리이다.
서브쿼리와 외부쿼리를 조인해서 상관쿼리로 작성함으로써, SALES_SOURCE
테이블에 있는 각 종류마다 그리고 모든 데이터에 대해 합계를 계산하게 된 것이다.
이런 식으로 다중 행을 처리할 때에는 상관 쿼리를 사용해서 처리를 해야 옳다.

그런데 문제가 하나 더 남아있다. 방금 전과 같은 상황에서 단일 행이 입력될
것인지 다중 행이 입력될 것인지를 예측하기가 힘들다는 것이다.
상관쿼리를 사용하면 그 만큼 성능이 느려진다.
그러므로, 두 경우에 따라서 단일 행 처리방법과 다중 행 처리방법을 제어하는
것이 좋겠다.

--입력 형태를 고려한 최종 처리 방법
create trigger trg_tot_update2
on sales for insert
as
if @@rowcount = 1
begin
update goods
set gtot = gtot + sqty
from inserted
where goods.gid = inserted.sgid
end
else
begin
update goods
set gtot = gtot + (select sum(sqty) from inserted
where goods.gid = inserted.sgid)
where goods.gid in (select sgid from inserted)
end
go

select * from goods
select * from sales
select * from sales_source


4. 중첩 트리거, 재귀 트리거

1) 중첩 트리거(Nested Trigger)
트리거는 트리거를 발생시킨 테이블과 상관없이 중첩해서 일어날 수 있다.

create table trg_01 (id int, name varchar(50))
create table trg_02 (id int, name varchar(50))
create table trg_03 (id int, name varchar(50))


-- trg_01 테이블에 입력되면, 그 데이터를 trg_02 테이블에 입력
create trigger trg_insert_02
on trg_01 for insert
as
insert trg_02(id, name) select id, name from inserted
go

-- trg_02 테이블에 입력되면, 그 데이터를 trg_03 테이블에 입력
create trigger trg_insert_03
on trg_02 for insert
as
insert trg_03(id, name) select id, name from inserted
go

insert trg_01(id, name) values (3, '03 테이블에 입력됨')
go
select * from trg_01
select * from trg_02
select * from trg_03

중첩될 수 있는 최대 레벨은 32 레벨이다.
또한 처음부터 중첩레벨이 32레벨이상인 것이 감지되면, 아예 해당
트리거는 하나도 실행되지 않는다. 트리거의 중첩도는 @@nestelevel 함수로
알 수 있으며, 이 함수로 중첩레벨 제어도 가능하다.


2) 재귀 트리거 (Recursive Trigger)
재귀 트리거는 중첩 트리거의 한 종류이다. 재귀 트리거는 반복해서
무한 루핑을 돌면서 일어나는 트리거를 말한다. 이것은 처음에 실행한
트리거로부터 시작된 트리거가 다시, 처음에 실행한 자신의 트리거를 호출할 때
발생된다.
재귀트리거는 다시 간접 재귀 트리거와 직접 재귀 트리거로 나누어진다.

* 직접 재귀 트리거 : A->A
* 간접 재귀 트리거 : A->B->C->A

1] 간접 재귀 트리거

create trigger trg_inset_01
on trg_03 for insert
as
insert trg_01(id, name) select id, name from inserted
go

insert trg_01 values (5, '각 테이블에 입력된당')
go

-- EM DB옵션 확인

--(1) DB option으로, 순환트리거 허용 후 다시 실행.
sp_dboption 'dbsys', 'recursive triggers', 'true'

--(2) sql server 2000 에서는 다음과 같은 방법도 지원한다.
-- (1)번의 방법은 다음 버젼 부터는 지원하지 않음
alter database dbsys
set recursive_triggers on
go

-- 옵션 설정 확인, 1이면 성정됨
select databasepropertyex ('dbsys', 'isrecursivetriggersenabled')

-- 옵션은 설정 되었지만 중첩레벨 32를 넘어 실행 불가

insert trg_01 values (5, '각 테이블에 입력된당')
go


2] 직접 트리거
직접 트리거는, 발생한 트리거가 다시 자신을 발생시키는
상황을 말한다

create table eee(id int, name varchar(10))
go

insert eee values(111, 'aaa')
go

create trigger tt
on eee for update, insert
as
insert eee values(222, 'bbb')
go

update eee
set name = 'ccc'
where id = 111
go

역시 중첩수준 32 레벨을 벗어나므로 실행되지 않는다.

3) 중첩 레벨 제어
중첩 레벨을 제어하여 32레벨을 초과하기 전에 트리거를 빠져나가게 하여,
재귀트리거를 발생시키도록 해 보는 방법을 설명한다.
이 때 가장 중요한 것은 @@nestlevel 이다. IF 문을 사용하여 @@nestlevel 이
32가 되기 전에 트리거를 종료시킬 수 있다.

delete trg_01
delete trg_02
delete trg_03

alter trigger trg_insert_03
on trg_02 for insert
as
if @@nestlevel < 32
begin
insert trg_03(id, name) select id, name from inserted
end
go

insert trg_01 values (5, '각 테이블에 반복 입력 된다.')
go

select count(*) from trg_01
union all
select count(*) from trg_02
union all
select count(*) from trg_03


5. INSTEAD OF TRIGGER
트리거는 일반적으로 어떤 트랜잭션(INSERT,UPDATE,DELETE)이 일어나면
발생하게끔 되어 있다. 만약 트랜잭션이 시도되었으나 제약조건이라든지
구문분석에서 에러가 발생한다면, 아예 트리거는 일어나지 않는다.
이것을 가리켜서 AFTER TRIGGER 라고 한다. 이것이 기본설정 값이다.

그런데, SQL Server 2000 에서는 이전 버전과 다르게 좀 더 세밀하게 트리거
발생 시기를 제어할 수 있는 기능이 추가되었다.
이것이 INSTEAD OF TRIGGER 이다. 이 INSTEAD OF TRIGGER 는 트리거가
발생하기 전에 다른 작업을 하라는 얘기와도 같다.
그래서 설정되어 있던 트리거는 실제로는 발생하지 않고, 대신
INSTEAD OF TRIGGER의 AS절 이하에서 정의된 다른 작업이 실행된다.

create table 제품정보
(상품아이디 int primary key identity(1,1), 색상 varchar(10), 재질 varchar(10),
색상재질 as (색상 + 재질))
go

create view insteadview
as
select 상품아이디, 색상, 재질, 색상재질 from 제품정보
go

select * from insteadview

만약 뷰에다 데이터를 입력하려고 한다면 이것을 실패하고 에러를 낸다.
기본키는 자동증가(identity) 속성, 계산된 컬럼... 사용자 입력 불가
그래서 사용자가 뷰에 값을 지정해서 입력을 하게 되면 실패한다.
한편 그런 이유 때문에 아예 지정을 하지 않으면, 그 때도 에러를 발생하게 된다.
지정을 하지 않는 것은 null 값을 넣겠다는 의미이며, 위의 두 컬럼
(상품아이디, 색상재질)에 null 값을 입력할 수는 없는 일이기 때문이다

insert insteadview (상품아이디, 색상, 재질, 색상재질)
values (1, 'blue', 'cotton', 'aaa')

insert insteadview (색상, 재질) values ('blue', 'cotton')

이러한 상황에서는 INSTEAD OF TRIGGER를 이용해서 뷰를 통해서 값을 입력하는
대신 트리거를 통해서 직접 테이블에 데이터를 입력하게 하여, 데이터 입력을
가능하게 할 수 있다.

create trigger insteadtrigger
on insteadview instead of insert
as
begin
insert into 제품정보 select 색상, 재질 from inserted
end
go

insert insteadview (상품아이디, 색상, 재질, 색상재질)
values (1, 'blue', 'cotton', 'aaa')
-- 입력 가능
insert insteadview (색상, 재질) values ('blue', 'cotton')
-- 기본키의 not null 제약 위반

위에서 정의한 트리거는, 뷰에다가 입력작업을 해도 그 작업대신 트리거
내에서 명기한 작업을 실행하게 하는 것이다.

트리거내의 입력작업은 실제 테이블에 대한 입력작업이므로 문제될 것이 없다.
다만 뷰에 입력을 할 때, 기본키에 NULL값을 입력하게 되면, 그 값을 그대로
받아들이게 되므로 실제 테이블에도 NULL을 입력하는 결과를 낳게 되어
입력을 실패한다. 대신 아무 값이라도 입력만 하게 되면, 그 값은 트리거 내에서
무시되므로 제대로 데이터가 입력되게 된다.

뷰에는 트리거를 만들 수 없지만, INSTEAD OF TRIGGER에서 만큼은 가능하다.
INSTEAD OF TRIGGER는 몇 가지 종류가 있다.

INSTEAD OF INSERT TRIGGER
INSTEAD OF UPDATE TRIGGER
INSTEAD OF DELETE TRIGGER

이중에 INSTEAD OF INSERT TRIGGER 가 가장 많이 쓰이며,
INSTEAD OF DELETE TRIGGER 는 거의 쓰이지 않는다.

create trigger insteadofupdate
on insteadview
as
begin
update 제품정보
set 색상 = (select 색상 from inserted), 재질 = (select 재질 from inserted)
where 상품아이디 in (select 상품아이디 from inserted)
end
go

update insertedview
set 상품아이디 = 5, 색상 = 'red', 색상재질 = 'cottons'
where 상품아이디 = 1
go

update insertedview
set 색상 = 'red', 색상재질 = 'cottons', 재질 = 'jean'
where 상품아이디 = 1
go

select * from 제품정보

-- 계산된 컬럼과 자동증가 컬럼은 원래 update 할 수 없어서 에러를 발생하지만,
-- instead of update 트리거는 그 값을 무시하고, 나머지 컬럼들만 update 한다.


** 트리거 생성구문 암호화 **

sp_helptext insteadtrigger -- 트리거 생성 구문 확인

create trigger trg_show_insert2
on trg_ed
WITH ENCRYPTION
for insert
as
if exists (select * from inserted)
begin
select * from inserted
end
go

insert trg_ed values(10, '타잔')
go

sp_helptext trg_show_insert2


-----------------------------------------------------------------------------------

문제1) 부서테이블에 데이터를 입력하는 저장프로시저 생성


문제2) 부서테이블에 데이터가 입력되었을때, 지역과 부서명이 동일한 부서가
기존에 존재한다면 입력작업을 롤백하고, RAISERROR를 발생시키는
트리거를 작성한다. 에러는 사전에 만들어 둔다.
정상적으로 입력되었다면, EMP테이블의 '유리상자'의 부서번호를 입력된
부서번호로 변경하고, 직책도 '부장'으로 변경하라.
RAISERROR(5001, 10, 1)


문제3) 계좌테이블과 대출테이블이 있다고 가정한다. 그리고 계좌테이블에는
대출여부 컬럼이 있어서, 해당 계좌에 대해서 현재 대출 중일 때는 대출여부
컬럼 값이 “YES" 이고 대출 중이지 않을 때는 대출여부 컬럼 값이 ”NO" 값을
가져야 하는 규칙이 있다고 가정한다. 이때, 대출테이블에 데이터가 입력되면,
즉 대출이 일어나면 해당 계좌의 대출여부 컬럼을 UPDATE 해서 "YES" 로
변경하는 트리거를 만드시오.

-- 계좌테이블 존재
CREATE TABLE ACCOUNT
( ACCTNUMB CHAR(15) PRIMARY KEY, ACCTNAME VARCHAR(20), ACCTDATE SMALLDATETIME,
ACCTMONY INT, ACCTLOAN CHAR(3) DEFAULT 'NO')
GO
INSERT ACCOUNT(ACCTNUMB, ACCTNAME, ACCTDATE, ACCTMONY, ACCTLOAN)
VALUES( '111-2345-1-6789', '주택청약 정기적금', GETDATE(), 5000, DEFAULT)
GO

-- 대출테이블 존재
create table LOAN
( LID INT IDENTITY PRIMARY KEY NONCLUSTERED , LMONY INT, LREPAY INT, LRATE DECIMAL,
LNUMB CHAR(15) FOREIGN KEY REFERENCES ACCOUNT(ACCTNUMB))
GO

-- 데이터 확인
SELECT * FROM ACCOUNT
SELECT * FROM LOAN
--------------------------------------------

1) 대출이 일어나면, 해당 계좌데이터의 대출여부컬럼을 'YES' 로 변경하는 TRIGGER

2) 대출원금과 이자를 합한 금액이 갚을 금액과 같으면, 다시 계좌데이터의 대출여부컬럼을 'NO'로 변경한다.


문제4.) 다음과 같은 테이블이 있을 때, 요구 조건을 만족
시키시오.
create table item(code char(3),chk char(1))
create table ipgo(no int,code char(3),qty int)
create table jipgae(code char(3),qty int)

--jepum
insert item values('001','Y')
insert item values('002','N')
insert item values('003','N')

--ipgo
insert ipgo values(1,'001',2)
insert ipgo values(2,'001',3)
insert ipgo values(3,'002',4)
insert ipgo values(4,'003',5)
insert ipgo values(5,'003',6)

위와같은 데이타가 있을때 jepum의 chk 가 'N'인것만
jipgae 테이블에 아래와 같이 집계를 내려고 합니다
(단, 수정일때는 ipgo의 코드와 qty의 데이타가 수정되었을때만 실행
(참고로 여러 필드가 존재하지만 간단하게 두가지만 나오게 했음))

1) 코드가 존재하면 수정을 해서 합산하는거고 존재하지 않으면 입력(추가)하는
트리거를 만드시오.

code qty
-------------
003 11


2) 만약 ipgo 테이블에서 코드에서 no 값이 4번의 코드가 003 에서 002로 변경되고
no값이 5번의 qty 가 6 에서 10으로 변경된다고 가정했을때

code qty
-------------
002 5
003 10

이런식으로 출력이 되는 트리거를 만드시오.

-------------------------------------------------

문제 5) 성적 테이블에는 다음과 같은 컬럼들이 있다.

학번 국어 영어 수학 총점 평균
001 100 100 100
002 90 90 90
003 80 80 80

성적 테이블에 학번, 국어, 영어, 수학의 데이터가 insert 될때
총점, 평균을 구해서 저장하는 트리거를 만드시오.

다운로드
trigger.jpg (64.2KB)

의견 1 신규등록      목록