CREATE TABLE [dbo].[backup_service_isp]
(
[service_isp_id] [binary] (16) NOT NULL,
[root_service_isp_id] [binary] (16) NULL,
[service_msp_id] [binary] (16) NULL,
[group_uuid] [binary] (16) NOT NULL,
[name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[notify_method] [int] NULL,
[group_quota] [int] NULL,
[member_quota] [int] NULL,
[used_quota] [int] NULL,
[last_notify_time] [int] NULL,
[max_registered_user] [int] NULL,
[registered_user] [int] NULL,
[trial_to_customer] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[duration_days] [int] NULL,
[start_date] [int] NULL,
[end_date] [int] NULL,
[status] [int] NULL,
[left_space_quota] [int] NULL,
[certificate] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[email_def_id] [binary] (16) NULL,
[contact_info] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[agent_package_url] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CS_AS 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
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO

/*
*********************************************
trigger for deleting backup_isp entry
If backup_service_isp is deleted, backup_service_msp.registered_companies must be decreased
*/

CREATE trigger [dbo].[backup_r_delete_isp_msp_reg_company]
            on [dbo].[backup_service_isp]
            after delete
    as
begin
        declare @_service_msp_id binary(16);
        select    @_service_msp_id = (select service_msp_id from deleted);

        if (@_service_msp_id is not null)
        begin        
            execute backup_p_delete_isp_msp_reg_company @_service_msp_id;
        end
    end;

GO


/*
*********************************************
trigger for inserting backup_service_isp entry
If backup_service_isp is created, backup_service_msp.registered_companies must be increased
*/

CREATE trigger [dbo].[backup_r_insert_isp_msp_reg_company]
            on [dbo].[backup_service_isp]
            after insert
    as
begin
        declare @_service_msp_id binary(16);
        select    @_service_msp_id = (select service_msp_id from inserted);

        if (@_service_msp_id is not null)
        begin
            execute backup_p_insert_isp_msp_reg_company @_service_msp_id;
        end
    end;

GO
ALTER TABLE [dbo].[backup_service_isp] ADD CONSTRAINT [XPKbackup_service_isp] PRIMARY KEY CLUSTERED ([service_isp_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif4backup_service_isp] ON [dbo].[backup_service_isp] ([email_def_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif3backup_service_isp] ON [dbo].[backup_service_isp] ([group_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif1backup_service_isp] ON [dbo].[backup_service_isp] ([root_service_isp_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif2backup_service_isp] ON [dbo].[backup_service_isp] ([service_msp_id]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[backup_service_isp] TO [backup_admin_group]
GRANT INSERT ON  [dbo].[backup_service_isp] TO [backup_admin_group]
GRANT DELETE ON  [dbo].[backup_service_isp] TO [backup_admin_group]
GRANT UPDATE ON  [dbo].[backup_service_isp] TO [backup_admin_group]
GRANT SELECT ON  [dbo].[backup_service_isp] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[backup_service_isp] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[backup_service_isp] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[backup_service_isp] TO [ca_itrm_group]
GO