触发器编写的例子

USE [ei]
GO
/****** Object:  Trigger [dbo].[TRG_tbAccountKind_INSERT]    Script Date: 03/12/2011 09:07:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: laozhou
-- Create date: 2010,7,16
-- Description: 新增触发器，新增的相关信息保存到message表里面去。
-- =============================================
ALTER TRIGGER [dbo].[TRG_tbAccountKind_INSERT] ON [dbo].[tbAccountKind]
FOR INSERT
AS

DECLARE
  @MES_ID INT,
  @MES_DET_ID INT,
  @V_AccountKindNo INT,
  @V_AccountKindName VARCHAR(MAX),
  @V_ID UNIQUEIDENTIFIER,
  @V_RecordStatus INT,
  @V_UpdateTime DATETIME

BEGIN TRAN

SELECT @V_ID=ID,@V_AccountKindNo=AccountKindNo,@V_AccountKindName=AccountKindName,@V_RecordStatus=RecordStatus,@V_UpdateTime=UpdateTime FROM INSERTED;
    BEGIN
      
     SELECT @MES_ID=SEQ_VALUE FROM SEQUENCE WHERE SEQ_NAME='MES_SEQ'
INSERT INTO UDP_MESSAGES(MES_ID,DB_CON_FLAG,DAT_STA_SET_FLAG,MES_REPORT_TYPE,MES_CREATE_DATETIME,MES_REPORT_STATUS,MES_REPORT_DESC) VALUES(@MES_ID,'ei','tbAccountKind','ADD',GETDATE(),0,'等待上报')
UPDATE SEQUENCE SET SEQ_VALUE=@MES_ID+1 WHERE SEQ_NAME='MES_SEQ'

SELECT @MES_DET_ID=SEQ_VALUE FROM SEQUENCE WHERE SEQ_NAME='MES_DET_SEQ'

INSERT INTO UDP_MESSAGE_DETAIL(MES_DET_ID,MES_ID,MES_DET_UNIQUE,MES_DET_FIELD,MES_DET_VALUE) VALUES(@MES_DET_ID,@MES_ID,1,'AccountKindNo',@V_AccountKindNo);
UPDATE SEQUENCE SET SEQ_VALUE=@MES_DET_ID+1 WHERE SEQ_NAME='MES_DET_SEQ'
INSERT INTO UDP_MESSAGE_DETAIL(MES_DET_ID,MES_ID,MES_DET_UNIQUE,MES_DET_FIELD,MES_DET_VALUE) VALUES(@MES_DET_ID+1,@MES_ID,0,'AccountKindName',@V_AccountKindName);
UPDATE SEQUENCE SET SEQ_VALUE=@MES_DET_ID+2 WHERE SEQ_NAME='MES_DET_SEQ'
INSERT INTO UDP_MESSAGE_DETAIL(MES_DET_ID,MES_ID,MES_DET_UNIQUE,MES_DET_FIELD,MES_DET_VALUE) VALUES(@MES_DET_ID+2,@MES_ID,0,'ID',@V_ID);
UPDATE SEQUENCE SET SEQ_VALUE=@MES_DET_ID+3 WHERE SEQ_NAME='MES_DET_SEQ'
INSERT INTO UDP_MESSAGE_DETAIL(MES_DET_ID,MES_ID,MES_DET_UNIQUE,MES_DET_FIELD,MES_DET_VALUE) VALUES(@MES_DET_ID+3,@MES_ID,0,'RecordStatus',@V_RecordStatus);
UPDATE SEQUENCE SET SEQ_VALUE=@MES_DET_ID+4 WHERE SEQ_NAME='MES_DET_SEQ'
INSERT INTO UDP_MESSAGE_DETAIL(MES_DET_ID,MES_ID,MES_DET_UNIQUE,MES_DET_FIELD,MES_DET_VALUE) VALUES(@MES_DET_ID+4,@MES_ID,0,'UpdateTime',@V_UpdateTime);
UPDATE SEQUENCE SET SEQ_VALUE=@MES_DET_ID+5 WHERE SEQ_NAME='MES_DET_SEQ'

END
COMMIT TRAN;
    RETURN;