Ezmind

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-정리노트]트리거|작성자 장미빛바다