/****
This SQL script was generated by the Configure Data Synchronization
dialog box. This script contains statements that create the
change-tracking columns, deleted-items table, and triggers on the
server database. These database objects are required by Synchronization
Services to successfully synchronize data between client and server
databases. For more information, see the
‘How to: Configure a Database Server for Synchronization’ topic in Help.
****/


IF @@TRANCOUNT > 0
set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 

GO
BEGIN TRANSACTION;


IF @@TRANCOUNT > 0
ALTER TABLE [dbo].[userInfo] 
ADD [LastEditDate] DateTime NULL CONSTRAINT [DF_userInfo_LastEditDate] DEFAULT (GETUTCDATE()) WITH VALUES
GO
IF @@ERROR <> 0 
     ROLLBACK TRANSACTION;


IF @@TRANCOUNT > 0
ALTER TABLE [dbo].[userInfo] 
ADD [CreationDate] DateTime NULL CONSTRAINT [DF_userInfo_CreationDate] DEFAULT (GETUTCDATE()) WITH VALUES
GO
IF @@ERROR <> 0 
     ROLLBACK TRANSACTION;


IF @@TRANCOUNT > 0
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[userInfo_Tombstone]')) 
BEGIN 
CREATE TABLE [dbo].[userInfo_Tombstone]( 
    [UserName] NVarChar(50) NOT NULL,
    [DeletionDate] DateTime NULL
)END 

GO
IF @@ERROR <> 0 
     ROLLBACK TRANSACTION;


IF @@TRANCOUNT > 0
ALTER TABLE [dbo].[userInfo_Tombstone] ADD CONSTRAINT [PKDEL_userInfo_Tombstone_UserName]
   PRIMARY KEY CLUSTERED
    ([UserName])
GO
IF @@ERROR <> 0 
     ROLLBACK TRANSACTION;


IF @@TRANCOUNT > 0
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[userInfo_DeletionTrigger]') AND type = 'TR') 
   DROP TRIGGER [dbo].[userInfo_DeletionTrigger] 

GO
CREATE TRIGGER [dbo].[userInfo_DeletionTrigger] 
    ON [userInfo] 
    AFTER DELETE 
AS 
SET NOCOUNT ON 
UPDATE [dbo].[userInfo_Tombstone] 
    SET [DeletionDate] = GETUTCDATE() 
    FROM deleted 
    WHERE deleted.[UserName] = [userInfo_Tombstone].[UserName] 
IF @@ROWCOUNT = 0 
BEGIN 
    INSERT INTO [dbo].[userInfo_Tombstone] 
    ([UserName], DeletionDate)
    SELECT [UserName], GETUTCDATE()
    FROM deleted 
END 

GO
IF @@ERROR <> 0 
     ROLLBACK TRANSACTION;


IF @@TRANCOUNT > 0
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[userInfo_UpdateTrigger]') AND type = 'TR') 
   DROP TRIGGER [dbo].[userInfo_UpdateTrigger] 

GO
CREATE TRIGGER [dbo].[userInfo_UpdateTrigger] 
    ON [dbo].[userInfo] 
    AFTER UPDATE 
AS 
BEGIN 
    SET NOCOUNT ON 
    UPDATE [dbo].[userInfo] 
    SET [LastEditDate] = GETUTCDATE() 
    FROM inserted 
    WHERE inserted.[UserName] = [userInfo].[UserName] 
END;
GO
IF @@ERROR <> 0 
     ROLLBACK TRANSACTION;


IF @@TRANCOUNT > 0
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[userInfo_InsertTrigger]') AND type = 'TR') 
   DROP TRIGGER [dbo].[userInfo_InsertTrigger] 

GO
CREATE TRIGGER [dbo].[userInfo_InsertTrigger] 
    ON [dbo].[userInfo] 
    AFTER INSERT 
AS 
BEGIN 
    SET NOCOUNT ON 
    UPDATE [dbo].[userInfo] 
    SET [CreationDate] = GETUTCDATE() 
    FROM inserted 
    WHERE inserted.[UserName] = [userInfo].[UserName] 
END;
GO
IF @@ERROR <> 0 
     ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
