1.트리거의 개념
(1)트리거의 개요
-제약조건과 더불어서 데이터의 무결성을 위한 또다른 기능
-DML트리거와 DDL트리거(SQL서버2005에서 새롭게 나왔다) 두가지가 있다.
-테이블 또는 뷰에 부착되는 프로그램코드
-저장프로시저와 비슷하게 작동하지만 직접 실행시킬 수 없고
오직 해당 테이블이나 뷰에 이벤트(입력,수정,삭제)가 발생할 경우에만 실행된다.
-저장프로시저와 달리 매개변수나 리턴값을 사용할 수 없다.
(2)트리거
CREATE TRIGGER름
ON 부착할테이블이름 //부착테이블지정
AFTER( 혹은 BEFORE) DELETE( 혹은 UPDATE , INSERT) //부착테이블과의 관계에서 지정해위 작동시기 지정
AS
동작할 내용 //행위 지정
USE tempdb;
CREATE TABLE testTbl (id INT, txt NVARCHAR(5));
GO
INSERT INTO testTbl VALUES(1, '우재남');
INSERT INTO testTbl VALUES(2, '지운이');
INSERT INTO testTbl VALUES(3, '한주연');
//트리거 생성
CREATE TRIGGER testTrg -- 트리거 이름
ON testTbl -- 트리거를 부착할 테이블
AFTER DELETE, UPDATE -- 삭제,수정후에 작동하도록 지정
AS
PRINT('트리거가 작동했습니다') ; -- 트리거 실행시 작동되는 코드들
INSERT INTO testTbl VALUES(4, '당탕이'); //트리거 작동안된다
UPDATE testTbl SET txt = '우지운' WHERE id = 2; //트리거 작동
DELETE testTbl WHERE id = 4; //트리거 작동
(3)DML 트리거의 종류
<1>AFTER 트리거
-테이블에 INSERT,UPDATE,DELETE 등의 작업이 일어났을 때 작동한다.
-해당작업 후에 작동한다
-테이블에만 작동하며 뷰에는 작동하지 않는다.
<2>INSTEAD OF 트리거
-BEFORE 트리거라도 부른다
-테이블이나 뷰에 이벤트가 작동되기 전에 작동한다.
-테이블 뿐 아니라 뷰에도 작동되며 ,주로 뷰가 업데이트가 가능하도록 사용한다
-INSERT, UPDATE,DELETE 세가지 이벤트로 작동한다.
(4)DML 트리거의 사용
USE master ;
RESTORE DATABASE sqlDB FROM DISK ='c:\sqldb.bak' WITH REPLACE ;
USE sqlDB;
DROP TABLE buyTbl; -- 구매테이블은 필요없으므로 삭제.
//백업테이블 생성
CREATE TABLE backup_userTbl
( userID nchar(8) ,
name nvarchar(10) ,
birthYear int ,
addr nchar(4) ,
mobile1 nchar(3),
mobile2 nchar(8),
height smallint ,
modType NCHAR(2), -- 변경된 타입. '수정' 또는 '삭제'
modDate datetime, -- 변경된 날짜
modUser nvarchar(256) -- 변경한 사용자
)
//트리거 생성
CREATE TRIGGER trg_BackupUserTbl -- 트리거 이름
ON userTbl -- 트리거를 부착할 테이블
AFTER UPDATE,DELETE -- 삭제,수정 후에 작동하도록 지정
AS
DECLARE @modType NCHAR(2) -- 변경 타입
IF (COLUMNS_UPDATED() > 0) -- userTbl이 업데이트 되었다면
BEGIN
SET @modType = '수정'
END
ELSE -- 삭제되었다면,
BEGIN
SET @modType = '삭제'
END
-- delete 테이블의 내용(변경전의 내용)을 백업테이블에 삽입
INSERT INTO backup_userTbl
SELECT userID, name, birthYear, addr, mobile1, mobile2,
height, @modType, GETDATE(), USER_NAME() FROM deleted
// delete되면 deleted라는 시스템테이블로 들어간다.
//트리거 실행여부체크
UPDATE userTbl SET addr = '미국' WHERE userID = 'JJJ';
DELETE userTbl WHERE height >= 180;
SELECT * FROM backup_userTbl;
TRUNCATE TABLE userTbl;
//truncate은 DML구문이 아니므로 트리거가 작동하지 않는다.
SELECT * FROM backup_userTbl;
//인서트 트리거 생성 - 인서트를 막기 위한 용도로 사용
CREATE TRIGGER trg_insertUserTbl
ON userTbl
AFTER INSERT -- 삽입 후에 작동하도록 지정
AS
RAISERROR(N'데이터의 입력을 시도했습니다.',10,1)
RAISERROR(N'귀하의 정보가 서버에 기록되었습니다.',10,1)
RAISERROR(N'그리고, 입력한 데이터는 적용되지 않았습니다.',10,1)
ROLLBACK TRAN;
//트리거 테스트
INSERT INTO userTbl VALUES(N'ABC', N'에비씨', 1977, N'서울', N'011', N'1111111', 181)
<3>CLR 트리거
-T-SQL저장프로시저 대신 .NET Framework에서 생성되는 트리거
(5)DML트리거가 생성하는 임시테이블
<1>inserted 테이블
insert와 update 작업시에 변경 후의 행 데이터와 동일한 데이터가 저장된다.
<2>deleted 테이블
delete와 update 작업이 수행되면 우선 해당테이블에 삭제 또는 변경된 후에,
삭제 또는 변경되기 전 의 데이터가 저장된다.
<3>각 상황별 판단
[update]
업데이트되기 전의 데이터는 deleted로 들어가고
업데이트된 후의 데이터는 inserted로 들어간다.
[insert]
insert 된 후의 데이터는 inserted로 들어간다.
[delete]
delete되기전의 데이터는 deleted로 들어간다.
(6)INSTEAD OF 트리거(BEFORE트리거)
-테이블 변경이 가해지기 전에 작동되는 트리거
-주로 뷰에 행이 삽입되거나 변경,삭제될 때 사용한다.
-INSTEAD OF 트리거가 작동하면 시도된 INSERT, UPDATE, DELETE 문은 무시된다.
(7)트리거의 사용
USE master ;
RESTORE DATABASE sqlDB FROM DISK ='c:\sqldb.bak' WITH REPLACE ;
USE sqlDB;
GO
//복합뷰 생성
CREATE VIEW uv_deliver -- 배송정보를 위한 뷰 ( 보안을 위해서 뷰만을 공개)
AS
SELECT b.userid, u.name, b.prodName, b.price, b.amount, u.addr
FROM buyTbl b
INNER JOIN userTbl u
ON b.userid = u.userid;
SELECT * FROM uv_deliver;
INSERT INTO uv_deliver VALUES ('DTI', '당탕이', '구두', 50, 1, '인천')
//BEFORE 트리거생성
CREATE TRIGGER trg_insert
ON uv_deliver
INSTEAD OF INSERT
AS
BEGIN
//회원정보가입
INSERT INTO userTbl(userid, name, birthYear, addr)
SELECT userid, name, 1900 , addr FROM inserted
//구매테이블 구매정보인서트
INSERT INTO buyTbl(userid, prodName, price, amount)
SELECT userid, prodName, price, amount FROM inserted
END;
INSERT INTO uv_deliver VALUES ('SYJ', '사용자', '구두', 50, 1, '강원')
SELECT * FROM userTbl WHERE userid = 'DTI';
SELECT * FROM buyTbl WHERE userid = 'DTI';
//뷰에 부착된 트리거 보기
EXEC sp_helptrigger uv_deliver;
//트리거 내용보기
EXEC sp_helptext trg_insert;
//트리거 이름 변경(권장하지 않는다)
EXEC sp_rename 'dbo.trg_insert', 'dbo.trg_uvInsert' ;
//트리거 삭제 후 다시 생성하는 것이 좋다.
DROP TRIGGER dbo.trg_uvInsert;
SELECT * FROM sys.sql_modules ;
//뷰를 삭제하면 트리거도 삭제된다.
DROP VIEW uv_deliver;
2.DDL 트리거
(1)기본개념
-DDL문에 의해서 작동되는 트리거
-주로 CREATE,ALTER, DROP 등으로 작동
-테이블이 아닌 데이터베이스나 SQL 서버에 대해서 작동한다.
-AFTER (또는 FOR) 트리거 만 지원한다.
(2)구문형식
CREATE TRIGGER 트리거이름
ON {ALL SERVER | DATABASE}
{FOR | AFTER} {event_type | event_group}
as
sql문장들 ;
[이벤트 형식 (event_type)]
-데이터베이스범위의 DDL 이벤트 - 데이터베이스 내의 개체를 생성,수정,삭제할 경우에 발생한다.
-서버 범위의 DDL 이벤트 - SQL 서버에 적용되는 DDL 이벤트
[이벤트 그룹(event_group)]
-몇몇 개의 관련있는 이벤트 형식을 묶는 집합
-트리구조로 되어 있으며 , 서버레벨 이벤트의 가장 최상위로는 DDL_SERVER_LEVEL_EVENTS 그룹이 있다.
DDL_DATABASE_LEVEL_EVENTS ---DDL_TABLE_VIEW_EVENTS --- DDL_TABLE_EVENTS(내부에 CREAE,ALTER,DROP)
---DDL_VIEW_EVENTS(내부에 CREAE,ALTER,DROP)
___DDL_TABLE_EVENTS(내부에 CREAE,ALTER,DROP)
---DDL_PROCEDURE_EVENTS (내부에 CREAE,ALTER,DROP)
(3)DDL 트리거의 사용
USE master ;
RESTORE DATABASE sqlDB FROM DISK ='c:\sqldb.bak' WITH REPLACE ;
USE sqlDB;
GO
//DDL트리거 생성
CREATE TRIGGER ddlTrg_sqlDB
ON DATABASE
AFTER DROP_TABLE, ALTER_TABLE
AS
PRINT ' 경고: 테이블을 삭제하거나 변경하는 것은 금지되어 있습니다.'
ROLLBACK TRANSACTION;
//트리거 테스트
DROP TABLE buyTbl;
DROP TABLE userTbl; //DDL트리거보다 제약조건이 먼저 작동한다.
//DDL트리거 수정
ALTER TRIGGER ddlTrg_sqlDB
ON DATABASE
AFTER DDL_TABLE_VIEW_EVENTS
AS
PRINT ' 경고: 모든 테이블/뷰/인덱스에 대한 생성,변경,삭제는 금지되었습니다.'
ROLLBACK TRANSACTION;
//트리거 테스트
CREATE TABLE prodTbl (prodID INT, prodName NVARCHAR(20));
//트리거중지 또는 가동
DISABLE TRIGGER ddlTrg_sqlDB ON DATABASE;
ENABLE TRIGGER ddlTrg_sqlDB ON DATABASE;
//SQL 서버에 대한 DDL트리거 생성
CREATE TRIGGER ddlTrg_server
ON ALL SERVER
AFTER CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS
PRINT ' 경고: 데이터베이스 관련된 작업은 관리자에게 문의하세요.'
ROLLBACK TRANSACTION;
CREATE DATABASE testDB;
//DDL트리거의 삭제
DROP TRIGGER ddlTrg_server ON ALL SERVER;
USE sqlDB;
DROP TRIGGER ddlTrg_sqlDB ON DATABASE;
3.기타 트리거에 관한 사항
(1)다중트리거와 중첩트리거
다중트리거 - 하나의 테이블에 동일한 트리거가 여러 개 부착되어 있는 것이다.
중첩트리거 - 트리거가 또 다른 트리걸르 작동시키는 것,32단계까지 작동가능하다.
중첩트리거의 예
고객의 물건구매 - 구매테이블에 인서트 - 물품테이블업데이트 - 배송테이블에 배송건수 인서트
(2)재귀트리거
<1>간접재귀 트리거
A테이블 인서트시 - B테이블로 인서트시 - A테이블 인선트
<2>직접재귀 트리거
자기 자신에게 계속적으로 작동하는 트리거
(3)다중트리거,중첩트리거,재귀트리거의 사용
//중첩트리거 설정값 변경
EXEC sp_configure 'nested triggers', 0
RECONFIGURE; //재설정
USE master;
CREATE DATABASE triggerDB;
USE triggerDB;
//구매테이블 생성
CREATE TABLE orderTbl -- 구매 테이블
(orderNo INT IDENTITY, -- 구매 일련번호
userID NVARCHAR(5), -- 구매한 회원아이디
prodName NVARCHAR(5), -- 구매한 물건
orderAmount INT ); -- 구매한 개수
GO
//물품테이블생성
CREATE TABLE prodTbl -- 물품 테이블
( prodName NVARCHAR(5), -- 물건 이름
account INT ); -- 남은 물건수량
GO
//배송테이블생성
CREATE TABLE deliverTbl -- 배송 테이블
( deliverNo INT IDENTITY, -- 배송 일련번호
prodName NVARCHAR(5), -- 배송할 물건
amount INT ); -- 배송할 물건개수
INSERT INTO prodTbl VALUES('사과', 100);
INSERT INTO prodTbl VALUES('배', 100);
INSERT INTO prodTbl VALUES('귤', 100);
CREATE TABLE recuA (id INT IDENTITY, txt NVARCHAR(10)); -- 간접 재귀트리거용 테이블A
GO
CREATE TABLE recuB (id INT IDENTITY, txt NVARCHAR(10)); -- 간접 재귀트리거용 테이블B
GO
CREATE TABLE recuAA (id INT IDENTITY, txt NVARCHAR(10)); -- 직접 재귀트리거용 테이블AA
EXEC sp_configure 'nested triggers'
//물품테이블에서 개수 감소시키는 트리거
CREATE TRIGGER trg_order
ON orderTbl
AFTER INSERT
AS
PRINT '1. trg_order를 실행합니다.'
DECLARE @orderAmount INT
DECLARE @prodName NVARCHAR(5)
SELECT @orderAmount = orderAmount FROM inserted
SELECT @prodName = prodName FROM inserted
UPDATE prodTbl SET account = account - @orderAmount
WHERE prodName = @prodName ;
GO
// 배송테이블에 새 배송 건을 입력하는 트리거
CREATE TRIGGER trg_prod
ON prodTbl
AFTER UPDATE
AS
PRINT '2. trg_prod를 실행합니다.'
DECLARE @prodName NVARCHAR(5)
DECLARE @amount INT
SELECT @prodName = prodName FROM inserted
SELECT @amount = D.account - I.account
FROM inserted I, deleted D -- 변경전의개수- 변경후의개수 = 주문개수
INSERT INTO deliverTbl(prodName,amount) VALUES(@prodName, @amount);
INSERT INTO orderTbl VALUES ('JOHN','배',5);
SELECT * FROM orderTbl;
SELECT * FROM prodTbl;
SELECT * FROM deliverTbl;
EXEC sp_rename 'dbo.deliverTbl.prodName', 'productName', 'COLUMN';
INSERT INTO orderTbl VALUES ('DANG','사과',9);
SELECT * FROM orderTbl;
SELECT * FROM prodTbl;
SELECT * FROM deliverTbl;
USE triggerDB;
//재귀트리거 허용여부 조회
SELECT name, is_recursive_triggers_on FROM sys.databases
WHERE name = 'triggerDB';
//재귀트리거 허용설정
ALTER DATABASE triggerDB
SET RECURSIVE_TRIGGERS ON;
CREATE TRIGGER trg_recuA
ON recuA
AFTER INSERT
AS
DECLARE @id INT
SELECT @id = trigger_nestlevel() -- 현재 트리거 레벨값
PRINT '트리거레벨==> ' + CAST(@id AS CHAR(5))
INSERT INTO recuB VALUES ('간접재귀트리거')
GO
//또다른 재귀트리거 생성
CREATE TRIGGER trg_recuB
ON recuB
AFTER INSERT
AS
DECLARE @id INT
SELECT @id = trigger_nestlevel() -- 현재 트리거 레벨값
PRINT '트리거레벨==> ' + CAST(@id AS CHAR(5))
INSERT INTO recuA VALUES ('간접재귀트리거')
INSERT INTO recuA VALUES ('처음입력값');
select * from recuA;
select * from recuB;
ALTER TRIGGER trg_recuA
ON recuA
AFTER INSERT
AS
IF ( (SELECT trigger_nestlevel() ) >= 32 )
RETURN
DECLARE @id INT
SELECT @id = trigger_nestlevel() -- 현재트리거레벨값
PRINT '트리거레벨==> ' + CAST(@id AS CHAR(5))
INSERT INTO recuB VALUES ('간접재귀트리거')
GO
ALTER TRIGGER trg_recuB
ON recuB
AFTER INSERT
AS
IF ( (SELECT trigger_nestlevel() ) >= 32 ) // 재귀트리거 종료조건 판단
RETURN
DECLARE @id INT
SELECT @id = trigger_nestlevel() -- 현재트리거레벨값
PRINT '트리거레벨==> ' + CAST(@id AS CHAR(5))
INSERT INTO recuA VALUES ('간접재귀트리거')
INSERT INTO recuA VALUES ('처음입력값');
select * from recuA;
select * from recuB;
[출처] [MSSQL - 뇌를자극하는MSSQL2005-정리노트]트리거|작성자 장미빛바다