CODEWITHSUNDEEP
sqlsql-serverhibernatehibernate-annotations
Taras
Taras

Reputation: 73

Hibernate, SQL Server: How could I use default value as value from another column

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

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

Upvotes: 4

Views: 977

Answers (5)

barrypicker
barrypicker

Reputation: 10088

Using NHibernate to call a stored procedure to insert data, rather than use a direct insert via a .save method could give you more control.

Upvotes: 0

Raj Kashyap
Raj Kashyap

Reputation: 81

How about having a child table to store just COLUMN_PK and COLUMN_NUM? This table will have a record only if COLUMN_NUM value 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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

I would enforce this through 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

Upvotes: 0

gbn
gbn

Reputation: 432190

Create a 3rd column that is computed

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 not known until INSERT ( of course). But not before so you have do something like this or use a trigger to update COLUMN_NUM. However, this solution above works for subsequent UPDATEs too with no extra code (i.e. another trigger for UPDATE)

Upvotes: 1

Mihir
Mihir

Reputation: 66

Use computed column and a scalar function as default value.

Upvotes: 0

Related Questions