CREATE TABLE [dbo].[backup_user]
(
[user_id] [binary] (16) NOT NULL,
[user_uuid] [binary] (16) NULL,
[link_dis_hw_user_uuid] [binary] (16) NULL,
[server_id] [binary] (16) NOT NULL,
[archive_id] [varbinary] (16) NULL,
[backup_policy_id] [binary] (16) NOT NULL,
[agent_config_id] [binary] (16) NOT NULL,
[name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[password] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[protected] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[active] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[version] [nvarchar] (76) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[storage_area_id] [varbinary] (4) NULL,
[storage_area_size] [int] NULL,
[config_file_crc] [varbinary] (4) NULL,
[deployment] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[code_page] [int] NULL,
[expert_user_installation] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[password_for_restore] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[use_domain_account] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[use_pc_name] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[moved_date] [int] NULL,
[last_server_id] [binary] (16) NULL,
[last_login_date] [int] NULL,
[last_backup_client_date] [int] NULL,
[last_backup_server_date] [int] NULL,
[last_good_backup_server_date] [int] NULL,
[low_disk_space_errors] [int] NULL,
[low_disk_space_count_date] [int] NULL,
[unprocessed_cap_files] [int] NULL,
[unompressed_cap_file_count_date] [int] NULL,
[creation_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[creation_date] [int] NULL,
[last_update_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL,
[dis_hw_uuid] [binary] (16) NULL,
[network_address] [nvarchar] (76) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[exported_from_user_id] [varbinary] (4) NULL,
[label] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[use_eiam_account] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[quota] [int] NULL,
[use_quota] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[has_migrated_files] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[last_notify_time] [int] NULL,
[user_status] [int] NULL,
[service_isp_id] [binary] (16) NULL,
[staging_server_id] [binary] (16) NULL,
[email_address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[random_password] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO



/*
*********************************************
trigger for deleteing backup_user entry:
If backup_user is deleted, backup_service_isp.left_space_quota must
be changed.
*/

CREATE trigger [dbo].[backup_r_delete_user_isp_left_space]
            on [dbo].[backup_user]
            after delete
    as
begin
        declare @_service_isp_id binary(16);
        declare @_archive_id binary(16);
        declare @_storage_size integer;

        select @_service_isp_id = (select service_isp_id from deleted);
        select @_archive_id = (select archive_id from deleted)
        select @_storage_size = (select storage_area_size from deleted);
        
        if (@_storage_size > 0)
        begin
            execute [dbo].[backup_p_update_user_isp_left_space] @_service_isp_id, @_archive_id;
        end;
    end;

GO


/*
*********************************************
trigger for deleting backup_user entry:
If backup_user is deleted, backup_service_isp.registered_user must be updated
*/

CREATE trigger [dbo].[backup_r_delete_user_isp_reg_user]
            on [dbo].[backup_user]
            after delete
    as
begin
        declare @_service_isp_id binary(16);
        select    @_service_isp_id = (select service_isp_id from deleted);

        if (@_service_isp_id is not null)
        begin
            execute backup_p_delete_user_isp_reg_user @_service_isp_id;
        end;
    end;

GO

/*
*********************************************
trigger for inserting backup_user entry:
If backup_user is inserted, backup_service_isp.registered user must be increased
*/

CREATE trigger [dbo].[backup_r_insert_user_isp_reg_user]
            on [dbo].[backup_user]
            after insert
    as
    begin
        declare @_service_isp_id binary(16);
        select    @_service_isp_id = (select service_isp_id from inserted);

        if (@_service_isp_id is not null)
        begin
            execute backup_p_insert_user_isp_reg_user @_service_isp_id;
        end;
    end;

GO



/*
*********************************************
trigger for inserting or updateing backup_user entry:
If backup_user.storage_area_size is modified the following tables are updated, too:
    1. backup_service_isp.left_space_quota
    2. backup_archive.size
*/

CREATE  trigger [dbo].[backup_r_update_user_isp_left_space]
            on [dbo].[backup_user]
            after insert, update
    as
begin
        declare @_archive_id binary(16);
        declare @_service_isp_id binary(16);
        declare @_storage_size_old integer;
        declare @_storage_size_new integer;

        select @_service_isp_id = (select service_isp_id from inserted);
        select @_archive_id = (select archive_id from inserted)
        select @_storage_size_old = (select storage_area_size from deleted);
        select @_storage_size_new = (select storage_area_size from inserted);

        if (@_storage_size_new > 0) and
           (@_storage_size_new != @_storage_size_old)
        begin        
            execute [dbo].[backup_p_update_user_isp_left_space] @_service_isp_id, @_archive_id;
        end;
    end;


GO



/*
*********************************************
trigger for inserting or updateing backup_user entry:
If backup_user.storage_area_id is modified it must be checked, whether the value is unique
*/

CREATE  trigger [backup_r_update_user_storage_area_id]
            on [dbo].[backup_user]
            after insert, update
    as
begin
        declare @_storage_area_id_old binary(4);
        declare @_storage_area_id_new binary(4);
        declare @_count integer;

        select @_storage_area_id_old = (select storage_area_id from deleted);
        select @_storage_area_id_new = (select storage_area_id from inserted);

        if (@_storage_area_id_new is not null) and
           (@_storage_area_id_new != @_storage_area_id_old)
        begin
            select @_count = (select count(*) from backup_user
                where storage_area_id = @_storage_area_id_new);    
            if (@_count > 1)
            begin
                raiserror ('Error xxxx: Unique value for storage_area_id required',16,1);
            end;
        end;    
    end;


GO

/****** Object:  Trigger dbo.rule_reset_agent_backup_status    Script Date: 12/1/2005 11:51:50 PM ******/


/*
*********************************************
trigger for deleting backup_user
*/

create trigger rule_reset_agent_backup_status
        on backup_user        
                after delete
        as            
        begin


        declare @_user_uuid binary(16);
        declare @_dis_hw_uuid binary(16);
        declare @_link_dis_hw_user_uuid binary(16);

        declare lst_user cursor for
                   select user_uuid, dis_hw_uuid, link_dis_hw_user_uuid from deleted;

        -- get first deleted user
        open lst_user;
        fetch from lst_user into @_user_uuid, @_dis_hw_uuid, @_link_dis_hw_user_uuid;

        while @@fetch_status = 0
          begin
             execute backup_p_reset_agent_backup_stat @_user_uuid, @_dis_hw_uuid, @_link_dis_hw_user_uuid;
                    -- fetch next
               fetch from lst_user into @_user_uuid, @_dis_hw_uuid, @_link_dis_hw_user_uuid;

   
        end;  -- end of loop over inserted grozp aces
         
        close lst_user;
        deallocate lst_user;



    end;                                    


GO


/*
***********************************************************
Issue: 15458839    Title: ENG_BACKUP STATUS IS WRONG
***********************************************************
*/
CREATE  trigger [dbo].[rule_set_agent_backup_status]
    on [dbo].[backup_user]
    after insert, update
    
    as
    begin

        declare @_user_uuid binary(16),
            @_dis_hw_uuid binary(16),
            @_link_dis_hw_user_uuid binary(16);
           
        select @_user_uuid = (select user_uuid from inserted);
        select @_dis_hw_uuid = (select dis_hw_uuid from inserted);
        select @_link_dis_hw_user_uuid = (select link_dis_hw_user_uuid from inserted);

        declare @_last_login_date_new integer;
        select @_last_login_date_new = (select last_login_date from inserted);

        if    (@_last_login_date_new > 0)    
        begin
            execute backup_p_set_agent_backup_status @_user_uuid, @_dis_hw_uuid, @_link_dis_hw_user_uuid;
        end;
    end;


GO
ALTER TABLE [dbo].[backup_user] ADD CONSTRAINT [XPKbackup_user] PRIMARY KEY CLUSTERED ([user_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif10backup_user] ON [dbo].[backup_user] ([agent_config_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif8backup_user] ON [dbo].[backup_user] ([archive_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif9backup_user] ON [dbo].[backup_user] ([backup_policy_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif13backup_user] ON [dbo].[backup_user] ([dis_hw_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif6backup_user] ON [dbo].[backup_user] ([link_dis_hw_user_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif5backup_user] ON [dbo].[backup_user] ([server_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif14backup_user] ON [dbo].[backup_user] ([service_isp_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif15backup_user] ON [dbo].[backup_user] ([staging_server_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif11backup_user] ON [dbo].[backup_user] ([user_uuid]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[backup_user] TO [backup_admin_group]
GRANT INSERT ON  [dbo].[backup_user] TO [backup_admin_group]
GRANT DELETE ON  [dbo].[backup_user] TO [backup_admin_group]
GRANT UPDATE ON  [dbo].[backup_user] TO [backup_admin_group]
GRANT SELECT ON  [dbo].[backup_user] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[backup_user] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[backup_user] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[backup_user] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[backup_user] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[backup_user] TO [dms_backup_group]
GRANT INSERT ON  [dbo].[backup_user] TO [dms_backup_group]
GRANT DELETE ON  [dbo].[backup_user] TO [dms_backup_group]
GRANT UPDATE ON  [dbo].[backup_user] TO [dms_backup_group]
GO