SQL server 2005 TRIGGER

use mydb00
use master
drop database mydb00
create database mydb00
트리거=============================================
트리거 작성과 트리거를 사용하여 데이터의 유효성을 검사하는 예
1. 트리거를 작성할 예제 테이블을 다음과 같이 작성한다.
use MYDB00
GO
CREATE TABLE 고객
(
 USERID  VARCHAR(10)  PRIMARY KEY NOT NULL,
 USERNAME  VARCHAR(10) NULL,
 JUMIN VARCHAR(15) NULL,
 AGE INT NULL,
 ADDR1 VARCHAR(20) NULL,
 TEL VARCHAR(15) NULL,
 JOB VARCHAR(20) NULL,
 POINT INT DEFAULT 0,
 REGDATE DATETIME DEFAULT GETDATE()
)

CREATE TRIGGER MT1   --누군가가 INSERT로 추가를 하면 자동적으로 실행되는 명령어
 ON 고객
 FOR INSERT
 AS
 DECLARE @AGE INT
 SELECT @AGE=AGE FROM INSERTED

IF @AGE<0 OR @AGE>60
BEGIN
 RAISERROR('입력된 나이 값이 지정된 범위를 벗어났습니다.',16, 1)
 ROLLBACK TRANSACTION  --(데이터가 잘못 됐을 경우) 취소시키는 명령어
END

INSERT 고객 VALUES('00012','김예린','12345-1231231',90,'서울특별시','712-3345','학생',DEFAULT, DEFAULT)
INSERT 고객 VALUES('00011','김예린','12345-1231231',20,'서울특별시','712-3345','학생',DEFAULT, DEFAULT)
SELECT * FROM 고객
USE TESTDB
SELECT * INTO 고객연습 FROM 고객정보
SELECT * FROM 고객연습
UPDATE 고객연습 SET ADDR1='서울'  -- 모든데이터가(ADDR1) 서울로 바뀜
BEGIN TRAN  -- ROLLBACK과 연동되어 자료를 원상태로 복구/COMMIT 한번 변환후 복구 불가 ㅊ ROLLBACK/COMMIT과 연동되어야 한다.
UPDATE 고객연습 SET ADDR2='영등포구'  -- ADDR2가 모두  영등포구로 바뀜
ROLLBACK
COMMIT -- 완전히 바꾸는 명령어.. ROLLBACK이 진다...

USE MYDB00
CREATE TRIGGER MT2
ON 고객
FOR UPDATE
AS
IF UPDATE(AGE)
BEGIN
 DECLARE @AGE INT
 SELECT @AGE=AGE FROM INSERTED
 IF @AGE<0 OR @AGE>60
 BEGIN
  RAISERROR('수정할 나이 값이 지정된 범위를 벗어났습니다.',16, 1)
  ROLLBACK TRANSACTION
 END
END

UPDATE 고객 SET AGE=98 WHERE USERID='00011'
UPDATE 고객 SET AGE=18 WHERE USERID='00011'
GO
SELECT * FROM 고객

CREATE TRIGGER MT4
ON 고객
INSTEAD OF INSERT
AS
PRINT 'INSTEAD OF 트리거가 실행되었습니다.'

INSERT 고객 VALUES('00022','김은희','123456-123451',28,'서울특별시','123-789','의료인',0,DEFAULT)
SELECT * FROM 고객

ALTER TRIGGER MT4
ON 고객
INSTEAD OF INSERT
AS
DECLARE
@USERID VARCHAR(10),
@USERNAME VARCHAR(10),
@JUMIN VARCHAR(15),
@AGE INT,
@ADDR1 VARCHAR(20),
@TEL  VARCHAR(15),
@JOB VARCHAR(20),
@POINT  INT,
@REGDATE DATETIME
SELECT @USERID=USERID, @USERNAME=USERNAME, @JUMIN=JUMIN, @AGE=AGE,
 @ADDR1=ADDR1, @TEL=TEL, @JOB=JOB, @POINT=POINT, @REGDATE=GETDATE()
FROM INSERTED
IF @AGE<0 OR @AGE>60
 SET @AGE=0
IF @JOB NOT IN('학생','공무원','회사원','방송인','기타')
 SET @JOB='기타'
IF @POINT<0
 SET @POINT=0
INSERT 고객 VALUES(@USERID, @USERNAME, @JUMIN, @AGE, @ADDR1, @TEL, @JOB, @POINT, @REGDATE)

INSERT 고객 VALUES('00022','김은희','13245-456789',98,'서울특별시','132-7894','의료인',-10,DEFAULT)
SELECT * FROM 고객

DROP TABLE 고객
GO
CREATE TABLE 고객
(
USERID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
USERNAME  VARCHAR(10) NOT NULL,
AGE INT NOT NULL,
ADDR1 VARCHAR(20) NOT NULL,
TEL VARCHAR(15) NOT NULL,
JOB VARCHAR(20) NOT NULL
)

CREATE VIEW MV1
AS
SELECT USERID, USERNAME, AGE, ADDR1, TEL FROM 고객

INSERT 고객(USERNAME, AGE, ADDR1, TEL, JOB) VALUES('김예린',22,'서울특별시','789-1234','학생')
SELECT * FROM 고객
DELETE 고객

INSERT MV1(USERNAME, AGE, ADDR1, TEL) VALUES('김으니',28,'서울특별시','789-1234')  --JOB가 NOT NULL로 잡혀있기때문에 입력이 되지 않는다.

CREATE TRIGGER MT5
ON MV1
INSTEAD OF INSERT
AS
DECLARE
@USERID VARCHAR(10),
@USERNAME VARCHAR(10),
@AGE INT,
@ADDR1 VARCHAR(20),
@TEL  VARCHAR(15),
@JOB VARCHAR(20)

SELECT
@USERID =USERID ,
@USERNAME =USERNAME,
@AGE =AGE,
@ADDR1 =ADDR1,
@TEL  =TEL
FROM INSERTED
SET @JOB='기타'
INSERT 고객(USERNAME, AGE, ADDR1,TEL,JOB) VALUES(@USERNAME,@AGE,@ADDR1,@TEL,@JOB)

INSERT MV1(USERNAME, AGE, ADDR1, TEL) VALUES('김으니',28,'서울특별시','789-1234')
SELECT * FROM 고객

--------------------------------------------------------------------------------------------
트리거를 사용하여 참조관계를 정의하는 예를 알아본다
참조 관계 정의에 사용할 두 테이블을 만드는 예
DROP TABLE 고객
CREATE TABLE 고객
(
 USERID VARCHAR(10) NOT NULL PRIMARY KEY,
 USERNAME  VARCHAR(10) NULL,
 AGE INT NULL,
 ADDR1  VARCHAR(20) NULL,
 TEL  VARCHAR(15) NULL,
 JOB  VARCHAR(20) NULL
)
CREATE TABLE 판매
(
 NUM  INT  IDENTITY(1,1) NOT NULL,
 USERID  VARCHAR(10)  NULL,
 PRODUCTNAME  VARCHAR(50)  NULL,
 PRICE MONEY NULL,
 DISCOUNT INT NULL,
 BUYDATE DATETIME NULL
)
CREATE TRIGGER MT7
ON 판매
FOR INSERT
AS
DECLARE @USERID VARCHAR(10)
SELECT @USERID=USERID FROM INSERTED

IF NOT EXISTS(SELECT * FROM 고객 WHERE USERID=@USERID)
BEGIN
 RAISERROR('입력한 고객 아이디가 테이블 [고객]에 존재하지 않습니다.',16, 1)
 ROLLBACK TRANSACTION
END

INSERT 판매
VALUES('00011','HP CD-WRITER PLUS 9100',203000,0,'2000-02-05')  --아직 고객정보에 아무런 정보가 없기때문에 입력불가
SELECT * FROM 고객
SELECT * FROM 판매
INSERT 고객
VALUES('00011','김예린',15,'서울특별시 마포구','789-4567','학생')
INSERT 판매
VALUES('00011','HP CD-WRITER PLUS 9100',203000,0,'2000-02-05')

고객 테이블에서 사용자 아이디가 변경될 경우 참조 관계를 유지하기 위해서는
하나의 테이블이 변경될 경우 연속적으로 관계된 테이블이 변경되도록 트러거를 만든다
CREATE TRIGGER MT8
ON 고객
FOR UPDATE
AS

IF UPDATE(USERID)
BEGIN
 UPDATE 판매 SET USERID = INSERTED.USERID
 FROM INSERTED, DELETED
 WHERE DELETED.USERID=판매.USERID
END
-----------------------------------------------------------------------------------------
UPDATE 고객 SET USERID='00033' WHERE USERID='00011'
SELECT * FROM 고객
SELECT * FROM 판매

고객 테이블에서 행이 삭제될 경우 판매정보 테이블을 검색하여 판매정보가
CREATE TRIGGER MT9
ON 고객
FOR DELETE
AS
DECLARE @USERID VARCHAR(10)
SELECT @USERID=USERID FROM DELETED

IF EXISTS(SELECT*FROM 판매 WHERE USERID=@USERID)
BEGIN
 RAISERROR('판매 정보가 존재하는 고객은 삭제할 수 없습니다. 판매정보를 먼저 삭제하여야 합니다.',16, 1)
 ROLLBACK TRANSACTION
END

DELETE 고객 WHERE USERID = '00033'

고객정보를 삭제할 경우 관계된 판매정보를 모두 삭제하도록 트리거를 수정
ALTER TRIGGER MT9
ON 고객
FOR DELETE
AS
DECLARE @USERID VARCHAR(10)
SELECT @USERID=USERID FROM DELETED
IF EXISTS(SELECT*FROM 판매 WHERE USERID=@USERID)
BEGIN
 DELETE 판매 WHERE USERID = @USERID     --내꺼
END

BEGIN 판매 FROM DELETED
WHERE 판매.USERID=DELETED.USERID     --선생님꺼
END

DELETE 고객 WHERE USERID = '00011'
SELECT * FROM 고객
SELECT * FROM 판매
DELETE 고객
INSERT 고객
VALUES('00011','김예린',15,'서울특별시 마포구','789-4567','학생')
INSERT 판매
VALUES('00011','HP CD-WRITER PLUS 9100',203000,0,'2000-02-05')

DROP TABLE 제품
DROP TABLE 판매
SP_HELP

CREATE TABLE 제품
(
PRODUCTID INT IDENTITY(1,1) NOT NULL,
PRODUCTNAME VARCHAR(50) NOT NULL,
UNITPRICE  MONEY NOT NULL
)
CREATE TABLE 판매
(
NUM  INT IDENTITY(1,1) NOT NULL,
USERID VARCHAR(10) NULL,
PRODUCTNAME  VARCHAR(50) NULL,
QTY INT DEFAULT 0,
UNITPRICE  MONEY DEFAULT 0,
PAYMENT  MONEY DEFAULT 0,
BUYDATE  DATETIME DEFAULT GETDATE()
)
SELECT * FROM 제품
SELECT * FROM 판매

INSERT 제품(PRODUCTNAME, UNITPRICE) VALUES('냉장고',5000)
INSERT 제품(PRODUCTNAME, UNITPRICE) VALUES('카메라',5500)
INSERT 제품(PRODUCTNAME, UNITPRICE) VALUES('선풍기',600)
INSERT 제품(PRODUCTNAME, UNITPRICE) VALUES('에어컨',7500)
INSERT 제품(PRODUCTNAME, UNITPRICE) VALUES('비디오',1500)
INSERT 제품(PRODUCTNAME, UNITPRICE) VALUES('캠코더',5550)
SELECT * FROM 제품
SELECT * FROM 판매

CREATE TRIGGER MT15
ON 판매
INSTEAD OF INSERT
AS DECLARE
@USERID VARCHAR(10),
@PRODUCTNAME VARCHAR(50),
@QTY INT,
@UNITPRICE MONEY
SELECT @USERID=USERID, @PRODUCTNAME=PRODUCTNAME, @QTY=QTY FROM INSERTED
SELECT @UNITPRICE=UNITPRICE FROM 제품 WHERE PRODUCTNAME=@PRODUCTNAME
INSERT 판매(USERID, PRODUCTNAME, UNITPRICE, QTY, PAYMENT)
VALUES(@USERID, @PRODUCTNAME, @UNITPRICE, @QTY,@UNITPRICE*@QTY )

INSERT 판매(USERID, PRODUCTNAME, QTY) VALUES('00011','카메라',7)
INSERT 판매(USERID, PRODUCTNAME, QTY) VALUES('00011','냉장고',3)
SELECT * FROM 제품
SELECT * FROM 판매

판매테이블에 재귀 트리거를 만드는 예   -- 자기 자신이 바껴야지만 자기 자신이 바뀌는...예
CREATE TRIGGER MT16
ON 판매
FOR UPDATE
AS
DECLARE
@NUM  INT,
@PRODUCTNAME  VARCHAR(50),
@QTY  INT,
@UNITPRICE  MONEY

IF UPDATE(PRODUCTNAME)
BEGIN
 SELECT @NUM=NUM, @PRODUCTNAME=PRODUCTNAME, @QTY=QTY FROM INSERTED
 SELECT @UNITPRICE=UNITPRICE FROM 제품 WHERE PRODUCTNAME=@PRODUCTNAME
 UPDATE 판매 SET UNITPRICE=@UNITPRICE WHERE NUM = @NUM
 PRINT '단가가 변경되었습니다.'
END

IF UPDATE(UNITPRICE)
BEGIN
 SELECT @NUM=NUM, @UNITPRICE=UNITPRICE, @QTY=QTY FROM INSERTED
 UPDATE 판매 SET PAYMENT=@UNITPRICE*@QTY WHERE NUM = @NUM
 PRINT '지불금액이 변경되었습니다.'
END

 

재귀 트리거를 허용하도록 데이터베이스 옵션을 변경하고 판매테이블에서 제귀트리거를 실행해본다.
ALTER DATABASE MYDB00
 SET RECURSIVE_TRIGGERS ON
GO
SELECT * FROM 판매 WHERE NUM=1
UPDATE 판매 SET PRODUCTNAME='에어컨' WHERE NUM=1
SELECT * FROM 판매 WHERE NUM=1