[MS-SQL] TRIGGER INSERT 예제

 

-- TRIGGER INSERT 예제

-- 아래와같은테이블이있다고가정한다.

-- 데이터INSERT 시EXPIREDATE 에YYYYMMDD 형식의데이터를INSERT 하며

-- 동시에TRIGGER를이용하여EXPIREDATE 의YYYYMM 형식을UPDATE 시키려한다.

 

CREATE TABLE DBO.TEST

(

SEED INT IDENTITY(1,1) NOT NULL CONSTRAINT IX_TEST PRIMARY KEY CLUSTERED ,

EXPIRDATE NVARCHAR(8) NOT NULL,

MONTH_EXPIREDATE NVARCHAR(6) NULL

)

GO

---------------------------------------------------------------------------------------------------------------------------------

-- TRIGGER 생성부분

---------------------------------------------------------------------------------------------------------------------------------

 

CREATE TRIGGER TEST_TRIGGER ON TEST

FOR INSERT

AS

UPDATE TEST SET MONTH_EXPIREDATE = LEFT(B.EXPIRDATE,6)

FROM TEST AS A,INSERTED AS B

WHERE A.SEED = B.SEED

---------------------------------------------------------------------------------------------------------------------------------

 

-- INSERT 하여테스트해본다.

INSERT TEST

(EXPIRDATE)

SELECT '20121231'

UNION

SELECT '20120130'

UNION

SELECT '20120229'

UNION

SELECT '20120328'

UNION

SELECT '20120427'

--결과를본다

SELECT * FROM TEST