Force SQL Server column to a specific value

Question

Is it possible to force a column in a SQL Server 2005 table to a certain value regardless of the value used in an insert or update statement is? Basically, there is a bug in an application that I don't have access to that is trying to insert a date of 1/1/0001 into a datetime column. This is producing a SqlDateTime overflow exception. Since this column isn't even used for anything, I'd like to somehow update the constraints on the columns or something in the database to avoid the error. This is obviously just a temporary emergency patch to avoid the problem... Ideas welcome...

Answer 1

How is the value being inserted? If it's through a stored proc... you could just modify the Sproc to ignore that input parameter.

if it's through client-side generated SQL, or an ORM tool, otoh, then afaik, the only option is a "Before" Trigger that "replaces" the value with an acceptable one...

Answer 2

the simplest hack would be to make it a varchar, and let it insert that as a string into the column.

The more complicated answer is, you can massage the data with a trigger, but it would still have to be valid in the first place. For instance I can reset a fields value in an update/insert trigger, but it would still have to get through the insert first.

Answer 3

I'd create a trigger to check and change the value

Answer 4

If you're using SQL 2005 you can create an INSTEAD OF trigger. The code in this trigger wil run in stead of the original insert/update

-Edoode

Answer 5

If it is a third party application then I will assume you don't have access to the Stored Procedure, or logic used to generate and insert that value (it is still worth checking the SPs for the application's database though, to see if you can modify them).

As Charles suggested, if you don't have access to the source, then you need to have a trigger on the insert.

The Microsoft article here will give you some in depth information on creating triggers.

However, SQL Server doesn't have a true 'before insert' trigger (to my knowledge), so you need to try INSTEAD OF. Have a look here for more information. In that article, pay particular note of section 37.7, and the following example (again from that article):

CREATE TRIGGER T_InsertInventory ON CurrentInventory
INSTEAD OF INSERT AS
BEGIN
INSERT INTO Inventory (PartNumber, Description, QtyOnOrder, QtyInStock)
SELECT PartNumber, Description, QtyOnOrder, QtyInStock
FROM inserted
END

Nick.