Trigger erroring at varchar field: String or binary data would be truncated

• Zososql

  Ten Centuries

  Points: 1218

  More actions

  March 9, 2017 at 7:18 pm

  #322447

  I am trying to use an insert trigger by mirroring inserts into Orders table towards an Orders_arch table that has the same DDL structure . I am getting a String or binary data would be truncated error on the status field, but the data i am entering I am maknig sure is not more than 50 characters :

  TABLE dbo.orders
  (
  OrderID (pk, int, NOT NULL),
  CustomerID varchar(5) NOT NULL,
  trackid (varchar(50) NOT NULL),
  Status varchar(50) null,
  Ordershipped datetime NULL,
  Orderarrived datetime NULL
  CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
  );

  USE [mydb]
  GO

  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER TRIGGER [dbo].[trInsert] on [dbo].[Orders]
  FOR INSERT
  AS declare @trackid varchar(50),@custid varchar(5),
  @status varchar(50), @ordershipped datetime;

  select @trackid=i.trackid from inserted i;
  select @custid=i.customer from inserted i;
  select @status=i.status from inserted i;
  select @ordershipped=i.rdershipped from inserted i;

  insert into orders_arch(trackid,custid
  ,status,ordershipped)
  values (@trackid,@custid,@status,@ordershipped);

  ???
  Thanks in advance

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  March 10, 2017 at 7:12 am

  #1932702

  Is the OrderID column in the Orders table an IDENTITY column?  Either way if it is or not I'd imagine that it would not be IDENTITY in the orders_arch table, so that you could ensure the same matching OrderID could be put into both tables, and if they are both IDENTITY then you cannot ensure that.  Also, This trigger won't handle the case if multiple orders get inserted at the same time, since you're using variables.  I'm thinking it could be simplified like this:
  
CREATE TRIGGER [dbo].[trInsert] on [dbo].[Orders] FOR INSERT
AS
INSERT INTO orders_arch
    (OrderID, CustomerID, trackid, Status, Ordershipped, Orderarrived)
SELECT OrderID, CustomerID, trackid, Status, Ordershipped, Orderarrived
  FROM inserted
GO


  as for the truncation error, I'd ensure the table definitions really are the same, your Orders table and variables look the same here, but none of the code as you present it runs without erroring so I'm not sure what it really looks like on your system.  You can compare the tables like this:
  
SELECT o.COLUMN_NAME, o.DATA_TYPE, o.CHARACTER_MAXIMUM_LENGTH, o.NUMERIC_PRECISION, o.DATETIME_PRECISION, a.COLUMN_NAME, a.DATA_TYPE, a.CHARACTER_MAXIMUM_LENGTH, a.NUMERIC_PRECISION, a.DATETIME_PRECISION
  FROM INFORMATION_SCHEMA.COLUMNS o
  FULL OUTER JOIN INFORMATION_SCHEMA.COLUMNS a ON o.COLUMN_NAME = a.COLUMN_NAME AND a.TABLE_SCHEMA = N'dbo' AND a.TABLE_NAME = N'Orders_arch'
  WHERE o.TABLE_SCHEMA = N'dbo' AND o.TABLE_NAME = N'Orders'