Is it possible to allow write-through computed columns for legacy code that expects a column to be read/write?

Question

I have this existing table for IP storage:

CREATE TABLE [dbo].[IPAddresses](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [IPv4Address] [varchar](15) NULL,
 [IPv6Address] [varchar](45) NULL,
 CONSTRAINT [PK_IPAddresses] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UniqueIPv4Address] UNIQUE NONCLUSTERED 
(
 [IPv4Address] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I want to convert this table to use an integer (or several integers) to store this IP information. I would like to make IPv4Address and IPv6Address computed columns based on my new columns for storage.

The problem I'm facing is that I have some legacy clients hitting this database, who need to write directly '192.168.1.54' into my IPv4Address column. I would like the database to intecept this, do a conversion to integer, and store it in a new column on the table defined as int.

Is it possible to

• Script a conversion of my existing table, converting all of my "string" IPs to their integer values and storing those in an int column, and making these "string" columns computed
• Allow legacy clients to "write" to these computed columns and intercept that data, convert it to an int, and store it transparently to legacy clients?

I am fairly sure I can write a script to conver the data, but I don't want to waste my time if I cannot make this transparent to legacy clients of my database. So any guidance will go a long way.

• Nate: @SQLKiwi, I'm well aware of that; sadly, these clients are black-box and have no support for stored procedures, or any column types besides number/text/datetime.
• Nate: @SQLKiwi No worries. I wish they had sproc support so I could make a sproc for them. At this point, they're mostly going to be viewing, so hopefully the overhead wont kill my server.

Answer 1

You can use INSTEAD OF INSERT Triggers.

• Nate: This looks great, is there something similar for UPDATE as well?
• Simon Righarts: If you look at the link Remus sent you, on the left-hand side of the page, there's a link called "INSTEAD OF UPDATE Triggers" right below "INSTEAD OF INSERT Triggers" ...
• Nate: @SimonRigharts Thanks, lost that in the mess of MSDN links, switched to scriptfree and found it right away.
• jcolebrand: Can someone update this to be "more than a link answer"? Please?
• Nate: While this seems works on a view, I cannot get this to work on a table. The server aborts my INSERT on a computed column before the trigger even happens. Is there any way around this?