Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ODBC from External software

Status
Not open for further replies.

nohandlesleft254

IS-IT--Management
Apr 19, 2006
58
GB
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
 
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
 
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
 
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?

 
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
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top