ALTER TABLE [dbo].[Client] ADD CONSTRAINT [DF_Client_CreationDate] DEFAULT (getutcdate()) FOR [CreationDate]

ALTER TABLE [dbo].[Client] ADD CONSTRAINT [DF_Client_LastEditDate] DEFAULT (getutcdate()) FOR [LastEditDate]

CREATE TABLE [dbo].[Client_Tombstone]

(

[ClientId] [uniqueidentifier] NOT NULL,

[DeletionDate] [datetime] NULL

) ON [PRIMARY]

CREATE TRIGGER [Client_DeletionTrigger]

    ON [Client]

    AFTER DELETE

AS

SET NOCOUNT ON

UPDATE [Client_Tombstone]

    SET [DeletionDate] = GETUTCDATE()

    FROM deleted

    WHERE deleted.[ClientId] = [Client_Tombstone].[ClientId]

IF @@ROWCOUNT = 0

BEGIN

    INSERT INTO [Client_Tombstone]

    ([ClientId], DeletionDate)

    SELECT [ClientId], GETUTCDATE()

    FROM deleted

END

CREATE TRIGGER [Client_UpdateTrigger]

    ON [Client]

    AFTER UPDATE

AS

BEGIN

    SET NOCOUNT ON

    UPDATE [Client]

    SET [LastEditDate] = GETUTCDATE()

    FROM inserted

    WHERE inserted.[ClientId] = [Client].[ClientId]

END;

CREATE TRIGGER [Client_InsertTrigger]

    ON [Client]

    AFTER INSERT

AS

BEGIN

    SET NOCOUNT ON

    UPDATE [Client]

    SET [CreationDate] = GETUTCDATE()

    FROM inserted

    WHERE inserted.[ClientId] = [Client].[ClientId]

END;