/*
   Friday, March 02, 20184:29:28 PM
   User: 
   Server: UAT-DB01\EQUITY
   Database: db_edits
   Application: 
*/

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.m_user
	DROP CONSTRAINT FK_m_user_status
GO
ALTER TABLE dbo.m_status SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.m_user
	DROP CONSTRAINT DF_m_user_is_delete
GO
ALTER TABLE dbo.m_user
	DROP CONSTRAINT DF_m_user_add_audit
GO
ALTER TABLE dbo.m_user
	DROP CONSTRAINT DF_m_user_view_history_audit
GO
CREATE TABLE dbo.Tmp_m_user
	(
	user_id int NOT NULL IDENTITY (1, 1),
	username varchar(8) NOT NULL,
	user_fullname varchar(50) NOT NULL,
	email varchar(100) NULL,
	branch_id int NULL,
	status_id int NOT NULL,
	created_by varchar(8) NOT NULL,
	created_date datetime NOT NULL,
	modified_by varchar(8) NOT NULL,
	modified_date datetime NOT NULL,
	is_delete tinyint NOT NULL,
	add_audit tinyint NOT NULL,
	view_history_audit tinyint NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_m_user SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_m_user ADD CONSTRAINT
	DF_m_user_is_delete DEFAULT ((0)) FOR is_delete
GO
ALTER TABLE dbo.Tmp_m_user ADD CONSTRAINT
	DF_m_user_add_audit DEFAULT ((0)) FOR add_audit
GO
ALTER TABLE dbo.Tmp_m_user ADD CONSTRAINT
	DF_m_user_view_history_audit DEFAULT ((0)) FOR view_history_audit
GO
SET IDENTITY_INSERT dbo.Tmp_m_user ON
GO
IF EXISTS(SELECT * FROM dbo.m_user)
	 EXEC('INSERT INTO dbo.Tmp_m_user (user_id, username, user_fullname, branch_id, status_id, created_by, created_date, modified_by, modified_date, is_delete, add_audit, view_history_audit)
		SELECT user_id, username, user_fullname, branch_id, status_id, created_by, created_date, modified_by, modified_date, is_delete, add_audit, view_history_audit FROM dbo.m_user WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_m_user OFF
GO
ALTER TABLE dbo.t_transaction_document_file_history
	DROP CONSTRAINT FK_t_transaction_document_file_history_user
GO
ALTER TABLE dbo.m_user_transaction_category
	DROP CONSTRAINT FK_m_user_transaction_category_m_user
GO
DROP TABLE dbo.m_user
GO
EXECUTE sp_rename N'dbo.Tmp_m_user', N'm_user', 'OBJECT' 
GO
ALTER TABLE dbo.m_user ADD CONSTRAINT
	PK__m_user__B9BE370F09B45E9A PRIMARY KEY CLUSTERED 
	(
	user_id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.m_user ADD CONSTRAINT
	IX_m_user_unq UNIQUE NONCLUSTERED 
	(
	username
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX IX_m_user_user_fullname ON dbo.m_user
	(
	user_fullname
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_m_user_username_idx ON dbo.m_user
	(
	username
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.m_user ADD CONSTRAINT
	FK_m_user_status FOREIGN KEY
	(
	status_id
	) REFERENCES dbo.m_status
	(
	status_id
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
CREATE TRIGGER [dbo].[m_user_Insert] 
ON dbo.m_user    
FOR INSERT    
AS    

 INSERT INTO [dbo].[log_m_user]
([user_id],[username],[user_fullname],[branch_id],[status_id],[created_by],[created_date],[modified_by],[modified_date],[is_delete],AuditDataState,AuditDMLAction,AuditUser,AuditDateTime,UpdateColumns)
SELECT CONVERT(int,[user_id]) as [user_id],[username],[user_fullname],[branch_id],[status_id],[created_by],[created_date],[modified_by],[modified_date],[is_delete],'New','Insert',SUSER_SNAME(),getdate(),''  FROM INSERTED
GO
CREATE TRIGGER [dbo].[m_user_Update]      
ON dbo.m_user    
FOR UPDATE    
AS 
   
 
    DECLARE @UpdatedCols varchar(max) 
 
   SELECT @UpdatedCols =  CASE WHEN UPDATE([user_id]) THEN '[user_id]-' ELSE '' END + 
CASE WHEN UPDATE([username]) THEN '[username]-' ELSE '' END + 
CASE WHEN UPDATE([user_fullname]) THEN '[user_fullname]-' ELSE '' END + 
CASE WHEN UPDATE([branch_id]) THEN '[branch_id]-' ELSE '' END + 
CASE WHEN UPDATE([status_id]) THEN '[status_id]-' ELSE '' END + 
CASE WHEN UPDATE([created_by]) THEN '[created_by]-' ELSE '' END + 
CASE WHEN UPDATE([created_date]) THEN '[created_date]-' ELSE '' END + 
CASE WHEN UPDATE([modified_by]) THEN '[modified_by]-' ELSE '' END + 
CASE WHEN UPDATE([modified_date]) THEN '[modified_date]-' ELSE '' END + 
CASE WHEN UPDATE([is_delete]) THEN '[is_delete]-' ELSE '' END  
    
   IF LTRIM(RTRIM(@UpdatedCols)) <> '' 
   BEGIN 
          INSERT INTO [dbo].[log_m_user]
([user_id],[username],[user_fullname],[branch_id],[status_id],[created_by],[created_date],[modified_by],[modified_date],[is_delete],AuditDataState,AuditDMLAction,AuditUser,AuditDateTime,UpdateColumns)
SELECT CONVERT(int,[user_id]) as [user_id],[username],[user_fullname],[branch_id],[status_id],[created_by],[created_date],[modified_by],[modified_date],[is_delete],'New','Update',SUSER_SNAME(),getdate(),@UpdatedCols  FROM INSERTED     
    
          INSERT INTO [dbo].[log_m_user]
([user_id],[username],[user_fullname],[branch_id],[status_id],[created_by],[created_date],[modified_by],[modified_date],[is_delete],AuditDataState,AuditDMLAction,AuditUser,AuditDateTime,UpdateColumns)
SELECT CONVERT(int,[user_id]) as [user_id],[username],[user_fullname],[branch_id],[status_id],[created_by],[created_date],[modified_by],[modified_date],[is_delete],'Old','Update',SUSER_SNAME(),getdate(),@UpdatedCols  FROM DELETED  
   END
GO
CREATE TRIGGER [dbo].[m_user_Delete]    
ON dbo.m_user    
FOR DELETE    
AS   
  INSERT INTO [dbo].[log_m_user]
([user_id],[username],[user_fullname],[branch_id],[status_id],[created_by],[created_date],[modified_by],[modified_date],[is_delete],AuditDataState,AuditDMLAction,AuditUser,AuditDateTime,UpdateColumns)
SELECT CONVERT(int,[user_id]) as [user_id],[username],[user_fullname],[branch_id],[status_id],[created_by],[created_date],[modified_by],[modified_date],[is_delete],'Old','Delete',SUSER_SNAME(),getdate(),''  FROM DELETED
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.m_user_transaction_category ADD CONSTRAINT
	FK_m_user_transaction_category_m_user FOREIGN KEY
	(
	user_id
	) REFERENCES dbo.m_user
	(
	username
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.m_user_transaction_category SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.t_transaction_document_file_history ADD CONSTRAINT
	FK_t_transaction_document_file_history_user FOREIGN KEY
	(
	user_id
	) REFERENCES dbo.m_user
	(
	user_id
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO
ALTER TABLE dbo.t_transaction_document_file_history SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
