Hibernate, SQL Server: how can I use the default value as the value from another column

Question

For example: I have a column COLUMN_PK of type int identity (1,1) and a column of COLUMN_NUM pf type int, how can I determine the default value for COLUMN_NUM - the value of COLUMN_PK?

So, if I have an object with COLUMN_NUM not specified, it will be populated with the generated value from COLUMN_PK. But if COLUMN_NUM is specified, its value will be used.

Answer 1

Create the 3rd column that is calculated

CREATE TABLE MyTable ( COLUMN_PK int NOT NULL identity(1,1) , ... COLUMN_NUM_internal int NULL, COLUMN_NUM AS COALESCE (COLUMN_NUM_internal, COLUMN_PK), ... ) 

The PK value is unknown before INSERT (of course). But not before, so you do something like this or use a trigger to update COLUMN_NUM. However, this solution above works for subsequent UPDATEs also without additional code (i.e., another trigger for UPDATE)

Answer 2

Use computed column and scalar function as default value.

Answer 3

I would accomplish this with a trigger.

 create trigger tr_IU_YourTable on YourTable for insert,update as begin update yt set column_num = yt.column_pk from inserted i inner join YourTable yt on i.column_pk = yt.column_pk and yt.column_num is null end go 

Answer 4

How about having a child table to store only COLUMN_PK and COLUMN_NUM? This table will only have an entry if COLUMN_NUM is specified. Then you can do:

 CREATE TABLE Parent (COLUMN_PK int NOT NULL identity(1,1), someCol int NOT NULL) CREATE TABLE Child (COLUMN_PK int NOT NULL, COLUMN_NUM int NOT NULL) INSERT INTO Parent (someCol) VALUES (1) INSERT INTO Parent (someCol) VALUES (2) INSERT INTO Parent (someCol) VALUES (3) INSERT INTO Parent (someCol) VALUES (4) INSERT INTO Parent (someCol) VALUES (5) INSERT INTO Child VALUES (1, 10) INSERT INTO Child VALUES (3, 30) INSERT INTO Child VALUES (5, 50) SELECT COLUMN_PK, CASE WHEN EXISTS (SELECT NULL FROM Child WHERE Child.COLUMN_PK = Parent.COLUMN_PK) THEN (SELECT COLUMN_NUM FROM Child WHERE Child.COLUMN_PK = Parent.COLUMN_PK) ELSE COLUMN_PK END FROM Parent 

Answer 5

Using NHibernate to call a stored procedure to insert data instead of using direct insertion through the .save method can give you more control.