0

I have a SQL server Table that has a varchar column that can save up to 4 characters

When we insert a value 963 into this column we have to add a leading "0" to this
for example:

  • if I insert 23 the value should be saved as 0023
  • if I insert 236 the value should be save as 0236
  • if I insert 2369 the value should be saves as 2369

Can we add a AFTER trigger to this table, to check if the value inserted is less than 4 digits, and to update the same value in that column with Leading "0"s appended to that value

Will this affect the performance of the Trigger? Is it a good practice to have a trigger to update the value in the same table?

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
nitin madan
  • 31
  • 6
  • 2
    I would recommend using a computed column instead of changing your data like this. What you have is a fixed width string instead of an int. This screams of poor design to me. Yes you could do this in a trigger but why do you need to have leading zeros like this? – Sean Lange Aug 19 '15 at 14:54
  • these are kind of codes given to a specific set of users for example "0239" is a identification for a company named "ABC" and ""0023" is for a company named "XYZ" - hope this helps answer your question , – nitin madan Aug 19 '15 at 14:55
  • Not sure what specific users has to do with it. If you want these values for display or output that would be the right time to add your padding. – Sean Lange Aug 19 '15 at 14:56
  • 1
    ... or format the data in the select statement ([see how to pad a string](http://stackoverflow.com/questions/29866779/using-a-sql-function-to-pad-strings-with-spaces/29866897#29866897)) or better yet, in the presentation layer. since mathematically there is no meaning to leading zeros, application-wise there should also be no meaning to them. – Zohar Peled Aug 19 '15 at 14:57

1 Answers1

1

Yes, you can create a trigger and for one operation it almost did not affect the performance.
Another way is to change the value when it use in select. e.g 

select right('0000' + column, 4), ....
Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116