时间戳

首页 > 解决方案 > 触发规则的触发器出错

sql-server - 触发规则的触发器出错

问题描述

我有一个触发器，它调用一个数据库过程，该过程记录对特定表中每一列的所有更改，但是当触发器触发时，我得到一个错误

消息 8114，级别 16，状态 5，过程 dp_PolicyBenefit_audit，第 0 行将
数据类型 nvarchar 转换为 int 时出错。

我对中的每一列都有一个INSERT和触发器，总共 100 个 - 下面的代码只显示一个UPDATEPolicyBenefit

drop procedure if exists dp_PolicyBenefit_audit
GO

create procedure dp_PolicyBenefit_audit
  @p_policy_id               varchar(20),
  @p_id                      int,
  @p_benefit_cd              char(4),
  @p_seq                     smallint,
  @p_operation               varchar(10),
  @p_description             varchar(100),
  @p_old_value               varchar(20),
  @p_new_value               varchar(20)
AS

  if (@p_operation = 'INSERT')
  begin
    insert into PolicyBenefitAudit
    (
        PolicyNo,
        EntityNo,
        BenefitCode,
        BenefitSequence,
        Description,
        OldValue,
        NewValue,
        CreatedBy, 
        CreatedDate
    )
    values
    (
      @p_policy_id,
      @p_id,
      @p_benefit_cd,
      @p_seq,
      @p_description,
      null,
      @p_new_value,
      user_name(),
      getdate()
    )
  end


  if (@p_operation = 'UPDATE') and isnull(@p_old_value,'') != isnull(@p_new_value,''))
  begin
    insert into PolicyBenefitAudit
    (
        PolicyNo,
        EntityNo,
        BenefitCode,
        BenefitSequence,
        Description,
        OldValue,
        NewValue,
        CreatedBy, 
        CreatedDate
    )
    values
    (
      @p_policy_id, 
      @p_id,
      @p_benefit_cd, 
      @p_seq,
      @p_description,
      @p_old_value,
      @p_new_value,
      user_name(),
      getdate()
    )
  end

GO


drop trigger if exists dt_PolicyBenefit_OccupationCode_upd
GO

create trigger dt_PolicyBenefit_OccupationCode_upd on PolicyBenefit
after update as
begin
    declare
        @old_OccupationCode varchar(100)
    select @old_OccupationCode = cast(OccupationCode as varchar(100)) from deleted
    exec dp_PolicyBenefit_audit @p_policy_id = PolicyNo, 
     @p_id = EntityNo, 
    @p_benefit_cd = BenefitCode, 
    @p_seq = BenefitSequence,
         @p_operation = 'UPDATE',
         @p_description = 'Occupational Per Mille loading duration (in years)',
         @p_old_value = @old_OccupationCode,
         @p_new_value = OccupationCode
end
GO

PolicyBenefitAudit期望在表中插入一行的过程

标签： sql-serverprocedureeventtrigger

解决方案

一起取消了数据库过程，并将其编码为 50 个触发器，如下所示：

create trigger dt_PolicyBenefit_OccupationalPerMilleLoadingDuration on PolicyBenefit
after insert, update as
begin
    insert into PolicyBenefitAudit
    (
        PolicyNo,
        EntityNo,
        BenefitCode,
        BenefitSequence,
        Description,
        OldValue,
        NewValue,
        CreatedBy,
        CreatedDate
    )
    select i.PolicyNo,
           i.EntityNo,
           i.BenefitCode,
           i.BenefitSequence,
           'Occupational Per Mille loading duration (in years)',
           d.OccupationalPerMilleLoadingDuration,
           i.OccupationalPerMilleLoadingDuration,
           user_name(),
           getdate()
    from   inserted i left join deleted d
    on     i.PolicyBenefitId = d.PolicyBenefitId
    where  i.OccupationalPerMilleLoadingDuration != d.OccupationalPerMilleLoadingDuration;
end
GO

推荐阅读