[한기교] SQL 활용 필기(16) - 트리거

< 무결성 규정 >

ㅁ 무결성의 의미
- 정밀성, 정확성, 정당성
- 허가 받은 사용자가 수행하는 갱신 작업에서 의미적 오류를 방지
- 의미적 제약의 개념

ㅁ 무결성 서브 시스템
- 사용자 요청> 보안 시스템> 갱신연산> 무결성 서브시스템> 갱신> 정확한 DB
- 무결성 규정을 유지 관리
- 데이터베이스의 무결성을 유지
- 트랜잭션이 수행하는 갱신 연산이 무결성 규정을 위반하지 않는가를 감시
--- 위반 시에는 거부, 보고, 취소 / 복귀를 수행함

 

< 제약 조건 >

ㅁ 무결성 규정 대상
- 도메인 : 형식, 타입, 범위
- 기본키, 외래키 : 개체 무결성(Entity Integrity), 참조 무결성(Referencial Integrity)
- 종속성 (묵시적 제약조건) : 함수 종속, 다치 종속, 조인 종속
- 관계 : 내부 관계, 외부 관계

 

ㅁ 도메인 무결성 대상
- 도메인 정의 : 도메인 이름, 데이터 형
- 삽입이나 갱신 연산에 적용

 

ㅁ 릴레이션의 무결성 규정
- 릴레이션을 조작하는 과정에서의 의미적 제약조건을 명세함
- 연산 수행 전 / 후에 대한 제약 조건을 규정함
--- 삽입/삭제/갱신

- 분류
--- 상태 제약과 과도 제약
--- 집합 제약과 튜플 제약
--- 즉시 제약과 지연 제약

 

ㅁ 상태 제약
: 릴레이션 상태에 대한 제약
- 일관성 있는 상태 유지 : 정적 제약
--- 각 릴레이션 상태가 모두 만족해야 하는 규정
--- 데이터베이스 상태의 유효성
- 키 속성의 제약 : 유일성
- NULL 값의 제약 : 이름은 NULL 값일 수 없음
- 관계 제약 : 참조무결성
- 도메인 제약 : 유효한 값
- 의미 무결성 제약

 

ㅁ 과도 제약
- 동적 제약
- 데이터베이스의 한 상태에서 다른 상태로 변환되는 과정에서 적용되는 규정
- 데이터베이스 상태의 변환 전과 후의 비교
--- 변환 전과 후에 모두 적용됨 (ex) 월급은 감소될 수 없음

 

ㅁ 집합 제약
: 튜플 집합 전체에 대한 제약

ㅁ 튜플 제약
: 처리 되고 있는 튜플에만 적용됨

ㅁ 즉시 제약
: 삽입 / 삭제 / 갱신 연산이 수행될 때마다 적용되는 제약 규정

ㅁ 지연 제약
: 트랜잭션이 완전히 수행된 후에 적용되는 제약 규정

 

< 트리거 활용 >

ㅁ 트리거의 개념

: DBMS에서 특정 사건이 발생 시 자동으로 일련의 과정이 수행되는 프로시저

* 프로시저
- 사용자가 직접 EXEC 명령어를 이용하여 프로시저를 수행

* 트리거
- 특정 조건을 만족하면 자동으로 수행되도록 하는 저장 프로시저
- 특정 사건이 발생될 때만 실행되는 프로시저
- 사용자가 트리거를 따로 호출할 필요 없음

 

 

ㅁ 무결성과 트리거(TRIGGER)
- 트리거는 데이터의 변경이 발생될 때 수행됨
- 데이터 변경 시 무결성에 문제가 발생되면 이를 보완할 수 있도록 자동으로 프로시저를 수행하도록 트리거를 정의해 놓으면 무결성을 유지시킬 수 있음
- 단점 : 릴레이션 선언 시 정의한 제약조건에 비하여 성능이 저하됨
- 장점 : 프로시저와 더불어 데이터베이스 내에 업무 규칙을 구현할 수 있음

 

 

ㅁ 수행 기점에 따른 트리거의 분류
- AFTER 트리거
: 이벤트(삽입 / 삭제 / 변경) 발생 직후 실행되는 트리거
--- 테이블에 대해서만 작성됨

- BEFORE 트리거
: 이벤트(삽입 / 삭제 / 변경) 발생 이전에 실행되는 트리거
--- 일반적으로 BEFORE 트리거는 지원되지 않음

- INSTEAD OF 트리거
: 이벤트(삽입 / 삭제 / 변경) 발생 시 해당 이벤트 대신 구동되는 트리거
--- 즉, 다른 작업을 수행하는 트리거
--- INSTEAD OF 트리거를 활용하여 BEFORE 트리거 같은 역할을 수행시킬 수 있음

 

 

ㅁ INSERTED와 DELETED 테이블

 

ㅁ 트리거 생성 문법

CREATE TRIGGER 트리거명
ON 테이블명
[for / after / instead of]
[insert / update / delete]
AS
SQL문

- for는 after와 같은 것임

- on 테이블에 의해 테이블에 내용이 추가 / 삭제되면 inserted 또는 deleted라는 가상 테이블에 자동으로 추가되고 이를 이용하여 트리거를 수행시키게 됨

 

 

ㅁ AFTER 트리거(또는 FOR 트리거) 구동 예

- 새로운 업무 규칙

--- 신입 직원들의 급여는 무조건 100임

--- 기존 직원들을 이 규칙에 적용 받지 않음

--- employee 테이블에 새로운 튜플이 들어 올 때 마다 salary속성을 자동으로 100이 되게 하면 됨

- INSTEAD OF 트리거

--- 뷰나 테이블에 삽입 / 삭제 / 변경 연산에 대응되어 다른 작업을 수행하는 트리거

 

ㅁ DDL 트리거

CREATE, ALTER, DROP과 같은 DDL문이 발생시 구동되는 트리거

정의 시 ON 테이블명 대신 ON DATABASE를 사용함

INSTEAD OF 트리거는 지원 안 함

CREATE TRIGGER 트리거명
ON DATABASE
{FOR|AFTER} {DROP _ TABLE|CREATE _ TABLE|ALTER _ TABLE}
AS SQL문

 

ㅁ DCL 트리거

트리거의 변경과 삭제

DCL문임으로 DROP과 ALTER문을 씀

 

-변경

ALTER TRIGGER 트리거명

- 삭제

DROP TRIGGER 트리거명