본문 바로가기
내가 하는일/SQL

트리거 예저

Tenin 2010. 2. 4. 11:11

 

 

-- ★ 트리거(TRIGGER)란?

-- 총의 방아쇠를 당기면 총알을 발사하듯이

-- 테이블에 특정 쿼리문(INSERT, UPDATE, DELETE)을

-- 수행할 경우에 자동으로 어떤일을 하도록 만드는 것

-- (주의 : SELECT 일 경우에는 TRIGGER 적용 안됨)

 
  1. -- ★ TRIGGER 형식   
  2. CREATE TRIGGER 트리거명   
  3. ON 대상이 되는 테이블   
  4. FOR (INSERT, UPDATE, DELETE)   
  5. AS  
  6. -- 수행해야 하는 일들   
  7.   
  8. CREATE TABLE T1   
  9. (   
  10.  NUM INT,   
  11.  NAME VARCHAR(10)   
  12. )    
  13. SELECT * FROM T1   
  14.   
  15. -- 등록하고자 하는 트리거가 존재한다면 해당 트리거 삭제   
  16. IF EXISTS(SELECT * FROM SYSOBJECTS WHERE TYPE = 'TR' AND NAME = 'TRG_01')   
  17. DROP TRIGGER TRG_01    
  18. -- INSERT 문이 실행될때 특정 문장 출력해주는 트리거   
  19. CREATE TRIGGER TRG_01   
  20. ON T1   
  21. FOR INSERT  
  22. AS  
  23. PRINT 'T1 테이블에 1개의 데이터가 등록되었습니다'  
  24. INSERT T1 VALUES(2, '나나나')   
  25.   
  26. -- INSERT 문이 실행되면 테이블 셀렉트 해오는 트리거   
  27. CREATE TRIGGER TRG_011   
  28. ON T1   
  29. FOR INSERT, UPDATE  
  30. AS  
  31. SELECT * FROM T1   
  32. INSERT T1 VALUES(3, '다다다')   
  33.   
  34. -- 트리거 등록 확인   
  35. SELECT * FROM SYSOBJECTS WHERE TYPE = 'TR'  
  36. --> TYPE = TR -> 트리거   
  37. --> TYPE = P -> 프로시저   
  38.   
  39. SELECT * FROM T1   
  40. SELECT * FROM T2    
  41. CREATE TABLE T2   
  42. (   
  43.  NUM INT,   
  44.  NAME VARCHAR(10)   
  45. )   
  46.   
  47. -- ★ INSERTED, DELETED 테이블 : 트리거가 실행될때 메모리상에만 임시로 저장되는 테이블   
  48. -- ★ UPDATED 테이블은 있는가? 없다. 업데이트시에도 INSERTED나 DELETED 테이블!!   
  49.   
  50. -- T1 테이블 데이터 삽입시 T2 테이블에도 삽입해주는 트리거   
  51. IF EXISTS(SELECT * FROM SYSOBJECTS WHERE TYPE = 'TR' AND NAME ='TRG_02_1')   
  52. DROP TRIGGER TRG_02_1   
  53. -- 1번째 방법 (비추 : 컬럼수가 늘어나거나 여러번 삽입시)   
  54. CREATE TRIGGER TRG_02_1   
  55. ON T1   
  56. FOR INSERT  
  57. AS  
  58. DECLARE @NUM INT, @NAME VARCHAR(10)   
  59. SET @NUM = (SELECT NUM FROM INSERTED)   
  60. SET @NAME = (SELECT NAME FROM INSERTED)   
  61. INSERT T2 VALUES (@NUM, @NAME)    
  62. -- ★ 2번째 방법(강추)   
  63. CREATE TRIGGER TRG_02_2   
  64. ON T1   
  65. FOR INSERT  
  66. AS  
  67. INSERT INTO T2 SELECT * FROM INSERTED   
  68. -- 삽입후 결과확인   
  69. INSERT T1 VALUES (12, 'AAA')   
  70. SELECT * FROM T1   
  71. SELECT * FROM T2   
트리거 연습문제..
-- ★ 트리거 연습문제   
  1. --1. 아래는 강남구 전화국과 서울시 전화국을 나타내는 가상의 테이블이다.   
  2. -- 이 테이블은 각각 varchar(20), char(9) 데이터 타입을 가진 이름과    
  3. --전화번호 컬럼을 가지고 있다.   
  4. -- 지금부터 강남구 테이블에서 DML작업이 발생했을 경우    
  5. -- 서울시 테이블에서도 동시에 같은 작업이 일어나도록 INSERT, UPDATE,   
  6. -- DELETE에 대한 Trigger를 각각 작성하라.   
  7.  -------------------------------------------------------------   
  8.        강남구      서울시   
  9.  -------------+---------------+--------------+----------------   
  10.  n(varchar(20)) |phone(char(9))  |n(varchar(20))  |phone(char(9))    
  11.  -------------+---------------+--------------+----------------   
  12.                    |                     |                    |   
  13.  -------------+-----------------------------------------------   
  14. --   
  15. CREATE TABLE TEL_KANGNAM   
  16. (   
  17.  N VARCHAR(20),   
  18.  PHONE CHAR(9)   
  19. )   
  20. CREATE TABLE TEL_SEOUL   
  21. (   
  22.  N VARCHAR(20),   
  23.  PHONE CHAR(9)   
  24. )   
  25. -- 삽입 트리거   
  26. CREATE TRIGGER TRG_TEL_INSERT   
  27. ON TEL_KANGNAM   
  28. FOR INSERT  
  29. AS  
  30. INSERT INTO TEL_SEOUL SELECT * FROM INSERTED   
  31. --   
  32. INSERT TEL_KANGNAM VALUES('AAA', '111-1111')   
  33. INSERT TEL_KANGNAM VALUES('BBB', '222-2222')   
  34. INSERT TEL_KANGNAM VALUES('CCC', '333-3333')   
  35. --   
  36. SELECT * FROM TEL_KANGNAM   
  37. SELECT * FROM TEL_SEOUL   
  38. --   
  39. -- 수정 트리거   
  40. CREATE TRIGGER TRG_TEL_UPDATE   
  41. ON TEL_KANGNAM   
  42. FOR UPDATE  
  43. AS  
  44. IF UPDATE(N)   
  45. BEGIN  
  46. UPDATE TEL_SEOUL SET TEL_SEOUL.N = INSERTED.N   
  47. FROM TEL_SEOUL, INSERTED   
  48. WHERE TEL_SEOUL.PHONE = INSERTED.PHONE   
  49. END  
  50. ELSE IF UPDATE(PHONE)   
  51. BEGIN  
  52. UPDATE TEL_SEOUL SET TEL_SEOUL.PHONE = INSERTED.PHONE   
  53. FROM TEL_SEOUL, INSERTED   
  54. WHERE TEL_SEOUL.N = INSERTED.N   
  55. END  
  56. --   
  57. UPDATE TEL_KANGNAM SET N = '111' WHERE N= 'AAA'  
  58. UPDATE TEL_KANGNAM SET N = 'AAA' WHERE N= '111'  
  59. UPDATE TEL_KANGNAM SET PHONE = '444-4444' WHERE PHONE = '111-1111'  
  60. UPDATE TEL_KANGNAM SET PHONE = '111-1111' WHERE PHONE = '444-4444'  
  61. --   
  62. SELECT * FROM TEL_KANGNAM   
  63. SELECT * FROM TEL_SEOUL   
  64. --   
  65. -- 삭제 트리거   
  66. CREATE TRIGGER TRG_TEL_DELETE   
  67. ON TEL_KANGNAM   
  68. FOR DELETE  
  69. AS  
  70. DELETE TEL_SEOUL WHERE N IN (SELECT N FROM DELETED)   
  71. --   
  72. BEGIN TRAN   
  73. DELETE TEL_KANGNAM WHERE N = 'BBB'  
  74. SELECT * FROM TEL_KANGNAM   
  75. SELECT * FROM TEL_SEOUL   
  76. ROLLBACK  
  77. --   
  78. --2. EMP 테이블에서 INSERT시는 "데이터가 입력되었습니다."   
  79. -- UPDATE시에는 "데이터가 변경되었습니다."   
  80. -- DELETE시는 "데이터가 삭제되었습니다"라는 이벤트를    
  81. -- 발생시키는 트리거를 생성하시오.   
  82. CREATE TRIGGER TRG_EMP_EVENT   
  83. ON EMP   
  84. FOR INSERT, UPDATE, DELETE  
  85. AS  
  86. DECLARE @IFLAG INT, @DFLAG INT  
  87. SET @IFLAG = (SELECT COUNT(*) FROM INSERTED)   
  88. SET @DFLAG = (SELECT COUNT(*) FROM DELETED)   
  89. IF (@IFLAG = 1 AND @DFLAG = 0)   
  90. BEGIN PRINT '데이터가 입력되었습니다.' RETURN END  
  91. IF (@IFLAG = 0 AND @DFLAG = 1)   
  92. BEGIN PRINT '데이터가 삭제되었습니다.' RETURN END  
  93. IF (@IFLAG = 1 AND @DFLAG = 1)   
  94. BEGIN PRINT '데이터가 변경되었습니다.' RETURN END  
  95. --   
  96. BEGIN TRAN   
  97. INSERT EMP VALUES(7999, 'JOHN', 'CLERK', 7788, GETDATE(), 1111, NULL, 20)   
  98. UPDATE EMP SET EMPNO = 1111 WHERE EMPNO= 7876   
  99. DELETE EMP WHERE EMPNO=7876   
  100. SELECT * FROM EMP ORDER BY HIREDATE DESC  
  101. ROLLBACK  
  102. --   
  103. --3. BONUS 테이블에 데이터가 삭제되면 다른 관리자에게    
  104. -- "데이터를 삭제했습니다."라는 메시지를 보내주는 트리거를   
  105. -- 생성하시오.   
  106. --1. Trigger 생성   
  107. CREATE TRIGGER TRG_DELETE_CALL   
  108. ON BONUS   
  109. FOR DELETE  
  110. AS  
  111. PRINT '데이터를 삭제했습니다' RETURN  
저작자표시 비영리 변경금지 (새창열림)

'내가 하는일 > SQL' 카테고리의 다른 글

오라클 조인및 서브쿼리  (0) 2010.05.13
오라클 날짜함수 계산  (0) 2010.05.11
오라클 10g 윈도우7에다가 설치하장  (0) 2010.04.13
트리거란.....  (0) 2010.02.04
컬럼값 교환  (0) 2010.02.04

'내가 하는일/SQL' Related Articles

티스토리툴바