title: Trigger that fires a rule is erroring
body: <p>I have a trigger that calls a database procedure which logs all changes to every column in a particular table but when the trigger fires I get an error</p>
  
  <blockquote>
    <p>Msg 8114, Level 16, State 5, Procedure dp_PolicyBenefit_audit, Line 0<br>
    Error converting data type nvarchar to int.</p>
  </blockquote>
  
  <p>I have an <code>INSERT</code> and <code>UPDATE</code> trigger for every column in <code>PolicyBenefit</code>, 100 in all - the code below shows just one</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Expecting procedure to insert a row into the <code>PolicyBenefitAudit</code> table</p>