1. 数据异常跟踪

由于不知道数据异常的原因，所有就只能在异常数据的表或存储过程中添加跟踪日志。

--首先创建日志表
CREATE TABLE [dbo].[mps_debug_inputbuffer](
    [EventType] [nvarchar](30) NULL,
    [Parameters] [smallint] NULL,
    [EventInfo] [nvarchar](4000) NULL
) ON [PRIMARY]

GO
--在要跟踪的表（触发器）或存储过程中添加如下代码
insert into mps_debug_inputbuffer
exec('dbcc INPUTBUFFER( @@SPID )')

-- 异常时查询原因
select * from mps_debug_inputbuffer

1.1. 实例

比如记录权限设置的表出现数据异常的情况，可用触发器记录原因

CREATE TABLE dbo.mps_permission_log (
    mp_logid int NOT NULL  primary key IDENTITY(1,1),
    mp_id     int NOT NULL,
    mp_cmpid int NULL DEFAULT ((0)),
    mp_type varchar(20) NULL DEFAULT (''),
    mp_ctgid int NULL DEFAULT ((0)),
    mp_group int NULL DEFAULT ((0)),
    mp_ppid int NULL DEFAULT ((0)),
    mp_objid1 int NULL DEFAULT ((0)),
    mp_objid2 int NULL DEFAULT ((0)),
    mp_caption nvarchar(200) NULL DEFAULT (''),
    mp_sql nvarchar(2000) NULL DEFAULT (''),
    mp_order int NULL DEFAULT ((0)),
    mp_updated int NULL DEFAULT ((0)),
    mp_level int NOT NULL DEFAULT ((0)),
    [mp_EventType] [nvarchar](30) NULL,
    [mp_Parameters] [smallint] NULL,
    [mp_EventInfo] [nvarchar](4000) NULL,
  mp_operatedate datetime default(getdate()),
  mp_hostname varchar(100) default(''),
    mp_loginname varchar(100) default(''),
    mp_logintime datetime,
    mp_programe_name nvarchar(100) default('')
);
GO
ALTER  trigger [dbo].[mps_permission_delete] on [dbo].[mps_permission] for delete as
begin

        declare @cmpid int 
        select top 1 @cmpid = mp_cmpid from  deleted 
        set @cmpid = isnull(@cmpid,0)

        if (not exists (select 1 from mps_command where cmd_cmpid=@cmpid and cmd_code='rightPart')) 
        begin
            insert into mps_command(cmd_code,cmd_cmpid) values('rightPart',@cmpid) 
        end 

        declare @t table (
            [EventType] [nvarchar](30) NULL,
            [Parameters] [smallint] NULL,
            [EventInfo] [nvarchar](4000) NULL
        ) 

        insert into @t
        exec('dbcc INPUTBUFFER( @@SPID )')

        insert into mps_permission_log(
                mp_id,
                mp_cmpid,
                mp_type,
                mp_ctgid,
                mp_group,
                mp_ppid,
                mp_objid1,
                mp_objid2,
                mp_caption,
                mp_sql,
                mp_order,
                mp_updated,
                mp_level,
                mp_EventType,
                mp_Parameters,
                mp_EventInfo,
            mp_hostname,
                mp_loginname,
                mp_logintime,
                mp_programe_name
    )
        select mp_id,
                mp_cmpid,
                mp_type,
                mp_ctgid,
                mp_group,
                mp_ppid,
                mp_objid1,
                mp_objid2,
                mp_caption,
                mp_sql,
                mp_order,
                mp_updated,
                mp_level,
                EventType,
                Parameters,
                EventInfo,
                host_name,
                login_name,
                login_time,
                program_name
        from @t,deleted

end