SQL Server - Default value

Question

I don't know if this is possible, but i would like to know if when we create a table on which a field has a default value, we can make this Default value get the value of another column upon row insertion.

Create Table Countries (
    ID Int Not Null Identity(1,1),
    CountryCode Char (2) Not Null,
    Country Varchar (50) Not Null,
    CountryRegion Varchar (50) Null Default ('Country'),
    Nationality Varchar (75) Not Null Default ('Not Known'),
    InsertDate Datetime2 Not Null Default Getdate(),
        Constraint PK_CountryCode Primary Key (CountryCode));

On CountryRegion field, I could place an ('Unknown') default value, but like I said, is it possible this field gets by default the value inserted on Country field if nothing is placed on it?

You could do this with a trigger. In other words, before insert, if the field is null then set it to the value in the other field. — Brian DeMilia, Aug 31 '14 at 01:46
Triggers tend to make a database hard to maintain. I'd solve this in the application layer. — Andomar, Aug 31 '14 at 03:33

score 1 · Accepted Answer · edited Aug 31 '14 at 03:32

1

Using a trigger would do - UPDATE a column right after insertion

CREATE TRIGGER CountriesTrigger ON Countries AFTER INSERT        
AS
BEGIN

  SET NOCOUNT ON

  UPDATE Countries
  SET
    Countries.CountryRegion = inserted.Country
  FROM Countries, inserted
  WHERE Countries.ID = inserted.ID 
        AND inserted.CountryRegion is null

END

edited Aug 31 '14 at 03:32

Andomar

232,371
49
380
404

answered Aug 31 '14 at 02:26

ydoow

2,969
4
24
40

1

I suggest changing `AFTER INSERT` for `AFTER INSERT, UPDATE`. It will work in the same way ([link](http://stackoverflow.com/questions/741414/insert-update-trigger-how-to-determine-if-insert-or-update/7430369#7430369)) and the answer will add more info. It can be tested with this [fiddle](http://sqlfiddle.com/#!6/3f974/1). – Zanon Aug 31 '14 at 03:31

score 0 · Answer 2 · answered Aug 31 '14 at 07:34

0

I think there is no easy of doing this at TABLE level. There are some workarounds to do this : 1. If you are using stored procs then you can write your logic over there. 2. Trigger is also an option but overhead in terms of execution.

Thanks.

answered Aug 31 '14 at 07:34

Karthik Kola

93
5

SQL Server - Default value

2 Answers2