Using scope_identity() to get ID "during" insertion of row?

I have a column that has an ident_current constraint on it to populate another column, like so:

alter table dbo.Employee 
add constraint dbo_Employee_D1 default ident_current('dbo.Employee') for SourceID`

When using ident_current, I have a risk of not always getting the correct identity value in case multiple threads execute my SQL. I wanted to use scope_identity() instead. But how do I use it? I cannot simply replace ident_current with scope_identity().

alter table dbo.Employee 
add constraint dbo_Employee_D1 default scope_identity() for SourceID`

An option is to use an AFTER INSERT trigger. But won't this have the same issue in multi-threaded environment?

Do exactly as Aaron said, or if you are afraid of nulls, or don't want to use COALESCE, perhaps you could do something along these lines:

USE tempdb;
GO
-- Create the table
CREATE TABLE dbo.T
(
    IdentityID INT NOT NULL CONSTRAINT PK_T PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , CopyOfIdentityID INT NULL 
);
GO
-- create a trigger to copy the values from column a to column b
CREATE TRIGGER T_Trigger ON T
AFTER INSERT
AS 
    UPDATE dbo.T SET CopyOfIdentityID = i.IdentityID FROM dbo.T INNER JOIN inserted i ON t.IdentityID = i.IdentityID;   
GO
-- insert some test values (this would typically be some other piece of code, perhaps
-- a stored proc or something.
INSERT INTO dbo.T DEFAULT VALUES;
-- you could return the SCOPE_IDENTITY() here for use in the update below.
SELECT SCOPE_IDENTITY();
-- show the row prior to changing the b column to some other value
SELECT *
FROM dbo.T;
-- update the b column to some other value.
DECLARE @SomeOtherID INT;
SET @SomeOtherID = 2;
UPDATE dbo.T SET CopyOfIdentityID = @SomeOtherID WHERE T.IdentityID = 1;
-- show the row with the updated value.
SELECT *
FROM dbo.T;

Results:

Simple, and doesn't rely on anything fancy.

StackExchange Database Administrators Q#69118, answer score: 2