Conditionally use computed column IS it possible that a not nullable column can be setup to either
a) add value freely with INSERT/UPDATE query
b) if value not provided on insert, to avoid null entry added default the value to that of another column?
I know that with a computed column I can set the default to be another columns value, but I cannot then override this.
An example:


CREATE TABLE test (id INT,
                   val INT);
GO




CREATE TRIGGER tr
ON test
AFTER INSERT, UPDATE
AS
UPDATE test
SET val = 123     -- custom value which must be set instead of NULL
                  -- maybe any expression 
                  -- and even an output of the subquery
WHERE EXISTS ( SELECT NULL
               FROM INSERTED
               WHERE id = test.id
                 AND val IS NULL );
GO




INSERT INTO test VALUES (1,1), (2,null), (3,3);
SELECT * FROM test;
GO


id | val
-: | --:
 1 |   1
 2 | 123      -- NULL is replaced with custom value
 3 |   3




UPDATE test SET val = NULL where id = 1;
SELECT * FROM test;
GO


id | val
-: | --:
 1 | 123      -- NULL is replaced with custom value
 2 | 123
 3 |   3


db&lt;&gt;fiddle here