SQLTeam.com | Weblogs | Forums

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


#1

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


#2

Hi sql2017, provide your orders_arch table definition and insert statement that inserts into Orders table.


#3

What happens if there is more than one row in INSERTED?

You are probably assuming that the trigger is called for EACH ROW that is inserted? That's not the case, the trigger is called once for each INSERT statement, and [INSERTED] pseudo-table will have as many rows as are being inserted.

Not sure why you want to put the values into @Variables and then insert them, can you not do this?:

ALTER TRIGGER [dbo].[trInsert] on [dbo].[Orders] 
FOR INSERT 
AS

INSERT INTO orders_arch
(
	trackid, custid, status, ordershipped
)
SELECT	trackid, customer, status, rdershipped
FROM inserted AS i

note that you are doing

select @custid=i.customer
select @ordershipped=i.rdershipped

which both look like they might be errors - does orders.customer match orders_arch.custid?

The second looks like a typing mistake