TSQL How can I create a field that depends on another

You can define a computed column where you define the expression of your column. For example, to create a table with 3 fields, primary key, date and date_computed:

CREATE TABLE ejemplo (
    llave_primaria int not null,
    fecha datetime not null,
    fecha_computada as dateadd(year, 3, fecha)
)

In this case, the column fecha_computada is defined by the expression: dateadd(year, 3, fecha) , which indicates how the value of the column should be computed from the field fecha .

Optionally you can indicate that the result of the computed column is materialized so that it can be used in an index. Otherwise, the result of the expression is not saved and is calculated at the moment that must be projected in some result.

In SQL Server, as far as I know, the only way to do it is through a trigger, but as it would be a UPDATE to the same table you have to keep in mind how the solution would work, basically what will happen is:

• UPDATE/INSERT of our table
  • Execution of the trigger
    • New UPDATE of the field that we want to update automatically
      • New Execution of the trigger. Here if we do not control the nesting level we can enter a "loop", we can also verify if the new field was already updated

What to keep in mind?

1. The database engine must be configured to allow nested transactions.

If necessary, this could be done

ALTER DATABASE MiBase
   SET RECURSIVE_TRIGGERS ON;
GO

2. The most important thing is to control the nesting level of the trigger

So that there is no triggers loop, we will use the TRIGGER_NESTLEVEL() function and we will control that it does not exceed the 2 executions, since we saw that in normal operation, the trigger will be executed twice.

CREATE TABLE MYTABLE (
    Id      INT IDENTITY,
    Fecha       DATETIME,
    Fecha3Meses DATETIME
)

GO

CREATE TRIGGER U_mytable ON dbo.MYTABLE AFTER UPDATE
AS

    IF ((SELECT TRIGGER_NESTLEVEL()) < 2)
    BEGIN
        IF UPDATE(Fecha)
        BEGIN
            UPDATE MYTABLE
                SET Fecha3Meses = DATEADD(MONTH, 3, I.Fecha)
                FROM MYTABLE T
                INNER JOIN INSERTED I
                    ON I.ID = T.ID
        END
    END

GO

CREATE TRIGGER I_mytable ON dbo.MYTABLE AFTER INSERT
AS

    IF ((SELECT TRIGGER_NESTLEVEL()) < 2)
    BEGIN
        IF UPDATE(Fecha)
        BEGIN
            UPDATE MYTABLE
                SET Fecha3Meses = DATEADD(MONTH, 3, I.Fecha)
                FROM MYTABLE T
                INNER JOIN INSERTED I
                    ON I.ID = T.ID
        END
    END

GO

And to test the solution

INSERT INTO MYTABLE (Fecha)
    VALUES('20170101')

SELECT * FROM MYTABLE

UPDATE MYTABLE SET Fecha = '20170201'

SELECT * FROM MYTABLE