MSSQL - 트리거의 개념, 사용 방법(Insert, Update, Delete, Inserted, Deleted)

 

트리거란? (Trigger)

• 자동으로 실행되는 프로시저의 일종, 수동으로는 실행 불가
• 어떤 이벤트에서 자동으로 실행되는지 지정
• 한 테이블과 관련되어서만 존재 할 수 있음
• Before(=Instead of), After 트리거 존재
• 트랜젝션의 일부로 처리 (트리거는 하나의 트랜잭션으로 처리)
  • 데이터의 무결성 강화, 오류 발생시 롤백 
  • 트랜잭션의 자세한 설명은 아래의 포스팅을 참고해주세요
    https://luvris2.tistory.com/387

MSSQL - 트랜잭션의 개념 이해와 종류, 사용 방법

---

사용 할 수 없는 키워드

• 트랜잭션 안에서 수행되기 때문에 다음 키워드는 사용 될 수 없음
  • ALTER DATABASE
  • CREATE DATABASE
  • DROP DATABASE
  • LOAD DATABASE
  • RESTORE DATABASE
  • DISK INIT
  • DISK RESIZE
  • LOAD LOG
  • RESTORE LOG
  • RECONFIGURE

---

트리거 구문 (Syntax)

• 자세한 설명은 사용 예시에서 다룹니다.

트리거 생성 (Create)

CREATE TRIGGER -- 트리거명
    ON -- 테이블명/뷰이름
    AFTER -- 입력(insert)/수정(update)/삭제(delete) 
    AS
        -- 조건식
    BEGIN
        -- SQL 쿼리 (sql statement)
END

---

트리거 수정 (Alter)

• CREATE와 동일하나 CREATE부분을 'ALTER'로 변경

ALTER TRIGGER -- 트리거명
    ON -- 테이블명/뷰이름
    AFTER -- 입력(insert)/수정(update)/삭제(delete) 
    AS
        -- 조건식
    BEGIN
        -- SQL 쿼리 (sql statement)
END

---

트리거 삭제 (Drop)

DROP TRIGGER -- 트리거명

---

트리거 사용 예시

기본 테이블 정보

• Table Name : MyPet

---

Insert

• 예시) 데이터 입력시 값 바로 확인
• 데이터가 입력 될 때 해당 테이블을 셀렉트해주는 트리거 작성

-- 트리거 생성
-- 값 입력시 해당 테이블 값 확인 (select)
CREATE TRIGGER insertAutoSelectTrigger
ON MyPet
AFTER insert
AS
BEGIN
    select * from MyPet
END

-- 값 입력
insert into MyPet (name, age) values ('test', 100);

---

반응형

Update

• 예시) 데이터 수정시 수정 불가
  • if update(컬럼) : 해당 컬럼 업데이트시 발생 이벤트
    • 에러메시지 출력
    • 트랜잭션의 일부이기 때문에 롤백 기능을 사용하여 복구처리

-- 트리거 생성
-- 데이터 업데이트시 수정 불가 메시지와 함께 롤백
CREATE TRIGGER notUpdateTrigger ON MyPet
FOR UPDATE
AS
    if update(name)
BEGIN
    RAISERROR('이름은 수정 할 수 없습니다.', 11, 1)
ROLLBACK TRAN
END

-- 값 수정
update MyPet set name='lala' where id = '1'

---

Delete

• 예시) 데이터 삭제 불가
  • Update 예시와 비슷

-- 트리거 생성
-- 데이터 삭제 불가
CREATE TRIGGER notUpdateTrigger ON MyPet
FOR DELETE
AS
BEGIN
    RAISERROR('삭제 할 수 없습니다.', 10, 1);
ROLLBACK TRAN
END

-- 값 삭제
delete from MyPet;

---

추가 설명 : RAISERROR

• 사용자에게 에러 메시지를 출력하는 명령어
• RAISERROR의 대한 설명은 아래의 포스팅 참고
  • https://luvris2.tistory.com/392

MSSQL - RAISERROR (오류 메시지 생성하기)

---

Inserted

• 입력된 값을 활용 할 때 사용
• 테이블명 자리에 Inserted 키워드 사용하면 입력한 값을 반환
• insert, update문은 둘 다 Inserted로 취급 (update : 삭제 후 새 값 입력이기 때문)
  • 예시) 값 입력시 다른 테이블에 입력된 값 백업

-- 트리거 생성
-- 다른 테이블에 입력한 값 복제 (백업, 이중저장)
CREATE TRIGGER insertedBackupTrigger on MyPet
FOR INSERT, UPDATE
AS
BEGIN
    -- 입력(수정)된 값을 저장 할 변수 선언
    DECLARE @nameVal nchar(10);
    DECLARE @ageVal int;
    -- 입력(수정)된 값을 변수에 저장
    SELECT @nameVal = name FROM INSERTED;
    SELECT @ageVal = age FROM INSERTED;
    -- 다른 테이블에 입력 된 값 저장(INSERT)
    INSERT INTO dbo.MyPetBackup (name, age) VALUES (@nameVal, @ageVal);
END

-- 값 입력 시, 다른 테이블에도 함께 저장되는 트리거 발동
insert into MyPet (name, age) values ('test', 100);

 

• 첫번째 결과 : 입력한 테이블 조회
• 두번째 결과 : 트리거가 발동되어 다른 테이블에 저장한 결과 조회

---

Deleted

• 삭제된 값을 활용 할 때 사용
• Inserted와 같이 삭제되었을 때 삭제된 값을 반환
  • 예시) 데이터 삭제시 다른 테이블의 동일한 값도 같이 삭제
  • 외래 키가 설정된 데이터 지울시 관련된 모든 데이터를 삭제한다는 가정

-- 트리거 생성
-- 데이터 삭제시 해당 ID와 일치하는 다른 테이블의 값 삭제
CREATE TRIGGER deleteAllTrigger on MyPet
FOR DELETE
AS
BEGIN
    -- 삭제된 값의 고유 번호를 저장 할 변수 선언
    DECLARE @idVal int;
    -- 삭제된 값의 고유 번호를 변수에 저장
    SELECT @idVal = age FROM INSERTED;
    -- 다른 테이블에 동일한 고유 번호의 값 삭제(DELETE)
    DELETE dbo.MyPetBackup WHERE id = @idVal;
END

-- 값 삭제, 트리거가 발동되어 다른 테이블의 동일한 값 같이 삭제
DELETE FROM MyPet;

---

트리거 수행 순서 지정

• 여러개의 트리거가 발동된다면 트리거의 수행 순서를 지정하여 실행 시킬 수 있음

-- 순서 : FIRST, LAST
-- 동작 종류 : INSERT, UPDATE
sp_settriggerorder '트리거명', '순서', '동작종류'

---

참고

• 작성한 트리거가 뭐였는지 기억이 안난다면 아래의 명령어로 트리거 목록 조회

select * from sysobjects where type ='TR'

 

• 무슨 트리거였는지 기억이 안난다면 아래의 명령어로 트리거 소스 코드 확인

exec sp_helptext 트리거이름

 

• Microsoft - SQL 문서 - AFTER TRIGGER

https://learn.microsoft.com/ko-kr/sql/t-sql/statements/alter-trigger-transact-sql?view=sql-server-ver16 

ALTER TRIGGER(Transact-SQL) - SQL Server

 

• Microsoft - SQL 문서 - inserted 및 deleted 테이블 사용

https://learn.microsoft.com/ko-kr/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-ver16 

DML 트리거와 함께 inserted 및 deleted 테이블을 사용하여 변경 내용을 검사하는 방법을 알아봅니다.