Preventing invalid data from being entered

Hi,

I need to be able to prevent an invalid character from being entered into a sql 2000 databae on import from oracle.

In short, I need to exclude a certain character from being entered and need to be able to send an email which specifies that an attempt was made to enter this character, if the change was due to an insert or an update, the row to be affected in the target database, date and time info. Also the source of the data.

If this is not possible, is it viable to remove the character after insert and still send the email withe the required info?

Any one any ideas on the cleanest way to achieve this?

Thanks

Hi,

you could use a trigger for that. Check the incoming values for information about special character and put in a auditing record in another table (sending EMails from triggers is not suggested as it runs in the same transaction and can slow down your system or break your code). So a sample for doing this would be the following:

--Check the triggernestlevel to ensure that the trigger does not call an infinite loop
BEGIN TRANSACTION

INSERT INTO AuditTable
SELECT TheColumns
FROM INSERTED
WHERE CHARINDEX(SomeColumn,'SomeChartoExclude') > 0

--Then either Replace the Value or do something else
UPDATE SomeTable
SET SomeColumn = REPLACE(SomeColumn, 'SomeChartoExclude','ReplacedChar')
INNER JOIN INSERTED
ON I.PrimaryKeyCol 0 S.PrimaryKeyCol
WHERE CHARINDEX(SomeColumn,'SomeChartoExclude') > 0

COMMIT

In addition put error handling in your trigger if you want to catch the error that might occur.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de