SQL Trigger Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Trigger Help

Author

Topic

tmcivery
Starting Member

46 Posts

Posted - 2009-07-09 : 13:29:37

Hello all,

I'm pretty new to all of this and was hoping for some guidance.

I'm attempting to create a trigger that will update a field named "Organisation" with a specific value that I will assign based off an IP address range(192.169.1.1 through 192.168.1.100 would go into OU "5" for example).

The table name is "Client" and both IP address and Organisation are fields within this table. When a record gets added to the "Client" table or if the IP Address field is updated I will need the Organisation field filled out appropriately. There will be multiple IP ranges so I'm sure I'll have to do an IF/THEN/ELSE statement but I'm more than a little confused on how to get it done with a trigger.

Any help would be greatly appreciated.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 14:04:47

I assume your IP Address are stored as VARCHAR? Are the Octets stored speparatly and/or did you conver the IP into a number?

tmcivery
Starting Member

46 Posts

Posted - 2009-07-09 : 14:13:47

The IP Address is Varchar(64). The octets are not stored separately. Will this cause a big problem?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 14:14:50

I'm sure you can search for functions people have written to do this, but here are some ways to convert your IP Address into something usable for a range comparison:

DECLARE @IPAddress VARCHAR(15)
SET @IPAddress = '192.169.1.1'

-- Parse octets
SELECT
	PARSENAME(@IPAddress, 4),
	PARSENAME(@IPAddress, 3),
	PARSENAME(@IPAddress, 2),
	PARSENAME(@IPAddress, 1)

--Convert to number
SELECT
	CAST(PARSENAME(@IPAddress, 4) AS BIGINT) * POWER(256, 3)
	+ 	CAST(PARSENAME(@IPAddress, 3) AS BIGINT) * POWER(256, 2)
	+ 	CAST(PARSENAME(@IPAddress, 2) AS BIGINT) * POWER(256, 1)
	+ 	CAST(PARSENAME(@IPAddress, 1) AS BIGINT) * POWER(256, 0)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 14:15:31

quote:
Originally posted by tmcivery

The IP Address is Varchar(64). The octets are not stored separately. Will this cause a big problem?

Not sure about BIG problems, but there is going be some function calls in order to get the data in a consumable format.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources