ODBC from External software

[nohandlesleft254]

Hi,

I am using a DB on sql server 2005 for an online account systerm. The sql db is a live copy of our internal systems DB (ODBC). My problem is that sometimes the internal system can send blank field values out to a numeric field in the SQL db, it fails to convert and stalls the system. I dont have much control over the internal system, so im looking for a way build in some sort of failsafe (eg if its blank, insert '0' or something) ...

thanks

 

[fredericofonseca]

use a trigger to do the validation

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[nohandlesleft254]

Hi fredericofonseca,

Do you think you could give me a hint on the validating as numeric? Have only really done triggers to update other tables when something happens, didnt realise i could check the field value...

Thanks

 

[fredericofonseca]

You can do validations in any field of the table being changed. Books online do have same samples. On your case you are looking for validation on the "inserted" logical table

see thread183-1240340 for validation of a numeric field.

As for the trigger bit, its just like any other, so if you have already done for updates, you can also do this one easily.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[nohandlesleft254]

Ok, think im there - one question though... i can see how i would pull the fields from the 'inserted' table and do the validation, is it then just a case of performing an 'insert' with the newly formatted info? Or are the values written before the trigger event starts?

 

[fredericofonseca]

Does not matter.

The inserted table will contain the new value that you are attempting to insert.
If it is invalid then just RAISE an error and the operation will fail.

Against what I was thinking you can not change the value of the inserted column, so the only option I can see is to raise the error.

Eventually this will not help you as the invalid numeric field may even be trapped before the insert.

The only other option I can see is to use s INSTEAD OF trigger where you can use the inserted columns values, validate them, and then do a insert into the table using those values.
sample from BOL

CREATE TABLE BaseTable
  (PrimaryKey     int IDENTITY(1,1),
   Color          nvarchar(10) NOT NULL,
   Material       nvarchar(10) NOT NULL,
   ComputedCol AS (Color + Material)
  )
GO


--Create an INSTEAD OF INSERT trigger on the table.
CREATE TRIGGER InsteadTrigger on BaseTable
INSTEAD OF INSERT
AS
BEGIN
  --Build an INSERT statement ignoring inserted.PrimaryKey and 
  --inserted.ComputedCol.
  INSERT INTO BaseTable
       SELECT Color, Material
       FROM inserted
END
GO

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com