mssql trigger

트리거 예제 ..

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