Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
@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 Jan 26 '12 at 21:54
    
@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. –  Nate Jan 26 '12 at 23:08

1 Answer 1

up vote 3 down vote accepted

You can use INSTEAD OF INSERT Triggers.

share|improve this answer
    
This looks great, is there something similar for UPDATE as well? –  Nate Jan 24 '12 at 23:22
    
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" ... –  Simon Righarts Jan 24 '12 at 23:41
    
@SimonRigharts Thanks, lost that in the mess of MSDN links, switched to scriptfree and found it right away. –  Nate Jan 24 '12 at 23:46
1  
Can someone update this to be "more than a link answer"? Please? –  jcolebrand Jan 25 '12 at 1:50
    
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? –  Nate Jan 27 '12 at 20:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.