Clever Geek Handbook

Freeze SQL Server column to specific value

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

+1


source to share


5 answers


If this is a third party application, then I am assuming that you do not have access to the stored procedure or logic used to generate and insert this value (it is still worth checking the SP for the application database though to see if you can change them).

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

Microsoft's article here will give you details on creating triggers.



However, SQL Server does not have a "before insert" trigger (as far as I know), so you should try INSTEAD OF. Take a look here for more information. In this article, pay special attention to section 37.7 and the following example (again from this 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.

+1


source


How is the value entered? If it's through a stored procedure ... you can just change Sproc to ignore this input parameter.



if it's with a client side SQL server or ORM tool, otoh and then afaik, the only option is "Before" Trigger, which "replaces" the value with an acceptable ...

+4


source


If you are using SQL 2005, you can create an INSTEAD OF trigger. The code in this trigger runs instead of the original insert / update

-Edoode

+3


source


I would create a trigger to check and change the value

+2


source


The easiest hack is to make it varchar and insert it as a string into a column.

More complex answer: you can mass the data with a trigger, but that should still be valid in the first place anyway. For example, I can reset the value of a field in an update / insert trigger, but it still has to go through the insert first.

0


source






More articles:


All Articles