How can I add a column in a table with a default value-CodePudding

I'm working with Azure Data Studio and trying to add a column to a table that is calculated by default. So far I've tried the following code:

alter table ETUDIANT add age int;
alter table ETUDIANT add constraint default_etudiant_age default(datediff(year, sysdatetime(), DateN));

alter table ETUDIANT add age int default(datediff(year, sysdatetime(), DateN));

didn't work.. anyone have perspective on what might be wrong?

CodePudding user response：

I think you actually want a computed column?

One where the database always calculates it for you, and you never supply the value yourself?

alter table ETUDIANT add age as (datediff(year, sysdatetime(), DateN)) persisted;

https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15

EDIT:

Or, perhaps a trigger?

CREATE TRIGGER trg_ETUDIANT_AfterInsert
  ON ETUDIANT 
AFTER INSERT
AS
  UPDATE ETUDIANT AS t
  SET t.age = t.datediff(year, sysdatetime(), DateN)
  FROM Inserted AS i
  WHERE t.PK = i.PK;
        -- Where PK is whatever unique key(s) exist on your table