TR_VENDOR1099DISTRIBUTION_VALIDSTATE (Trigger)

                
CREATE trigger dbo.TR_VENDOR1099DISTRIBUTION_VALIDSTATE 
  on VENDOR1099DISTRIBUTION for insert, update not for replication as
begin
  set nocount on;
  if exists
  ( 
    select *
    from inserted I
    left join dbo.TAXID T 
    on (I.STATEID = T.STATEID and T.STATUSCODE = 1)
    where 
    (I.STATEID is not null and T.STATEID is null)
  )
  begin
    raiserror ('The State code has not been created in Manage TaxID.', 16, 1);
    rollback;
  end
  
  if exists(select I.ID
    from INSERTED I
    inner join dbo.FINANCIALTRANSACTION1099BOXNUMBER B on B.ID = I.BOXNUMBER1099ID
    where B.BOXNUMBER = '4' and not I.STATEID is null)
  begin
    raiserror ('ERR_1099DISTRIBUTION_BOX4_STATE_NOT_ALLOWED', 16, 1);
    rollback;
  end
  
  if exists(select I.ID
    from INSERTED I
    inner join dbo.FINANCIALTRANSACTION1099BOXNUMBER B on B.ID = I.BOXNUMBER1099ID
    where B.BOXNUMBER = '16' and I.STATEID is null)
  begin
    raiserror ('ERR_1099DISTRIBUTION_BOX16_STATE_IS_REQUIRED', 16, 1);
    rollback;
  end
end