읽기 전용 테이블 만들기

어떤 테이블이 읽기 전용이 되어 야 할경우 어떻게 해야 할까요?

오라클의 경우 11G 이후로는 읽기 전용 테이블이 가능하다고 하는데
애석하게도 SQL Server에서는 읽기 전용 테이블은 없는데요. 제가 알기로는...
그래도 꼼수를 동원해서 제가 한번 만들어보겠습니다.

여러가지 방법이 있는데요.

1. 데이터베이스를 읽기 전용으로 만드는 방법입니다.
데이터베이스를 읽기 전용으로 설정하는 것은 간단합니다.
ALTER DATABASE database_name SET READ_ONLY
그런데 이 경우 모든 테이블이 읽기 전용이 되어버리겠네요.

2. 다음은 CHECK 제약조건을 이용하는 방법입니다.
CHECK 제약 조건은 열에서 허용되는 값을 제한하여 도메인 무결성을 강제 적용하는데 사용하는데요.
CHECK 제약 조건이 FALSE로 평가되면 값을 거부합니다.
그래서 항상 FALSE로 평가되는 CHECK 제약조건을 걸어서 수정을 못하게 하는 방법입니다.

일단 테이블을 하나 만들고 데이터를 집어 넣습니다.
CREATE TABLE TBL1 (COL1 INT, COL2 DATETIME)
GO
INSERT INTO TBL1 SELECT 1, GETDATE()
GO
INSERT INTO TBL1 SELECT 2, GETDATE()
GO
INSERT INTO TBL1 SELECT 3, GETDATE()
GO
INSERT INTO TBL1 SELECT 4, GETDATE()
GO
INSERT INTO TBL1 SELECT 5, GETDATE()
그리고 여기에  항상 FALSE로 평가되는 CHECK 제약조건을 겁니다.
예를 들어 0 = 1
ALTER TABLE TBL1 ADD CONSTRAINT CHK_TBL1_READONLY CHECK (0 = 1)
그런데 문제가 생겼습니다. 에러가 나네요.
메시지 547, 수준 16, 상태 0, 줄 1
ALTER TABLE 문이 CHECK 제약 조건 "CHK_TBL1_READONLY"과(와) 충돌했습니다.
데이터베이스 "TestDB", 테이블 "dbo.TBL1"에서 충돌이 발생했습니다.
이미 존재하는 데이터들이 CHECK 제약조건에 걸려서 충돌이 나는 것입니다.
NOCHECK 옵션을 사용하여 CHECK 제약조건을 생성할 때 기존 데이터를 검사하지 않도록 해야겠네요.
ALTER TABLE TBL1 WITH NOCHECK ADD CONSTRAINT CHK_TBL1_READONLY CHECK (0 = 1)
자 이제 CHECK 제약 조건이 생성되었습니다.
데이터를 한건 더 넣어보겠습니다.
INSERT INTO TBL1 SELECT 6, GETDATE()
에러가 발생하네요.
메시지 547, 수준 16, 상태 0, 줄 1
INSERT 문이 CHECK 제약 조건 "CHK_TBL1_READONLY"과(와) 충돌했습니다.
데이터베이스 "TestDB", 테이블 "dbo.TBL1"에서 충돌이 발생했습니다.
문이 종료되었습니다.
CHECK 제약조건을 이용하면 INSERT나 UPDATE는 막을 수 있습니다.
그런데 DELETE는 막을 수가 없습니다.
읽기 전용 테이블이라고 보긴 좀 무리가 있네요.

3. 트리거를 이용한 방법입니다.
테이블에 INSERT, UPDATE, DELETE 할때 ROLLBACK을 수행하는 트리거를 생성하는 방법입니다.
CREATE TRIGGER TRG_IUD_TBL1_READONLY ON TBL1 FOR INSERT, UPDATE, DELETE

AS

BEGIN
        RAISERROR ('TBL1은 읽기 전용입니다.', 16, 1)
        ROLLBACK TRANSACTION
END
여기에 데이터를 입력하면
메시지 50000, 수준 16, 상태 1, 프로시저 TRG_IUD_TBL1_READONLY, 줄 6
TBL1은 읽기 전용입니다.
메시지 3609, 수준 16, 상태 1, 줄 1
트리거가 발생하여 트랜잭션이 종료되었습니다. 일괄 처리가 중단되었습니다.
이렇게 에러가 발생하게 됩니다.
이 방법은 INSERT, UPDATE, DELETE 모두를 막을 수 있겠네요.

4. 다음은 Read Only Filegroup에 테이블을 생성하는 방법입니다.
/* Database 생성 */
CREATE DATABASE TestDB
GO
/* 파일그룹 추가 */
ALTER DATABASE TestDB ADD FILEGROUP Read_Only_FileGroup
GO
/* 파일 추가 */
ALTER DATABASE TestDB
ADD FILE (NAME = N'Read_Only_Tables', FILENAME = N'D:\TestDB_Read_Only.ndf', SIZE = 4MB, FILEGROWTH = 4MB)
TO FILEGROUP Read_Only_FileGroup
GO
/* Read_Only_FileGroup에 테이블 생성, 아직은 이름만 Read Only입니다. */
CREATE TABLE TBL2 (COL1 INT, COL2 DATETIME) ON Read_Only_FileGroup
GO
/* 데이터 INSERT */
INSERT INTO TBL2 SELECT 1, GETDATE()
GO
INSERT INTO TBL2 SELECT 2, GETDATE()
GO
INSERT INTO TBL2 SELECT 3, GETDATE()
GO
/* 파일그룹을 READ ONLY로 설정 */
ALTER DATABASE TestDB MODIFY FILEGROUP Read_Only_FileGroup READONLY
여기에 데이터를 입력하면
INSERT INTO TBL2 SELECT 4, GETDATE()
에러가 발생하게 됩니다.
메시지 652, 수준 16, 상태 1, 줄 1
테이블 "dbo.TBL2"(RowsetId 72057594038845440)에 대한 인덱스 ""이(가) 수정할 수 없는 읽기 전용 파일 그룹("Read_Only_FileGroup")에 있습니다.
5. 다음은 권한 설정을 이용한 방법입니다.
DENY INSERT, UPDATE, DELETE ON TBL2 TO Public
이렇게 한 후 데이터를 입력하면 에러가 나게됩니다.
메시지 229, 수준 14, 상태 5, 줄 1
개체 'TBL2', 데이터베이스 'TestDB', 스키마 'dbo'에 대한 INSERT 권한이 거부되었습니다.
6. 다음은 VIEW를 이용한 방법인데요.
Read Only로 설정할 테이블의 이름을 바꾸고 원래 테이블 이름으로 VIEW를 만듭니다.
이 VIEW에 상수 필드를 추가하고 UNION 합니다.
SP_RENAME 'TBL2', 'TBL2_Orign'
GO
CREATE VIEW TBL2

AS

SELECT COL1, COL2 FROM TBL2_Orign
UNION ALL
SELECT 0, GETDATE() WHERE 1 = 0
이제 이 뷰에 INSERT를 해보겠습니다.
INSERT INTO TBL2 SELECT 5, GETDATE()
에러가 나네요.
메시지 4406, 수준 16, 상태 1, 줄 1
뷰 또는 함수 'TBL2'은(는) 파생 필드나 상수 필드를 포함하므로 업데이트 또는 삽입하지 못했습니다.
DELETE를 해보겠습니다.
DELETE TBL2
이것도 에러가 나네요.
메시지 4426, 수준 16, 상태 1, 줄 1
뷰 'TBL2'의 정의에 UNION 연산자가 포함되어 있으므로 이 뷰를 업데이트할 수 없습니다.
UPDATE를 해보겠습니다.
UPDATE TBL2 SET COL2 = GETDATE() WHERE COL1 = 1
이것 역시 에러 납니다.
메시지 4406, 수준 16, 상태 1, 줄 1
뷰 또는 함수 'TBL2'은(는) 파생 필드나 상수 필드를 포함하므로 업데이트 또는 삽입하지 못했습니다.