I need to validate the data in the following table. (Update: in the insert and update trigger of table
create table M (Column_Name sysname primary key, FirstValue varchar(8000))
The table has a trigger to ensure that all the values of
Column_Name are in
(select column_name from information_schema.columns where table_name = 'T').
What's the best way to guarantee that
M.FirstValue can be inserted into
T without any issue. Is there any third party function accept the parameters of
(Value, Data_type, Max_..._length, etc) where the data type information can be provided by
** I am trying to ensure that the value in FirstValue can be inserted into the column whose name is stored in the Column_Name column.
Thanks for clarifying your question. There's no built-in function or procedure to do that so you'll probably have to write your own. One way would be to try to
CAST the value to the same data type as the column. If you get a conversion error, you know an
INSERT would fail.
But, there's really no easy and reliable way, because even if the value is compatible with the data type, there may be a
CHECK constraint or trigger on the table that prevents it being inserted for some other reason. And since you store all values as strings, there are always potential issues such as conversions to datetime that work fine for you but fail for someone else because of different language or date format options.