상세 컨텐츠

본문 제목

mssql trigger

IT 세상/DB세상

by 이현민 (지후지율아빠) 2011. 1. 10. 17:03

본문


트리거 예제 ..

board
----------------------------------------------------------
ALTER TRIGGER [dbo].[srch_queue_delete] ON BoardBase
FOR  DELETE
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 SELECT @srchkey = ArticleCD, @action = 'D'
 FROM DELETED

 INSERT INTO board_queue VALUES (@srchkey, @action, 1);
 INSERT INTO board_queue VALUES (@srchkey, @action, 2);

 


ALTER TRIGGER [dbo].[srch_queue_insert] ON BoardBase
FOR INSERT
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 SELECT @srchkey = ArticleCD, @action = 'I'
 FROM INSERTED

 INSERT INTO board_queue VALUES (@srchkey, @action, 1);
 INSERT INTO board_queue VALUES (@srchkey, @action, 2);


ALTER TRIGGER [dbo].[srch_queue_update] ON BoardBase
FOR UPDATE
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 IF UPDATE(Title) OR UPDATE(Contents) BEGIN
  SELECT @srchkey = ArticleCD, @action = 'U'
  FROM INSERTED

  INSERT INTO board_queue VALUES (@srchkey, @action, 1);
  INSERT INTO board_queue VALUES (@srchkey, @action, 2);
 END

 

kms
-------------------------------------------------------------
idea_queue_update
ALTER TRIGGER [EIPKSILOUser].[idea_queue_update] ON EIPKSILOUser.IdeaDocContent
FOR UPDATE
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 IF UPDATE(OldMethod) OR UPDATE(NewMethod) OR UPDATE(EffectCont) OR UPDATE(ProfitCont) BEGIN
  SELECT @srchkey = DocSeq, @action = 'U'
  FROM INSERTED

  INSERT INTO idea_queue VALUES (@srchkey, @action, 1);
  INSERT INTO idea_queue VALUES (@srchkey, @action, 2);
 END


ALTER TRIGGER [EIPKSILOUser].[idea_srch_queue_delete] ON EIPKSILOUser.IdeaDOC
FOR UPDATE
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 IF UPDATE(DelYN) BEGIN
  SELECT @srchkey = DocSeq, @action = 'D'
  FROM INSERTED

  INSERT INTO idea_queue VALUES (@srchkey, @action, 1);
  INSERT INTO idea_queue VALUES (@srchkey, @action, 2);
 END


ALTER TRIGGER [EIPKSILOUser].[idea_srch_queue_insert] ON EIPKSILOUser.IdeaDOC
FOR INSERT
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 SELECT @srchkey = DocSeq, @action = 'I'
 FROM INSERTED 

 INSERT INTO idea_queue VALUES (@srchkey, @action, 1);
 INSERT INTO idea_queue VALUES (@srchkey, @action, 2);


ALTER TRIGGER [EIPKSILOUser].[idea_srch_queue_update] ON EIPKSILOUser.IdeaDOC
FOR UPDATE
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 IF UPDATE(DocTitle) OR UPDATE(RejectYN) OR UPDATE(StatusCD) OR UPDATE(SendYN) BEGIN
  SELECT @srchkey = DocSeq, @action = 'U'
  FROM INSERTED

  INSERT INTO idea_queue VALUES (@srchkey, @action, 1);
  INSERT INTO idea_queue VALUES (@srchkey, @action, 2);
 END

 

ALTER TRIGGER [EIPKSILOUser].[qa_answer_srch_queue_delete] ON EIPKSILOUser.QAAnswer
FOR UPDATE
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 IF UPDATE(UseYN) BEGIN
  SELECT @srchkey = QueSeq, @action = 'D'
  FROM INSERTED

  INSERT INTO qa_queue VALUES (@srchkey, @action, 1);
  INSERT INTO qa_queue VALUES (@srchkey, @action, 2);
 END

 

ALTER TRIGGER [EIPKSILOUser].[qa_answer_srch_queue_insert] ON EIPKSILOUser.QAAnswer
FOR INSERT
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 SELECT @srchkey = QueSeq, @action = 'I'
 FROM INSERTED

 INSERT INTO qa_queue VALUES (@srchkey, @action, 1);
 INSERT INTO qa_queue VALUES (@srchkey, @action, 2);

 

ALTER TRIGGER [EIPKSILOUser].[qa_answer_srch_queue_update] ON EIPKSILOUser.QAAnswer
FOR UPDATE
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 IF UPDATE(Title) OR UPDATE(Contents) BEGIN
  SELECT @srchkey = QueSeq, @action = 'U'
  FROM INSERTED

  INSERT INTO qa_queue VALUES (@srchkey, @action, 1);
  INSERT INTO qa_queue VALUES (@srchkey, @action, 2);
 END

 

ALTER TRIGGER [EIPKSILOUser].[qa_question_srch_queue_delete] ON EIPKSILOUser.QAQuestion
FOR UPDATE
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 IF UPDATE(UseYN) BEGIN
  SELECT @srchkey = QueSeq, @action = 'D'
  FROM INSERTED

  INSERT INTO qa_queue VALUES (@srchkey, @action, 1);
  INSERT INTO qa_queue VALUES (@srchkey, @action, 2);
 END

 

ALTER TRIGGER [EIPKSILOUser].[qa_question_srch_queue_insert] ON EIPKSILOUser.QAQuestion
FOR INSERT
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 SELECT @srchkey = QueSeq, @action = 'I'
 FROM INSERTED

 INSERT INTO qa_queue VALUES (@srchkey, @action, 1);
 INSERT INTO qa_queue VALUES (@srchkey, @action, 2);

 


ALTER TRIGGER [EIPKSILOUser].[qa_question_srch_queue_update] ON EIPKSILOUser.QAQuestion
FOR UPDATE
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 IF UPDATE(Title) OR UPDATE(Contents) BEGIN
  SELECT @srchkey = QueSeq, @action = 'U'
  FROM INSERTED

  INSERT INTO qa_queue VALUES (@srchkey, @action, 1);
  INSERT INTO qa_queue VALUES (@srchkey, @action, 2);
 END

 

ALTER TRIGGER [EIPKSILOUser].[srch_queue_delete] ON EIPKSILOUser.KSiloDOC
FOR UPDATE
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 IF UPDATE(DocDelYn) BEGIN
  SELECT @srchkey = DocID, @action = 'D'
  FROM INSERTED

  INSERT INTO kms_queue VALUES (@srchkey, @action, 1);
  INSERT INTO kms_queue VALUES (@srchkey, @action, 2);
 END

 

ALTER TRIGGER [EIPKSILOUser].[srch_queue_insert] ON EIPKSILOUser.KSiloDOC
FOR INSERT
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 SELECT @srchkey = DocID, @action = 'I'
 FROM INSERTED

 INSERT INTO kms_queue VALUES (@srchkey, @action, 1);
 INSERT INTO kms_queue VALUES (@srchkey, @action, 2);

 

ALTER TRIGGER [EIPKSILOUser].[srch_queue_update] ON EIPKSILOUser.KSiloDOC
FOR UPDATE
AS
 DECLARE @srchkey VARCHAR(20)
 DECLARE @action CHAR(1)

 IF UPDATE(DocTitle) OR UPDATE(DocContents) OR UPDATE(DocKeyword) OR UPDATE(RegFlag)BEGIN
  SELECT @srchkey = DocID, @action = 'U'
  FROM INSERTED

  INSERT INTO kms_queue VALUES (@srchkey, @action, 1);
  INSERT INTO kms_queue VALUES (@srchkey, @action, 2);
 END

반응형

'IT 세상 > DB세상' 카테고리의 다른 글

sybase jdbc6* 로 timeout 해결  (0) 2011.03.11
oracle - 시간구하기  (0) 2011.01.19
Derby 사용법 [펌]  (0) 2010.05.26
aqua derby 접속 방법  (0) 2010.04.28
13.2.5. LOAD DATA INFILE Syntax  (0) 2010.03.22

관련글 더보기