UserDevices.sql

CREATE TABLE [<MobileServiceName>].[UserDevices] (
    [id]          NVARCHAR (255)     CONSTRAINT [DF_UserDevices_id] DEFAULT (CONVERT([nvarchar](255),newid(),(0))) NOT NULL,
    [__createdAt] DATETIMEOFFSET (3) CONSTRAINT [DF_UserDevices___createdAt] DEFAULT (CONVERT([datetimeoffset](3),sysutcdatetime(),(0))) NOT NULL,
    [__updatedAt] DATETIMEOFFSET (3) NULL,
    [__version]   ROWVERSION         NOT NULL,
    [userId]      NVARCHAR (450)     NULL,
    [deviceId]    NVARCHAR (MAX)     NULL,
    [channeluri]  NVARCHAR (MAX)     NULL,
    PRIMARY KEY NONCLUSTERED ([id] ASC)
);


GO
CREATE CLUSTERED INDEX [__createdAt]
    ON [<MobileServiceName>].[UserDevices]([__createdAt] ASC);


GO
CREATE NONCLUSTERED INDEX [userId]
    ON [<MobileServiceName>].[UserDevices]([userId] ASC);


GO
CREATE TRIGGER [<MobileServiceName>].[TR_UserDevices_InsertUpdateDelete] ON [<MobileServiceName>].[UserDevices]
		   AFTER INSERT, UPDATE, DELETE
		AS
		BEGIN
			SET NOCOUNT ON;
			IF TRIGGER_NESTLEVEL() > 3 RETURN;

			UPDATE [<MobileServiceName>].[UserDevices] SET [<MobileServiceName>].[UserDevices].[__updatedAt] = CONVERT (DATETIMEOFFSET(3), SYSUTCDATETIME())
			FROM INSERTED
			WHERE INSERTED.id = [<MobileServiceName>].[UserDevices].[id]
		END