Triggers란?

트리거는 상용 데이터베이스 시스템이 무결성제약 관리를 위하여 지원하는 기능이며, SQL 표준은 1999년도 제정되었지만 상용 데이터베이스 시스템은 이전부터 자체적으로 지원해왔다.

• 즉, 트리거는 DB에 원치않는 변경이 있을 때 시스템에 의해 자동으로 수행되는 구문이다.

트리거는 기본적으로 ECA 규칙으로서 사건(Event)/조건(Condition)/행동(Action) 부문으로 구성된다.

• 데이터베이스 시스템에 특정 사건이 발생할 때 수행되고,
  • 여기서 사건이란 데이터베이스에 대한 변경 연산(즉, insert/delete/update 연산)을 의미한다.
• 주어진 조건을 평가하여 조건이 만족되면 주어진 행동을 하는 규칙이다.

Event와 Action in 트리거

트리거를 디자인하려면 다음을 명확히 해야한다:

• 트리거를 일으키는 Event
• 트리거가 진행되려면 만족해야 하는 Condition
• 조건이 만족되었을 때에 취해야 하는 Action

트리거에서 의미하는 사건은 터플의 변화이며, 터플 입력, 터플 삭제, 터플 갱신 연산을 의미한다.

• 특히, 트리거 갱신(update) 연산에서 특정 속성을 지정할 수도 있다.
  • 문법은 after update of 속성명 on 테이블명 이다.

변경 전/후의 속성 값은 참조될 수가 있다. (By 변수 선언)

• referencing old row as : delete and update 연산을 위해
• referencing new row as : insert and update 연산을 위해

터플에 변화가 있으면 변화하기 전 터플과 변화 후의 터플을 지칭하는 문장이 상기에 나와 있다. delete 연산이면 변화 전 터플만을 지칭할 수 있고, insert 연산이면 변화 후 터플만을 지칭할 수 있다.

Trigger Example (1) : 설명

학생이 수업을 수강하여 학점을 취득하면, 학생이 취득한 총 학점을 변경하는 트리거를 구성해 보자.

수강하여 학점 취득한 상황뿐만 아니라, F였다가 성적변경으로 인해 다른 성적을 받아도 학점을 취득할 수 있다.

• Event
  • takes 테이블의 grade 속성에 변경이 있을 때이며, 그 외 테이블의 인스턴스 변화는 관련이 없다.
  • after update of grade on takes : grade에 update가 된다면 trigger 발동
• Condition
  • old grade is 'F' or old grade is null
  • new grade is not 'F' and new grade is not null
  • grade 속성의 값이 변경전에는 F이거나 또는 널 값이고, 변경 후의 속성 값은 F가 아니면서 널 값도 아니어야 한다. 변경 전에는 F이었다가 같은 F 또는 널 값으로 변경되는 경우에는 총 학점 변화가 없으므로 트리거 행동(action)을 수행하지 말아야 한다.
• Action
  • student 테이블의 totalCredit 속성 값을 조정하는 것인데 추가하는 학점은 course 테이블에서 찾을 수 있다.
  • update “totalCredit” of “student” table by adding “credit” of “course” with “cID” that is the same as “cID” of the updated tuple

Trigger Example (1) : SQL 작성

Create trigger myCred after update of grade on takes
referencing new row as nrow
referencing old row as orow
for each row    # takes에 update 대상이되는 각각 튜플에 대해서
when (nrow.grade <> ’F’ and nrow.grade is not null)
	and (orow.grade = ’F’ or orow.grade is null)
begin 
	Update student
	set totalCredit = totalCredit +
		(select credit
 		from course
 		where cID = nrow.cID)
 	where sID = nrow.sID;
end;

첫 문장에서 트리거 이름과 사건을 명시한다.

두 번째, 세 번째 문장은 update가 발생하는 경우 update 전과 후의 테이블의 터플을 참조하는 문장이다.

네 번째 문장(for each row)은 조건과 행동을 값이 변경된 각 터플(상기 예제에서는 grade 속성 값이 변경된 터플)을 기준으로 수행하는 것을 의미한다. 즉, takes 테이블의 update된 각 터플에 대하여 조건을 검사하고 조건을 만족하면 행동(여기서는 Update 문장)을 수행한다.

다섯번쨰 문장의 when절은 조건을 명시하고,

여섯번째 문장의 조건 밑에 나오는 begin 블록이 트리거의 행동을 명시한다.

when절을 보면, 변경후 터플의 grade 값은 ‘F’가 아니면서 널 값도 아니고, 변경전 터플의 grade 값은 ‘F’이거나 널 값이어야 한다. 조건이 만족하면 begin 블록을 수행하는데, 그 내용은 student 테이블의 totalCredit 속성 갱신이다. 새로운 터플의 cID 값을 가지고 course 테이블에서 해당 과목의 credit 값을 구하여 기존 totalCredit 속성 값에 합산하는 연산을 한다.

Trigger Example (2) : 설명

한 사람이 카드에 돈이 없는데도 계속 돈을 썼을 때, 마이너스 통장을 만드는 대신에 은행측에서는 다음과 같이 처리한다고 가정하자:

• account의 balance를 zero로 만듦
• 대출한 금액만큼의 대출계좌 loan을 생성
• 이 대출에 돈이 없는 원래 계좌번호(account number)와 동일한 대출계좌번호(loan number)를 주기로 한다.

간단한 은행 관련 스키마이다. account 테이블에 계좌번호(aNumber)와 잔고(balance)가 있으며, 계좌번호를 가진 고객이름(cName)은 depositor 테이블에 저장되어 있다. 은행에서 고객이 대출을 받는 경우, loan 테이블은 대출번호(lNumber)와 대출금이 있으며, 대출 고객명(cName)은 borrower 테이블에 나온다.

상기 트리거는 잔고보다 더 많은 금액의 수표가 발생되는 경우(즉, overdraft) 대출 계좌를 사용자 명의로 개설하고 대출금을 overdraft된 금액으로 하는 작업을 한다.

Event는 account 테이블 갱신 연산이며, Condition은 balance 속성 값이 음수(negative)로 되는 것이다.

Trigger Example (2) : SQL 작성

Create trigger myOverdraft after update of balance on account 
referencing new row as nrow
for each row
when nrow.balance < 0
begin atomic
	Insert into borrower 
		(select cName, aNumber
 		from depositor
 		where nrow.aNumber = depositor.aNumber);
	Insert into loan values (nrow.aNumber, –nrow.balance);
 	Update account set balance = 0
 		where account.aNumber = nrow.aNumber;
end

상기 그림에서 주요 연산은 다음과 같다. 트리거 수행되는 조건은 account의 balance가 음수(negative)가 되는 것이다.

그리고 왜 referecing new row as nrow만 했냐면 옛날의 정보와 비교할 필요 없이 오로지 업데이트연산된 balance가 오로지 음수가 되는지만 확인하면 되므로 old row는 필요없다.

첫 번째 Insert 연산은 borrower에 새로운 터플을 생성하는데, depositor에 있는 aNumber를 loan number로 사용한다.

• borrower에다가 ← depositor의 cName, aNumber를 주었기때문에.
• 의미론적으로는, 일반인이 채무자로 변경되었음을 의미한다.

두 번째 Insert 연산은 loan에 새로운 터플을 입력한다. 주어진 balance의 값에 음수를 붙여 양수 값이 되며, 이 값을 loan amount로 입력된다. 마지막 갱신 연산은 account 관계의 balance 속성 값을 zero로 갱신한다.

상기 예제에서 행동에 해당되는 insert/insert/update 문장을 트랜잭션으로 수행하기 위하여 begin atomic ... end를 사용하였다. 데이터베이스 연산을 트랜잭션으로 처리하면 상기 예제에서는 세 개의 SQL 문장이 모두 수행하던가 아니면 하나도 수행하지 않는다 (all-or-nothing).

트랜잭션은 ACID(atomicity, consistency, isolation, durability) 성질을 가지는 데이터베이스 연산의 sequence인데 자세한 사항은 생략하기로 한다.

만약 행동 SQL 문장이 하나이면 begin atomic ... end를 사용하지 않아도 무방하다

Trigger Example (3)

Create trigger myTotalSalary after update of salary on employee
referencing new row as nrow
referencing old row as orow
for each row
when (nrow.dNumber is not null)
Update department
	set totalSalary=totalSalary+nrow.salary–orow.salary
	where dno = nrow.dNumber;

상기 예제는 employee 관계의 salary 속성에 변경이 발생하는 경우, department 관계의 totalSalary 속성을 변경하는 터플 수준 트리거이다.

트리거 조건은 nrow.dNumber의 값이 널이 아닌 경우이다. nrow.dNumber의 값이 널이면 갱신된 근로자의 소속 부서를 모르므로 department 관계의 totalSalary 속성 값 갱신이 불가능하다.

Trigger에서 before event

트리거를 정의 할 때, before 키워드를 사용하면 트리거는 이벤트가 수행되기 전에 트리거가 수행된다.

Create trigger mySetNull before update of grade on takes
referencing new row as nrow
for each row
when (nrow.grade = ' ')
Update takes set nrow.grade = null;

상기 예제는 takes 테이블을 갱신하기 전에 터플의 grade 속성 값이 ‘ ’(값은 존재하나 존재하는 값이 blank)이면 이를 널 값으로 갱신하는 트리거이다.

즉, update를 할 때 null값으로 바꿔서 update하라는 구문이다.

문장 수준 트리거

Tuple Level Trigger(터플 수준 트리거)는 터플 단위로 트리거 행동을 수행하게 된다.

반면에,

Statement Level Trigger(문장 수준 트리거)는 터플 단위가 아니고 SQL 문장 단위로 트리거 행동을 수행하게 되며, 사건 전후 테이블을 테이블 단위로 참조한다.

• for each row 대신에 for each statement 를 사용
• 영향을 받는 rows들을 포함하는 일시적인 table을 참조하기 위해서 referencing old table 또는 referencing new table 사용

트리거 행동으로 인하여 매우 많은 수의 터플에 변화가 있는 경우에는 문장 수준 트리거가 유용하다.

문장수준트리거 example

Create trigger myTotalSalaryStateLevel after update of salary on employee
referencing old table as O
referencing new table as N
for each statement
when exists(select * from O where O.dnumber is not null) or
	 exists(select * from N where N.dnumber is not null)
Update department as D
set D.totalSalary = D.totalSalary
   + (select sum(N.salary) from N where D.dno=N.dnumber)
   - (select sum(O.salary) from O where D.dno=O.dnumber)
where D.dno in ( (select dnumber from N) union 
                 (select dnumber from O) );

상기 예제는 employee 관계의 salary 속성에 변경이 발생하는 경우, department 관계의 totalSalary 속성을 변경하는 문장 수준 트리거이다.

when절에 명시되어 있는 조건은 구 테이블이나 신 테이블의 dNumber 속성이 널 값이 아닌 터플이 존재하면 만족하는데, 이는 널 값이 아닌 dNumber 터플이 존재하면 해당 department의 totalSalary 속성 값을 변경해야 하기 때문이다.

트리거의 실행 부문에서 각 터플별로 수행을 하지 않고 전체 테이블에 대하여 일괄적으로 totalSalary 속성 값을 변경하고 있다.