CREATE TABLE [dbo].[backup_job]
(
[job_id] [binary] (16) NOT NULL,
[server_id] [binary] (16) NULL,
[type] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[name] [nvarchar] (76) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[create_date] [int] NULL,
[start_date] [int] NULL,
[end_date] [int] NULL,
[exit_code] [int] NULL,
[status] [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,
[user_id] [binary] (16) NULL,
[job_content] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[status_description] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[is_finished] [int] NULL,
[enable] [int] NULL,
[is_cycle] [int] NULL,
[job_thread_number] [int] NULL,
[job_number] [int] NULL,
[engine_sleep_time] [int] NULL,
[schedule_id] [varbinary] (16) NULL,
[creation_date_m_seconds] [int] NULL,
[service_isp_id] [binary] (16) NULL,
[service_admin_id] [binary] (16) NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO



/*
*********************************************
trigger for inserting backup_job entry
*/

CREATE trigger backup_r_insert_job
            on backup_job
            after insert
    as
    begin
        declare @_domain_uuid binary(16);
        select  @_domain_uuid =
            (select set_val_uuid from ca_settings where (set_id=1));

        declare @_manager_id binary(16);
        select    @_manager_id = (select manager_id from backup_manager where (domain_uuid = @_domain_uuid));

        execute backup_p_job_seq_number @_manager_id;
    end;


GO

/****** Object:  Trigger dbo.rule_del_job_check_active    Script Date: 12/1/2005 11:50:39 PM ******/

/*
*********************************************
trigger for deleting a backup_job
*/

create trigger rule_del_job_check_active
            on backup_job
            after delete
            as
            begin
        declare @_job_status integer;

        declare lst_job_stat cursor for
                   select status from deleted;

        -- get first inserted group ace
        open lst_job_stat;
        fetch from lst_job_stat into @_job_status;

        while @@fetch_status = 0
          begin
             execute backup_p_del_job @_job_status;
                    -- fetch next
               fetch from lst_job_stat into @_job_status;
   
        end;  -- end of loop over inserted grozp aces
         
        close lst_job_stat;
        deallocate lst_job_stat;
                
       end;


GO
ALTER TABLE [dbo].[backup_job] ADD CONSTRAINT [XPKbackup_job] PRIMARY KEY CLUSTERED ([job_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif3backup_job] ON [dbo].[backup_job] ([schedule_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif1backup_job] ON [dbo].[backup_job] ([server_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif5backup_job] ON [dbo].[backup_job] ([service_admin_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif4backup_job] ON [dbo].[backup_job] ([service_isp_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif2backup_job] ON [dbo].[backup_job] ([user_id]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[backup_job] TO [backup_admin_group]
GRANT INSERT ON  [dbo].[backup_job] TO [backup_admin_group]
GRANT DELETE ON  [dbo].[backup_job] TO [backup_admin_group]
GRANT UPDATE ON  [dbo].[backup_job] TO [backup_admin_group]
GRANT SELECT ON  [dbo].[backup_job] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[backup_job] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[backup_job] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[backup_job] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[backup_job] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[backup_job] TO [dms_backup_group]
GRANT INSERT ON  [dbo].[backup_job] TO [dms_backup_group]
GRANT DELETE ON  [dbo].[backup_job] TO [dms_backup_group]
GRANT UPDATE ON  [dbo].[backup_job] TO [dms_backup_group]
GO