Before Insert validation in SQL Server 2000

[craigK]

I have to do a data validation on the DB level and would like to re-format the phone number before it is inserted.

I am thinking to implement this by a trigger.

I rarely have to do something like this and don't even know if it is possible. Any help or advise would be appreciated.

thanks,

 

[SQLDenis]

>>re-format the phone number before it is inserted.


you should not format data in the DB, numbers in, formatting is done at the presentation layer

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Crowley16]

it would be better to force your users to use a stored procedure to input the data, and then do the data validation and reformatting before the insert.

a trigger is only fired after an action, not before.

--------------------
Procrastinate Now!

 

[SQLDenis]

>>a trigger is only fired after an action, not before.

There are two types of triggers, AFTER and INSTEAD

with the instead of trigger you can take an action before the insert in the table happens

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[craigK]

The problem is i do not have control of the application that is sending the data. I do have access to the database so that was my thought for a trigger, but it doesn't sound like i can do a data validation anyway? There are many reports that access the data that all have to be changed unless i can find a way to do this at the DB level.

 

[SQLDenis]

>>There are many reports that access the data that all have to be changed unless i can find a way to do this at the DB level.

that is why you use a stored proc and you do the formatting there (if you need to) Ideally formattinmg is done in the reports themselves


>>but it doesn't sound like i can do a data validation anyway?

you have to use a INSTEAD of trigger and format the data there before the insert happens

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[craigK]

So, it might be possible to use an INSTEAD OF trigger?


This is specifically what I'm trying to do.


TABLE CALLDATA

If PhoneNum = SIP:3025551212@10.1.1.200
THEN strip out the SIP: and everything to the right of the @ sign including the @ sign.

THEN insert formatted phone number into PhoneNum along with all the other columns.

 

[SQLDenis]

example,

declare @p varchar(50)

select @p = 'SIP:3025551212@10.1.1.200'

select case when charindex('@',@p,1) > 0 then replace(left(@p,charindex('@',@p,1)-1),'SIP:','')
else replace(@p,'SIP:','') end 
go

declare @p varchar(50)

select @p = 'SIP:3025551212'

select case when charindex('@',@p,1) > 0 then replace(left(@p,charindex('@',@p,1)-1),'SIP:','')
else replace(@p,'SIP:','') end 
go

declare @p varchar(50)

select @p = '3025551212@10.1.1.200'

select case when charindex('@',@p,1) > 0 then replace(left(@p,charindex('@',@p,1)-1),'SIP:','')
else replace(@p,'SIP:','') end

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[craigK]

Thanks for that. Can you give me any guidance on how it incorporate this into the INSTEAD OF trigger?

 

[SQLDenis]

Lookup INSTEAD OF INSERT Triggers in BOL

http://msdn2.microsoft.com/en-us/library/ms175089.aspx

Inside the trigger place the code I gave you, replace the variable with the column name from the table

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Crowley16]

doh, totally forgot about the instead of...

I hardly ever use triggers

--------------------
Procrastinate Now!

 

[craigK]

Thanks for your help Denis. It works perfectly!!

i thought i would post the final product in case it might help anyone else trying to do something similar.

CREATE TRIGGER [ParseSIPAddress] ON [dbo].[CallData] 
INSTEAD OF INSERT AS

    BEGIN
	INSERT INTO CallData
	(CallId,
	PhoneNumber)

	SELECT
	CallId,
	Case 
	When (Left(PhoneNumber,4)= 'SIP:') Then 
		case when charindex('@',PhoneNumber,1) > 0 then 
		replace(left(PhoneNumber,charindex('@',PhoneNumber,1)-1),'SIP:','')
		else replace(PhoneNumber,'SIP:','') end 
	Else PhoneNumber
	End
	FROM inserted
    END