1

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.

Improve this question
2
  • @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
    Commented Jan 26, 2012 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
    Commented Jan 26, 2012 at 23:08

1 Answer 1

Reset to default
3

You can use INSTEAD OF INSERT Triggers.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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