March 9, 2017 at 7:18 pm
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
March 10, 2017 at 7:12 am
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'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply