CODEWITHSUNDEEP
sqlsql-serverdefault-value
Vitor Barreto
Vitor Barreto

Reputation: 177

SQL Server - Default value

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?

Upvotes: 1

Views: 188

Answers (2)

Karthik Kola
Karthik Kola

Reputation: 93

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.

Upvotes: 0

ydoow
ydoow

Reputation: 2996

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

Upvotes: 1

Related Questions