[MSSQL] 14.트리거,커서 (Trigger, Cursor)

1. What is a Triggers

 

- Stored Procedure와 마찬가지로 Compile되어 SQL Server에 저장되며, 실행계획이 Cache상에 존재하게 된다. 

Stored Procedure는 앞서 SQL Server에 저장이 되고, 실행계획이 Cache라는 곳에 저장이 되어진다고 말씀을 드렸습니다. 그럼 Trigger도 일종의 Stored Procedure라고 했으니까, Trigger도 SQL Server에 저장되고, 실행계획도 있을까요? 예 그렇습니다. Trigger는 Stored Procedure와 마찬가지로 Trigger에서 정의한 T-SQL 구문들을 Compile하여 SQL Server에 저장하게 되며, 실행계획을 Cache상에 올려 놓게 됩니다.

 

- Table에 종속 

Data 연산과 밀접한 관계가 있는 것이 아니라, Data 연산이 이루어지는 Table과 밀접한 관계를 가지고 있습니다. 사실 Trigger는 Table에 대해서 정의되어집니다. 특정 Table에 대해서 Trigger를 정의하게 되면, 해당 Table에 대해서 Data의 연산이 발생되면, 정의된 Trigger가 수행되게 되는 것입니다.

 

- 자동실행 

앞서 설명을 드린 것과 같이 Trigger는 Data의 입력, 갱신, 삭제 등의 연산이 일어나면 자동으로 실행되어 집니다. 그럼 Stored Procedure처럼 사용자가 호출할 수 있을까요? 그렇지 않습니다. Trigger는 단순히 Data에 대한 연산이 일어나면, 자동적으로 SQL Server에 의해서 호출이 되기 때문에, 사용자가 호출하여 사용을 할 수 없습니다.

 

- Temp Table, View ??? 

Trigger는 Table에 대해서 종속적이지만, 임시 Table이나 View에 대해서는 아무런 작용도 일으키지 않습니다. 즉 임시 Table이나 View에 대해서는 Trigger를 정의할 수 없다는 것입니다.

- Transaction 안에서 실행 

 

 

- 결과를 반환하지 않는다. 

Stored Procedure는 연산의 결과로 결과값을 반환하는 등의 작업을 할 수 있지만, Trigger의 경우에는 연산만 처리할 뿐, 결과 값을 반환하는 등의 작업은 불가능합니다.

 

- After Trigger 

Before Trigger와 After Trigger, 두 가지로 나뉘어 집니다. SQL Server 7.0에서 제공하는 Trigger는 After Trigger입니다. 즉, 연산이 수행되고 난 뒤에 발생이 되는 Trigger라는 뜻이죠. Data에 대한 연산이 발생되면, Trigger와 같이 발생되는 것이 또 하나 있습니다. 제약조건인데요. 예를 들어 Unique로 지정된 Column에 중복된 값을 입력하려고 할 때, 에러가 발생되게 됩니다. 이러한 경우에는 Data가 입력되기 전에 제약조건을 만족하는지를 먼저 검사하게 됩니다. Trigger보다 먼저 수행되겠죠. 

 

2. Uses of Triggers

앞서 Data 무결성에 대해서 설명을 드리면서, Data 무결성의 특징 중에서 업무규칙을 구현할 수 있다는 장점이 있다고 말씀을 드렸습니다.

 

하지만 간단한 업무규칙의 경우에는 제약조건을 통해서 구현이 가능하지만, 좀 복잡한 업무규칙의 경우에는 구현이 불가능하게 됩니다. 이렇게 복잡한 업무규칙에 대한 부분은 Stored Procedure나 Trigger를 통해서 구현이 가능합니다. 물론 앞서 말씀을 드린 것처럼, Trigger는 Data를 반환하는 등의 작업은 어렵지만, Data가 입력되면서 이루어져야 할 업무규칙 등의 처리는 탁월한 성능을 제공하게 됩니다. 

 

 Database 내에 관련된 Table의 Data에 대한 일련의 작업을 수행 

앞서 Stored Procedure에서 임대에 대한 업무 예제를 구현해 보았습니다. 기억이 나실지 모르겠는데요. UP_임대 라는 Stored Procedure였습니다.

 

- 복잡한 Data 무결성 강화 

 

 

- Cascade의 구현 

SQL Server 2000에서는 Cascade 기능을 제공을 하고 있지만, SQL Server에서는 이를 지원하지 않기 때문에 직접적으로 구현을 해주어야 합니다. 이러한 Cascade기능은 주로 Trigger를 통해서 구현이 되어집니다. Cascade는 앞선 Data Modeling 과정에서 식별자업무규칙 정의라는 것을 하였습니다. 이는 Data가 입력, 삭제, 갱신이 되는 경우 다른 Data에 영향을 미치는 규칙을 정의한 것인데요. 이 중에서 Cascade와 Restrict가 있었습니다.

 

3. Considerations for Using Triggers

 

- Trigger는 반작용으로 실행 

 

- 하나의 Table에 여러 개의 Trigger를 생성 가능 

- View나 임시 Table에 Trigger를 생성할 수 없습니다. 

- Trigger는 결과 집합을 반환하지 않습니다. 

- Trigger는 여러 개의 Row에 대해서 작업을 할 수 있습니다. 

 

4. Defining Triggers - Creating Triggers

- 기본적인 구문 

CREATE TRIGGER [owner.]trigger_name 

ON [owner.]table_name 

[WITH ENCRYTION ] 

{ 

     {FOR {INSERT | UPDATE | DELETE } 

          [WITH APPEND ] 

              [NOT FOR REPLICATION ] 

AS 

sql_statements} 

}

이렇게 Trigger 생성 구문을 사용하는데 있어서 몇 가지 제약조건이 있습니다.

• 모든 CREATE, ALTER 구문은 사용될 수 없습니다. 
  DATABASE, SCHEMA, TABLE, INDEX, PROCEDURE, DEFAULT, RULE, TRIGGER, VIEW 등을 생성하거나 변경하는 구문은 사용될 수 없습니다.
• 모든 DROP 구문은 사용될 수 없습니다.
• TRUNCATE TABLE
• GRANT, REVOKE, DENY
• UPDATE STATISTICS
• RECONFIGURE
• LOAD DATABASE, LOG, RESTORE DATABASE, RESTORE LOG

 

 

 

1. What is a Cursor

일반적인 SELECT문과 Cursor의 차이는 Data를 바라보는 관점에 있다고 할 수 있습니다. SELECT문의 경우에는 그림에서 보시는 것과 같이, Data를 집합(Set)단위로 작업을 수행하는 것에 반해 Cursor의 경우에는 행(Row)단위로 작업을 수행하게 됩니다.

즉, Cursor는 결과집합에서 Row단위로 Data를 반환하여 작업을 처리하게 됩니다.

• SELECT - Set at a time
• Cursor - Record at a time

2. Methods of Calling Cursors

이렇게 Cursor는 하나의 Row씩을 반환하여 작업을 할 수 있도록 하지만, 사실은 모든 Data를 가져와 Memory 상의 Cache에 두게 됩니다. 이때 결과집합을 어디에 두느냐에 따라서 Client Side Cursor와 Server Side Cursor로 나눌 수 있습니다. 그림에서 보시는 것과 같이 Cursor의 결과 집합이 MSSQL Server로부터 Network을 통해서 Client로 전달되어 Client의 Memory나 임시파일에 저장되어 있는 경우에는 Client Side Cursor라고 합니다.

 

3. How Transact-SQL Cursors Work

 

/*********************************************/

/*예제  : 기본적인 CURSOR 예제 */

/*********************************************/ 

USE VIDEO

GO

 

DECLARE @tablename nvarchar (256)

--생성 

DECLARE tnames_cursor CURSOR 

     FOR SELECT [name]

            FROM sysobjects 

            WHERE objectproperty(id, 'IsUserTable' ) = 1

            ORDER BY [name]

-- 활성화 

OPEN tnames_cursor

 

--SELECT @@CURSOR_ROWS 

 

--반환 

FETCH NEXT FROM tnames_cursor INTO @tablename

--SELECT @@FETCH_STATUS 

 

WHILE (@@FETCH_STATUS = 0)

     BEGIN 

          EXEC sp_spaceused @tablename

          FETCH NEXT FROM tnames_cursor INTO @tablename

     END 

 

-- 해제 

CLOSE tnames_cursor

 

-- 비활성화 

DEALLOCATE tnames_cursor

 

-- Cursro의 정보 

SELECT @@CURSOR_ROWS 

SELECT @@FETCH_STATUS 

 

4. Transact-SQL Cursor Syntax

지금까지 살펴 본 Cursor의 5단계에 대해서 구문 중심으로 살펴보도록 하겠습니다.

 

DECLARE	Cursor를 정의하고 자원을 할당
OPEN	정의된 Cursor에 결과집합을 반환하여 활성화
FETCH	Cursor 결과집합을 사용
CLOSE	반환된 결과집합을 해제
DEALOOCATE	할당된 자원과 Cursor 정의를 제거

 

5. Working with Data from Cursors

• 반드시 FOR UPDATE 구문을 명시
• READ ONLY Option은 사용불가

 

6. Getting Cursor Information

이러한 Cursor에 대한 정보는 System Stored Procedure와 System Function을 통해서 얻을 수 있습니다.

- System Stored Procedure 

먼저 System Stored Procedure는 표에서 보시는 것과 같이 4개의 System Stored Procedure가 있습니다.

 

System Stored Procedure	설                        명
sp_cursor_list	현재 Connection에서 열려 있는 Cursor의 특성을 반환
sp_describe_cursor	특정 Cursor의 특성을 반환
sp_describe_cursor_columns	Server Cursor의 결과 집합에 있는 열의 특성반환
sp_describe_cursor_tables	Server Cursor가 참조하는 Table의 특성을 반환

 자세한 내용은 Books Online을 참조하세요.