다음을 통해 공유

INSTEAD OF INSERT 트리거

  • 아티클

INSTEAD OF INSERT 트리거를 뷰 또는 테이블에 정의하여 INSERT 문의 표준 작업을 대신할 수 있습니다. 대개 INSTEAD OF INSERT 트리거는 뷰에 정의되어 하나 이상의 기본 테이블에 데이터를 삽입합니다.

뷰 SELECT 목록에 있는 열은 Null을 허용하거나 허용하지 않을 수 있습니다. 뷰 열에서 Null을 허용하지 않으면 INSERT 문이 열에 값을 제공해야 합니다. 뷰 열을 정의하는 식에 다음과 같은 항목이 있으면 뷰 열에서 Null을 허용합니다.

  • Null을 허용하는 기본 테이블 열에 대한 참조
  • 산술 연산자
  • 함수에 대한 참조
  • Null을 허용하는 하위 식이 있는 CASE 또는 COALESCE
  • NULLIF

COLUMNPROPERTY 함수에서 보고한 AllowsNull 속성을 사용하면 뷰 열에서 Null이 허용되는지 여부를 알 수 있습니다. sp_help 저장 프로시저를 사용해도 Null이 허용되는 열을 알 수 있습니다.

INSTEAD OF INSERT 트리거가 있는 뷰를 참조하는 INSERT 문이 Null을 허용하지 않는 모든 뷰 열에 값을 제공해야 합니다. 여기에는 다음과 같이 입력 값을 지정할 수 없는 기본 테이블의 열을 참조하는 뷰 열이 포함됩니다.

  • 기본 테이블의 계산 열
  • IDENTITY INSERT가 OFF로 설정된 기본 테이블의 ID 열
  • timestamp 데이터 형식의 기본 테이블 열

INSTEAD OF INSERT 뷰 트리거가 inserted 테이블에 있는 데이터를 사용하여 기본 테이블에 대해 INSERT를 생성할 경우, INSERT 문의 SELECT 목록에 열을 포함시키지 않음으로써 이러한 유형의 열에 제공된 값을 무시해야 합니다. INSERT 문은 이러한 유형의 열에 대해 더미 값을 생성할 수 있습니다.

예를 들어 INSERT 문에서 기본 테이블의 ID 열 또는 계산 열에 매핑되는 뷰 열에 값을 지정하지 않고 자리 표시자 값을 제공할 수도 있습니다. 기본 테이블에 값을 삽입하는 INSERT 문이 INSTEAD OF 트리거로 구성되면 이 트리거가 제공된 값을 무시할 수 있습니다.

다음 문은 테이블, 뷰, 그리고 프로세스를 보여 주는 트리거를 만듭니다.

CREATE TABLE BaseTable
  (PrimaryKey     int PRIMARY KEY IDENTITY(1,1),
   Color          nvarchar(10) NOT NULL,
   Material       nvarchar(10) NOT NULL,
   ComputedCol AS (Color + Material)
  )
GO

--Create a view that contains all columns from the base table.
CREATE VIEW InsteadView
AS SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable
GO

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
  --Build an INSERT statement ignoring inserted.PrimaryKey and 
  --inserted.ComputedCol.
  INSERT INTO BaseTable
       SELECT Color, Material
       FROM inserted
END
GO

BaseTable을 직접 참조하는 INSERT 문에서는 PrimaryKey 및 ComputedCol 열에 값을 제공할 수 없습니다. 예를 들면 다음과 같습니다.

--A correct INSERT statement that skips the PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable (Color, Material)
       VALUES (N'Red', N'Cloth')

--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable

--An incorrect statement that tries to supply a value for the 
--PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable
       VALUES (2, N'Green', N'Wood', N'GreenWood')

그러나 InsteadView를 참조하는 INSERT 문에서는 PrimaryKey 및 ComputedCol에 값을 제공해야 합니다.

--A correct INSERT statement supplying dummy values for the 
--PrimaryKey and ComputedCol columns.
INSERT INTO InsteadView (PrimaryKey, Color, Material, ComputedCol)
       VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM InsteadView

InsteadTrigger로 전달된 inserted 테이블은 Null을 허용하지 않는 PrimaryKey 열과 ComputedCol 열로 작성됩니다. 따라서 뷰를 참조하는 INSERT 문에서 이 열에 값을 제공해야 합니다. 값 999와 N'XXXXXX'가 InsteadTrigger로 전달되었지만 트리거의 INSERT 문에서 inserted.PrimaryKey 또는 inserted.ComputedCol 중 하나를 선택하지 않으므로 두 값이 무시됩니다. BaseTable에 실제로 삽입된 행의 PrimaryKey에는 2가 포함되고 ComputedCol에는 N'BluePlastic'이 포함됩니다.

inserted 테이블의 계산 열, ID 열 및 timestamp 열에 포함된 값은 뷰에 지정된 INSTEAD OF 트리거와 테이블에 지정된 INSTEAD OF INSERT 트리거에서 서로 다릅니다.

기본 테이블 열 테이블의 INSERT 트리거에서 inserted 테이블에 있는 값 뷰의 INSTEAD OF INSERT 트리거에서 inserted 테이블에 있는 값

계산 열

계산 식

사용자 지정 값 또는 NULL

열에 IDENTITY 속성이 있는 경우

IDENTITY_INSERT가 OFF이면 0, IDENTITY_INSERT가 ON이면 지정된 값

사용자 지정 값 또는 NULL

timestamp 데이터 형식

열에서 Null을 허용하지 않으면 이진수 0, Null을 허용하면 NULL

사용자 지정 값 또는 NULL

기본 테이블을 직접 참조하는 INSERT 문에서는 DEFAULT 정의가 있는 NOT NULL 열에 값을 제공하지 않아도 됩니다. INSERT 문에서 값을 제공하지 않으면 기본값이 사용됩니다. 그러나 INSTEAD OF INSERT 트리거가 있는 뷰의 단순 식에서 DEFAULT 정의가 있는 NOT NULL 열을 참조할 경우에는 뷰를 참조하는 INSERT 문에서 열에 값을 제공해야 합니다. 이 값은 트리거로 전달된 inserted 테이블을 작성하는 데 필요합니다. 기본값을 사용해야 하는 트리거로 신호를 보내는 값에는 규칙이 필요합니다. 가장 적절한 규칙은 INSERT 문에서 기본값을 제공하는 것입니다.

INSTEAD OF INSERT 트리거에서 deleted 테이블은 항상 비어 있습니다.

참고 항목

관련 자료

COLUMNPROPERTY(Transact-SQL)
sp_help(Transact-SQL)

도움말 및 정보

SQL Server 2005 지원 받기