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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-11-11 : 08:11:09
|
| Paul writes "I am writing a trigger to update another table.The field that defines the update is a character field, wherethe user can enter characters or numbers.If the character field contains a number the trigger will fire and the update will procede, if not it will stop.How do I trap the error when they enter a character in the field and prevent the trigger from running." |
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-11-11 : 10:13:38
|
| If you are saying that you want that field to content just numbers then it will be better to add a constraint so that particular field won't accept any non numeric character ([0-9]) or change the datatype to tinyint or int and set a constraint so the field value be in a range of valid numbers*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-11 : 12:01:25
|
| if exists (select * from inserted where fld like '%[^0-9]%')begin raiserror('fld not numeric',16,-1) rollback tran returnend==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Hippi
Yak Posting Veteran
63 Posts |
Posted - 2004-11-14 : 18:28:05
|
How about declare a cursor and loop throgh the inserted table?Just my two cents. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-14 : 20:58:48
|
quote:
Originally posted by Hippi
How about declare a cursor and loop throgh the inserted table?Just my two cents.
Sure, anything to do with the question though?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-15 : 00:48:49
|
| The client can validate the data in the gui, before it is sent to the database.If you are using stored procedure to enter the data as you should do, then the sp can validate the data as well.You could also put constraint on the table as tuenty suggests,or have the trigger find the rows with numeric data as nr suggest.-- No I am not paranoid :)So, should the column allow non-numeric data, and the trigger fire only for inserted rows with numeric data ?Or should the column not allow non-numeric data in the first place ?rockmoose |
|
|
|
|
|
|
Trigger