SQL SERVER - SET FIELD (COLUMN) TO DEFAULT VALUE? 1

[bawtry]

Given a Field (Column) called PhoneNo ... need to SET PhoneNo = '' if supplied value is Null.
SQL DEFAULT is OK for INSERT. How do you do this for UPDATE?
HINTS: Client is Access making the update directly ... so can't use S-PROC.
Don't want NULL to be allowable in Table Def ... Access handling of NULL fields is problematic.
Access BOUND forms give no opportunity to intervene and replace NULL PhoneNo with "" on the client
Any suggestions, please?

 

[rajeevnandanmishra]

Hi bawtry,

I think the only possible solution for you is having a trigger for update on your table.
Like :
---------------
CREATE TRIGGER myTableTrigger ON myTable FOR UPDATE
AS
IF UPDATE(phone_no) AND
EXISTS(SELECT * FROM inserted WHERE phone_no is null)
UPDATE myTable
SET phone_no=myDefaultValue
FROM myTable a, inserted b
WHERE a.myPrimaryKey=b.myPrimaryKey And bphone_no is null
RETURN
---------------

The trigger will be executed irrespective updation occur from front-end or backend.