CREATE TABLE [dbo].[ca_agent]
(
[last_run_date] [int] NULL,
[object_uuid] [binary] (16) NOT NULL,
[agent_version] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[prev_manager] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[server_uuid] [binary] (16) NULL,
[agent_area] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[agent_file_id] [nvarchar] (36) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[agent_type] [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 CONSTRAINT [DF__ca_agent__versio__40257DE4] DEFAULT ((0)),
[class_id] [int] NULL,
[domain_id] [int] NULL,
[unit_id] [int] NULL,
[agent_name] [nvarchar] (192) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[boot_server_uuid] [binary] (16) NULL,
[registration_type] [int] NULL,
[auto_server_version] [int] NULL CONSTRAINT [DF__ca_agent__auto_s__4119A21D] DEFAULT ((0)),
[auto_rep_version] [timestamp] NULL,
[agent_ref_count] [int] NULL,
[agent_lock] [int] NULL,
[derived_status_am] [int] NULL CONSTRAINT [DF__ca_agent__derive__420DC656] DEFAULT ((0)),
[derived_status_sd] [int] NULL CONSTRAINT [DF__ca_agent__derive__4301EA8F] DEFAULT ((0)),
[derived_status_rc] [int] NULL CONSTRAINT [DF__ca_agent__derive__43F60EC8] DEFAULT ((0)),
[derived_status_babld] [int] NULL CONSTRAINT [DF__ca_agent__derive__44EA3301] DEFAULT ((0)),
[ip_address] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[proc_os_id] [int] NULL,
[user_def1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_def2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_def3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[user_def4] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[trustlevel] [int] NULL CONSTRAINT [ca_agent_tl] DEFAULT ((5)),
[origin] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[derived_status_pt] [int] NULL CONSTRAINT [DF__ca_agent__derive__02A2DC18] DEFAULT ((0)),
[derived_status_pt_as] [int] NULL CONSTRAINT [DF__ca_agent__derive__03970051] DEFAULT ((0)),
[derived_status_pt_av] [int] NULL CONSTRAINT [DF__ca_agent__derive__048B248A] DEFAULT ((0)),
[derived_status_pt_ip] [int] NULL CONSTRAINT [DF__ca_agent__derive__057F48C3] DEFAULT ((0)),
[tenant_id] [binary] (16) NULL
) ON [PRIMARY]
GO
CREATE trigger   r_am_proc_delete_unit
     on ca_agent
     instead of delete
as
begin
    declare @object_uuid binary(16) ;
    declare @agent_type integer;
    declare @unit_id integer;
    declare @domain_id integer;
        
    DECLARE r_am_proc_delete_unit_cur CURSOR
        FOR SELECT object_uuid, agent_type, unit_id, domain_id
            FROM deleted
      
    OPEN r_am_proc_delete_unit_cur
    FETCH NEXT FROM r_am_proc_delete_unit_cur INTO @object_uuid, @agent_type, @unit_id, @domain_id
    WHILE @@FETCH_STATUS = 0
    BEGIN

        execute ca_am_update_agent_derived @object_uuid
        execute am_proc_delete_unit @object_uuid, @agent_type, @unit_id, @domain_id
        delete from ca_agent where object_uuid=@object_uuid
        FETCH NEXT FROM r_am_proc_delete_unit_cur INTO @object_uuid, @agent_type, @unit_id, @domain_id
    END
    CLOSE r_am_proc_delete_unit_cur
    DEALLOCATE r_am_proc_delete_unit_cur
    
    
end;
GO
/****** Object:  Trigger dbo.r_del_agent    Script Date: 12/1/2005 11:51:19 PM ******/


/*
***********************************************
trigger on delete on ca_agent
*/
CREATE trigger r_del_agent
     on ca_agent
    after delete
as
begin
    declare @old_object_uuid as binary(16);
    declare cur_cl cursor for
        select object_uuid from deleted

    open cur_cl;
    fetch cur_cl into @old_object_uuid; -- get first
    while @@fetch_status = 0
    begin
    
        execute  p_integrity_d_agent @old_object_uuid ;
        fetch cur_cl into @old_object_uuid;

     end; -- while
     close cur_cl;
     deallocate cur_cl;

end
GO
/****** Object:  Trigger dbo.r_u_ca_agent    Script Date: 12/1/2005 11:51:24 PM ******/


/*
**********************************************************
    trigger on after update (description)
    on ca_agent to track all changes in the
    ca_agent about the description
**********************************************************
*/
CREATE trigger r_u_ca_agent
    on ca_agent
    after update
as
    if update(description)
begin    
    declare @object_uuid binary(16);
    declare @description nvarchar(255);
    declare @domain_id integer;

    declare cursor_ca_agent cursor local for
        select object_uuid, description, domain_id from inserted

    open cursor_ca_agent;
    fetch next from cursor_ca_agent into @object_uuid, @description, @domain_id; -- get first
    while @@fetch_status = 0
    begin
    
        execute p_urc_ab_ca_agent_updated @object_uuid, @description, @domain_id;
        fetch next from cursor_ca_agent into @object_uuid, @description, @domain_id; -- get first

    end; -- while
    close cursor_ca_agent;
    deallocate cursor_ca_agent;

end
GO
/****** Object:  Trigger dbo.r_upd_verno_agent    Script Date: 12/1/2005 11:51:27 PM ******/





/*
***********************************************
trigger on update on ca_agent
*/

CREATE trigger r_upd_verno_agent
        on ca_agent
        after update        
        as
                declare @old_verno as int;
                declare @new_verno as int;
        begin
            if update (version_number)
            begin

                DECLARE mycur CURSOR
                FOR select deleted.version_number, inserted.version_number from inserted, deleted

                OPEN mycur
                FETCH NEXT FROM mycur INTO @old_verno ,  @new_verno ;
                WHILE @@FETCH_STATUS = 0
                BEGIN

                    execute  p_integrity_version_number @old_verno ,  @new_verno ;
                    FETCH NEXT FROM mycur INTO @old_verno ,  @new_verno ;

                END

                CLOSE mycur
                DEALLOCATE mycur

                
            end;
        end;
GO
SET QUOTED_IDENTIFIER OFF
GO

/****** Object:  Trigger dbo.rule_del_agent_check_busr    Script Date: 12/1/2005 11:51:37 PM ******/

/*
*********************************************
trigger for deleting a ca_agent
*/

create trigger rule_del_agent_check_busr
            on ca_agent
            after delete
            as
            begin
        declare @_object_uuid binary(16);

        declare lst_obj_uuid cursor for
                   select object_uuid from deleted;

        -- get first inserted group ace
        open lst_obj_uuid;
        fetch from lst_obj_uuid into @_object_uuid;

        while @@fetch_status = 0
          begin
             execute backup_p_del_agent @_object_uuid;
                    -- fetch next
               fetch from lst_obj_uuid into @_object_uuid;
   
        end;  -- end of loop over inserted grozp aces
         
        close lst_obj_uuid;
        deallocate lst_obj_uuid;
                
       end;


GO
SET QUOTED_IDENTIFIER ON
GO
create trigger t_ca_agent_status_rc_insert
    on ca_agent
    after insert
as
begin
    declare @l_func                nvarchar(40)
    declare @l_modified_uuid    binary(16)

    set @l_func = 't_ca_agent_status_rc_insert'
    /* exec urc_trace_text @l_func, 'trigger called' */
        
    set @l_modified_uuid = (select object_uuid from INSERTED);

    /* exec urc_trace_uuid @l_func, 'modified object_uuid', @l_modified_uuid */
        
    /* exec urc_trace_text @l_func, 'calling procedure p_ca_rc_agent_statsus_update' */

    exec p_ca_rc_agent_status_update @l_modified_uuid

    /* exec urc_trace_text @l_func, 'trigger ends' */
end;
GO
create trigger t_ca_agent_status_rc_update
    on ca_agent
    after update
as
    if update ( last_run_date )
    begin
        declare @l_func                nvarchar(40)
        declare @l_modified_uuid    binary(16)

        set @l_func = 't_ca_agent_status_rc_update'
        /*exec urc_trace_text @l_func, 'trigger called' */
        
        set @l_modified_uuid = (select object_uuid from INSERTED);

        /* exec urc_trace_uuid @l_func, 'modified object_uuid', @l_modified_uuid */
        
        /* exec urc_trace_text @l_func, 'calling procedure p_ca_rc_agent_statsus_update' */

        exec p_ca_rc_agent_status_update @l_modified_uuid

        /* exec urc_trace_text @l_func, 'trigger ends' */
    end;
GO
CREATE TRIGGER t_u_ca_agent ON dbo.ca_agent
FOR UPDATE
AS

/*  Only run if last_run_date has been updated. */

if update (last_run_date)
  begin
  
      update ca_discovered_hardware
         set discovery_changes_switch = 1
       where dis_hw_uuid in (select object_uuid from inserted)
         and discovery_changes_switch <> 1

  end
GO
create trigger uam_trg_ca_agent_insert
     on ca_agent
     after insert
as
begin
    declare @object_uuid binary(16) ;
    declare @agent_type integer;
    
    DECLARE am_cur CURSOR
        FOR SELECT object_uuid,agent_type
            FROM inserted
            
    OPEN am_cur
    FETCH NEXT FROM am_cur INTO @object_uuid,@agent_type
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF(@agent_type=1 OR @agent_type=2)
    BEGIN
            EXECUTE ca_am_update_agent_derived @object_uuid
    END
        FETCH NEXT FROM am_cur INTO @object_uuid,@agent_type
    END
    CLOSE am_cur
    DEALLOCATE am_cur
end
GO
create trigger uam_trg_ca_agent_update
    on ca_agent
    after update
as
    if UPDATE(last_run_date)
begin
    declare @object_uuid binary(16) ;
    declare @agent_type integer;
    
    DECLARE am_cur CURSOR
        FOR SELECT object_uuid,agent_type
            FROM deleted
            
    OPEN am_cur
    FETCH NEXT FROM am_cur INTO @object_uuid,@agent_type
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF(@agent_type=1 OR @agent_type=2)
    BEGIN
            execute ca_am_update_agent_derived @object_uuid
    END
        FETCH NEXT FROM am_cur INTO @object_uuid,@agent_type
    END
    CLOSE am_cur
    DEALLOCATE am_cur
end
GO
/* End of lines added to convert to online lock */
CREATE  trigger usd_trg_d_agent_usd_rel
on ca_agent
for delete as
declare
    @counted int
begin
    -- Only work with usd agents
    set @counted = (select count(*) from deleted where agent_type = 1 or agent_type = 4)
    if(@counted = 0)
    begin
    -- No USD agents exit
    return
    end

    -- If we are involved in an active job, raise error
    set @counted = (select count(*)
    from usd_v_nr_of_active_applics aa, deleted d
    where aa.target = d.object_uuid
    and activity <> 0x00000000000000000000000000000000)
    if(@counted > 0)
    begin
    raiserror('Error 9011: Cannot remove the computer because of active Software Delivery jobs', 16, 1 )
        rollback transaction
    return
    end

    -- Get all the activities related to this computer
            
    -- Store some of the data of the activities involved in the deleted computer(s)
    -- We must store away this as we need to delete applics early on.
    -- If we delete the applics first then we would not find the relevant activities
    select act.objectid, act.actproc into #activity
    from usd_applic a, usd_activity act, deleted d
    where a.activity = act.objectid
    and a.target = d.object_uuid

    set @counted = (select count(*) from #activity)
    if(@counted = 0)
    begin
    -- Not involved in any job only cleanup some objects
        delete from usd_link_grp_cmp from deleted where comp = object_uuid
        delete from usd_applic from deleted where target = object_uuid
        delete from usd_target from deleted where objectid = object_uuid

    update usd_class_version set modify_version = modify_version + 1
    where name = 'link_grp_cmp'
    or name = 'usd_applic'
    or name = 'target'
    return
    end
    
    -- Cleanup 1
    -- Delete the following: usd_link_grp_cmp,usd_target
    -- usd_link_act_cmp,usd_link_jc_srv,usd_link_act_inst
    delete from usd_link_grp_cmp from deleted where comp = object_uuid
    delete from usd_link_act_cmp from deleted where comp = object_uuid
    delete from usd_link_jc_srv from deleted where server = object_uuid
    delete from usd_link_act_inst
    from deleted d inner join usd_applic a on d.object_uuid = a.target
    where usd_link_act_inst.installation = a.objectid
    delete from usd_target from deleted where objectid = object_uuid

    -- Update the class version
    update usd_class_version set modify_version = modify_version + 1
    where name = 'link_grp_cmp'
    or name = 'link_act_cmp'
    or name = 'link_jc_srv'
    or name = 'link_act_inst'
    or name = 'target'
    
   
    -- We must now remove all the applics of these computer as we need to recalculate counters
    -- Cleanup 2
    delete from usd_applic
    from deleted
    where target = object_uuid

    -- Now store the activities from #activity that only targeted the applics deleted above
    -- We only want to keep activites that involve other computers than the ones we deleted.
    -- For these kept activities we need to update counters and masks
    select objectid into #activity_deleted
    from #activity
    where objectid not in (select appl.activity
    from usd_applic appl, #activity act
    where appl.activity = act.objectid)

    -- Remove the unwanted activities        
    delete from #activity
    from #activity_deleted ad
    where #activity.objectid = ad.objectid

    -- Update the class version
    update usd_class_version set modify_version = modify_version + 1 where name = 'applic'

    -- Create a temp table to hold the new counters
    create table #actcount(oid binary(16), totcnt int default 0, waitcnt int default 0, actcnt int default 0, okcnt int default 0, errcnt int default 0,
                      totrencnt int default 0, waitrencnt int default 0, actrencnt int default 0, okrencnt int default 0, errrencnt int default 0)

    -- Be careful understanding how the counterid is used, this table is used for many things
    -- and you must not mix up objects taken from this table.
    create table #interm_count(oid binary(16), cnt int, counterid int)

    -- Insert the tot counters, use the temp table above
    insert into #actcount(oid, totcnt)
    select act.objectid, count(*)
    from usd_applic app, #activity act
    where app.activity = act.objectid
    group by act.objectid

    -- Update the renew tot counters
    insert into #interm_count(oid, cnt, counterid)
    select act.objectid, count(*), 1
    from usd_applic appv, #activity act
    where appv.activity = act.objectid
    and renewstatus <> 0
    group by act.objectid

    update #actcount set totrencnt = cnt
    from #interm_count
    where #actcount.oid = #interm_count.oid and #interm_count.counterid = 1

    -- Update the wait counters, use the temp table above and also store
    -- temp data in a new temp table
    insert into #interm_count(oid, cnt, counterid)
    select act.objectid, count(*) cnt, 2
    from usd_v_nr_of_waiting_applics appv, #activity act
    where appv.activity = act.objectid
    group by act.objectid
    
    update #actcount set waitcnt = cnt
    from #interm_count
    where #actcount.oid = #interm_count.oid and #interm_count.counterid = 2

    -- Update the renew wait counters
    insert into #interm_count(oid, cnt, counterid)
    select act.objectid, count(*) cnt, 3
    from usd_v_nr_of_renew_wait_applics appv, #activity act
    where appv.activity = act.objectid
    group by act.objectid

    update #actcount set waitrencnt = cnt
    from #interm_count
    where #actcount.oid = #interm_count.oid and #interm_count.counterid = 3

    -- Update the active counters, use the temp table above and also store
    -- temp data in a new temp table
    insert into #interm_count(oid, cnt, counterid)
    select act.objectid, count(*) cnt, 4
    from usd_v_nr_of_active_applics appv, #activity act
    where appv.activity = act.objectid
    group by act.objectid
    
    update #actcount set actcnt = cnt
    from #interm_count
    where #actcount.oid = #interm_count.oid and #interm_count.counterid = 4

    -- Update the renew active counters
    insert into #interm_count(oid, cnt, counterid)
    select act.objectid, count(*) cnt, 5
    from usd_v_nr_of_renew_active_applics appv, #activity act
    where appv.activity = act.objectid
    group by act.objectid

    update #actcount set actrencnt = cnt
    from #interm_count
    where #actcount.oid = #interm_count.oid and #interm_count.counterid = 5

    -- Update the ok counters, use the temp table above and also store
    -- temp data in a new temp table
    insert into #interm_count(oid, cnt, counterid)
    select act.objectid, count(*) cnt, 6
    from usd_v_nr_of_ok_applics appv, #activity act
    where appv.activity = act.objectid
    group by act.objectid
    
    update #actcount set okcnt = cnt
    from #interm_count
    where #actcount.oid = #interm_count.oid and #interm_count.counterid = 6

    -- Update the renew ok counters
    insert into #interm_count(oid, cnt, counterid)
    select act.objectid, count(*) cnt, 7
    from usd_v_nr_of_renew_ok_applics appv, #activity act
    where appv.activity = act.objectid
    group by act.objectid

    update #actcount set okrencnt = cnt
    from #interm_count
    where #actcount.oid = #interm_count.oid and #interm_count.counterid = 7

    -- Update the error counters, use the temp table above
    update #actcount set errcnt = totcnt - waitcnt - actcnt - okcnt
    
    -- Update the renew error counters, use the temp table above

    update #actcount set errrencnt = totrencnt - waitrencnt - actrencnt - okrencnt

    -- Go and do the big update to the real activity table
    update usd_activity set
    waitingcnt = waitcnt,
    activecnt = actcnt,
    okcnt = #actcount.okcnt,
    errorcnt = errcnt,
    waitingrenewcnt = waitrencnt,
    activerenewcnt = actrencnt,
    okrenewcnt = okrencnt,
    errorrenewcnt = errrencnt
    from #actcount
    where objectid = oid

    -- set the new states
    update usd_activity set state = 1 -- INPROGRESS
    from #actcount
    where objectid = oid
    and #actcount.actcnt > 0
    
    update usd_activity set state = 0 -- WAITING
    from #actcount
    where objectid = oid
    and #actcount.actcnt = 0
    and #actcount.waitcnt > 0
    
    update usd_activity set state = 2 -- ACTOK
    from #actcount
    where objectid = oid
    and #actcount.actcnt = 0
    and #actcount.waitcnt = 0
    and #actcount.totcnt = #actcount.okcnt
    and state <> 7
    
    update usd_activity set state = 3 -- ACTERROR
    from #actcount
    where objectid = oid
    and #actcount.actcnt = 0
    and #actcount.waitcnt = 0
    and #actcount.totcnt <> #actcount.okcnt
    and state <> 7

    -- set the new renew states
    update usd_activity set renewstate = 1 -- INPROGRESS
    from #actcount
    where objectid = oid
    and #actcount.actrencnt > 0
    and renewstate <> 8

    update usd_activity set renewstate = 0 -- WAITING
    from #actcount
    where objectid = oid
    and #actcount.actrencnt = 0
    and #actcount.waitrencnt > 0
    and renewstate <> 8

    update usd_activity set renewstate = 8 -- NOT_YET_RENEWED
    from #actcount
    where objectid = oid
    and #actcount.actrencnt = 0
    and #actcount.waitrencnt = 0
    and #actcount.okrencnt+#actcount.errrencnt+#actcount.actrencnt+#actcount.waitrencnt = 0
    and renewstate <> 8

    update usd_activity set renewstate = 3 --  ACTERROR
    from #actcount
    where objectid = oid
    and #actcount.actrencnt = 0
    and #actcount.waitrencnt = 0
    and #actcount.okrencnt+#actcount.errrencnt+#actcount.actrencnt+#actcount.waitrencnt <> 0
    and #actcount.errrencnt > 0
    and renewstate <> 8

    update usd_activity set renewstate = 2 --  ACTOK
    from #actcount
    where objectid = oid
    and #actcount.actrencnt = 0
    and #actcount.waitrencnt = 0
    and #actcount.okrencnt+#actcount.errrencnt+#actcount.actrencnt+#actcount.waitrencnt <> 0
    and #actcount.errrencnt = 0
    and #actcount.actcnt = 0
    and #actcount.waitcnt = 0
    and #actcount.totrencnt = #actcount.errcnt
    and renewstate <> 8

    update usd_activity set renewstate = 50 --  ACT_PARTIALLY_RENEWED_OK
    from #actcount
    where objectid = oid
    and #actcount.actrencnt = 0
    and #actcount.waitrencnt = 0
    and #actcount.okrencnt+#actcount.errrencnt+#actcount.actrencnt+#actcount.waitrencnt <> 0
    and #actcount.errrencnt = 0
    and #actcount.actcnt = 0
    and #actcount.waitcnt = 0
    and #actcount.totrencnt <> #actcount.errcnt
    and renewstate <> 8

    update usd_activity set renewstate = 50 --  ACT_PARTIALLY_RENEWED_OK
    from #actcount
    where objectid = oid
    and #actcount.actrencnt = 0
    and #actcount.waitrencnt = 0
    and #actcount.okrencnt+#actcount.errrencnt+#actcount.actrencnt+#actcount.waitrencnt <> 0
    and #actcount.errrencnt = 0
    and (#actcount.actcnt <> 0 or #actcount.waitcnt <> 0)
    and renewstate <> 8

    -- now, this can cause the permission mask to change...
    
    -- Set bit SDAPI_JOB_CANCEL_ALLOWED
    update usd_activity set permmask = permmask | 2
    from #activity b
    where usd_activity.objectid = b.objectid
    and state in (0,2,3) -- ACTOK or ACTERROR

    -- Clear bit SDAPI_JOB_CANCEL_ALLOWED
    update usd_activity set permmask = permmask & ~2
    from #activity b
    where usd_activity.objectid = b.objectid
    and state not in (0,2,3) -- WAITING, ACTOK or ACTERROR

    -- Set bit SDAPI_JOB_RESCHEDULE_ALLOWED if activity waiting and no applics exists
    update usd_activity set usd_activity.permmask = usd_activity.permmask | 8
    from #activity b, usd_applic appl
    where usd_activity.objectid = b.objectid
    and usd_activity.state = 0 -- WAITING
    and appl.activity = b.objectid

    -- Clear bit SDAPI_JOB_RESCHEDULE_ALLOWED if activity not waiting
    update usd_activity set permmask = permmask & ~8
    from #activity b
    where usd_activity.objectid = b.objectid
    and usd_activity.state <> 0 -- WAITING

    -- Figure out the recover bit
    
    -- Count the recovery procs for each activity
    
    -- Get the rsw for each activity
    select act.objectid as actoid, rsw.objectid as rswoid into #actrsw
    from #activity act,usd_actproc ap, usd_rsw rsw
    where act.actproc = ap.objectid
    and rsw.objectid = ap.rsw
    
    -- Get the uninst procs for each rsw
    insert into #interm_count(oid, cnt, counterid)
    select rsw.rswoid oid, count(ap.objectid) cnt, 9
    from usd_actproc ap, #actrsw rsw
    where ap.rsw = rsw.rswoid
    and ap.task = 1 -- UNINSTALL
    and (ap.includedproc = 0 or ap.includedproc = 1 or ap.includedproc = 3) -- ADDEDPROC, EMBEDDEDPROC, CONVERTED
    group by rsw.rswoid

    -- Remove the activities where there are no recovery procs        
    delete from #actrsw
    where rswoid not in (select oid from #interm_count where counterid = 9)
    
    -- We now have a list of activites (#actrsw) with recovery procs
    
    -- Let us now see if we are go for recovery, check other existing applics...
    
    -- We should only consider these activities for recovery
    select objectid, activity, actproc, target into #act_valid_for_rec
    from usd_applic
    where(renewstatus = 0 -- UNDEFINED
    or renewstatus = 10) -- EXECUTION_ERROR
    and actproc <> 0x00000000000000000000000000000000
    and status = 10
    and task = 0x00000001
    and errorcause in (228001, 228002, 228003, 228004, 228005)
    and objectid in (select
        case
        when AP.firstrenew <> 0x00000000000000000000000000000000 then AP.firstrenew
        else AP.objectid
        end as objid
        from usd_applic AP, #activity ACT
        where AP.activity = ACT.objectid)
        
    -- Get rid of activities that have no recovery procs
    delete from #act_valid_for_rec
    where #act_valid_for_rec.activity not in (select #actrsw.actoid from #actrsw)

    -- Are there any other activites that (using the same proc and targeting the same comp)
    -- have succeeded applics?
    select AP.objectid, AP.activity, AP.target, AP.actproc into #act_not_valid_for_rec
    from usd_applic AP, #act_valid_for_rec AVFR
    where AP.target = AVFR.target
    and AP.actproc = AVFR.actproc
    and (
    /* task=install and status!=execution_error and status!=already_installed and status!=manipulation_not_allowed */
    (AP.task = 0x01 and AP.status != 10 and AP.status != 15 and AP.status != 16) or
    /* task=deliver and status!=delivery_error and status!=already_delivered */
    (AP.task = 0x10 and AP.status != 5 and AP.status != 6)
    )
    /* not uninstalled */
    and AP.uninstallstate <> 2
    and (AP.status = 9 or AP.status = 4)
    
    -- Get rid of them
    delete from #act_valid_for_rec
    from #act_not_valid_for_rec
    where #act_valid_for_rec.target = #act_not_valid_for_rec.target
    and #act_valid_for_rec.actproc = #act_not_valid_for_rec.actproc
    
    -- Last a check if there are any ongoing uninstalls
    select a.installation into #ongoing_uninstall_appl
    from usd_applic a, #act_valid_for_rec avfr
    where installation = avfr.objectid
    and  a.installation <> a.objectid /* do not read myself */
    and a.task = 0x02 -- UNINSTALL
    and status <> 10 -- EXECUTION_ERROR
    and status <> 5 -- DELIVERY_ERROR

    -- Get rid of them
    delete from #act_valid_for_rec
    from #ongoing_uninstall_appl
    where #act_valid_for_rec.objectid = #ongoing_uninstall_appl.installation

    -- Set bit SDAPI_JOB_RECOVER_ALLOWED
    update usd_activity set permmask = permmask | 4
    from #act_valid_for_rec avfr
    where usd_activity.objectid = avfr.activity
    and state in (3,1) -- ACTERROR or INPROGRESS

    -- Clear bit SDAPI_JOB_RECOVER_ALLOWED 1
    update usd_activity set permmask = permmask & ~4
    from #act_valid_for_rec avfr
    where usd_activity.objectid = avfr.activity
    and state not in (3,1) -- ACTERROR or INPROGRESS

    -- Clear bit SDAPI_JOB_RECOVER_ALLOWED 2
    update usd_activity set permmask = permmask & ~4
    from #activity
    where #activity.objectid not in (select activity from #act_valid_for_rec)
    and usd_activity.objectid = #activity.objectid

    -- Do the update, it is also time to update the version number
    -- This may be too much, can optimize it?
    update usd_activity set version = version + 1
    from #activity
    where usd_activity.objectid = #activity.objectid
    
    -- Time to delete objects...#activity_deleted
    -- Cleanup 3
    delete from usd_activity from #activity_deleted ad where usd_activity.objectid = ad.objectid
    delete from usd_link_jc_act from #activity_deleted ad where activity = ad.objectid

    -- Update the class versions
    update usd_class_version set modify_version = modify_version + 1 where name = 'link_jc_act'
    update usd_class_version set modify_version = modify_version + 1 where name = 'activity'

    -- Activities DONE!
    
    -- Get all the job container views related to this computer
    select distinct JCV.objectid as jcvoid, JC.objectid as jcoid into #jcview_jc
    from usd_jcappgr APG, usd_jcview JCV, usd_job_cont JC, deleted d
    where APG.jobtarget = d.object_uuid
    and APG.jobcontview = JCV.objectid
    and JC.objectid = JCV.jobcont

    -- We must now remove all the jcappgr of this computer as we need to recalculate
    
    -- Cleanup 4
    delete from usd_jcappgr from deleted where jobtarget = object_uuid
    -- Update the class version
    update usd_class_version set modify_version = modify_version + 1 where name = 'jcappgr'
    
    -- Now remove the jcviews from #jcview_jc that only targeted the jcappgr deleted above
    -- We only want to keep jcviews that involve other computers than the ones we deleted.
    -- For these kept jcviews we need to update counters
    select jcvoid, jcoid into #jcview_jc_deleted
    from #jcview_jc
    where jcvoid not in (select japg.jobcontview
                  from usd_jcappgr japg, #jcview_jc jv
                 where japg.jobcontview = jv.jcvoid)
        
    -- Remove the unwanted jcviews
    delete from #jcview_jc
    from #jcview_jc_deleted jjd
    where #jcview_jc.jcvoid = jjd.jcvoid

    -- Update class version
    update usd_class_version set modify_version = modify_version + 1 where name = 'jcappgr'
    
    -- Create a temp table to hold the new counters
    create table #jcvcount(jcvoid binary(16), waitcnt int default 0, actcnt int default 0, okcnt int default 0, errcnt int default 0,
                                waitrencnt int default 0, actrencnt int default 0, okrencnt int default 0, errrencnt int default 0)

    -- Insert all the objects to count for the views
    insert into #jcvcount(jcvoid)
    select distinct jcv.jcvoid
    from usd_jcappgr jcap, #jcview_jc jcv
    where jcap.jobcontview = jcv.jcvoid

    -- Update the counters for the view (JOBWAITING)
    insert into #interm_count(oid, cnt, counterid)
    select jcv.jcvoid, count(*) cnt, 17
    from usd_jcappgr jcap, #jcview_jc jcv
    where jcap.jobcontview = jcv.jcvoid
    and jobstatus = 0
    group by jcv.jcvoid

    update #jcvcount set waitcnt = cnt
    from #interm_count
    where #jcvcount.jcvoid = #interm_count.oid and #interm_count.counterid = 17

    -- Update the counters for the view (JOBACTIVE)
    insert into #interm_count(oid, cnt, counterid)
    select jcv.jcvoid, count(*) cnt, 10
    from usd_jcappgr jcap, #jcview_jc jcv
    where jcap.jobcontview = jcv.jcvoid
    and jobstatus = 1
    group by jcv.jcvoid

    update #jcvcount set actcnt = cnt
    from #interm_count
    where #jcvcount.jcvoid = #interm_count.oid and #interm_count.counterid = 10

    -- Update the counters for the view (JOBOK)
    insert into #interm_count(oid, cnt, counterid)
    select jcv.jcvoid, count(*) cnt, 11
    from usd_jcappgr jcap, #jcview_jc jcv
    where jcap.jobcontview = jcv.jcvoid
    and jobstatus = 2
    group by jcv.jcvoid

    update #jcvcount set okcnt = cnt
    from #interm_count
    where #jcvcount.jcvoid = #interm_count.oid and #interm_count.counterid = 11

    -- Update the counters for the view (JOBERROR)
    insert into #interm_count(oid, cnt, counterid)
    select jcv.jcvoid, count(*) cnt, 12
    from usd_jcappgr jcap, #jcview_jc jcv
    where jcap.jobcontview = jcv.jcvoid
    and jobstatus = 3
    group by jcv.jcvoid

    update #jcvcount set errcnt = cnt
    from #interm_count
    where #jcvcount.jcvoid = #interm_count.oid and #interm_count.counterid = 12

    -- Update the counters for the view (JOBWAITING RENEWED)
    insert into #interm_count(oid, cnt, counterid)
    select jcv.jcvoid, count(*) cnt, 13
    from usd_jcappgr jcap, #jcview_jc jcv
    where jcap.jobcontview = jcv.jcvoid
    and renewedjobstatus = 0
    group by jcv.jcvoid

    update #jcvcount set waitrencnt = cnt
    from #interm_count
    where #jcvcount.jcvoid = #interm_count.oid and #interm_count.counterid = 13

    -- Update the counters for the view (JOBACTIVE RENEWED)
    insert into #interm_count(oid, cnt, counterid)
    select jcv.jcvoid, count(*) cnt, 14
    from usd_jcappgr jcap, #jcview_jc jcv
    where jcap.jobcontview = jcv.jcvoid
    and renewedjobstatus = 1
    group by jcv.jcvoid

    update #jcvcount set actrencnt = cnt
    from #interm_count
    where #jcvcount.jcvoid = #interm_count.oid and #interm_count.counterid = 14

    -- Update the counters for the view (JOBOK RENEWED)
    insert into #interm_count(oid, cnt, counterid)
    select jcv.jcvoid, count(*) cnt, 15
    from usd_jcappgr jcap, #jcview_jc jcv
    where jcap.jobcontview = jcv.jcvoid
    and renewedjobstatus = 2
    group by jcv.jcvoid

    update #jcvcount set okrencnt = cnt
    from #interm_count
    where #jcvcount.jcvoid = #interm_count.oid and #interm_count.counterid = 15

    -- Update the counters for the view (JOBERROR RENEWED)
    insert into #interm_count(oid, cnt, counterid)
    select jcv.jcvoid, count(*) cnt, 16
    from usd_jcappgr jcap, #jcview_jc jcv
    where jcap.jobcontview = jcv.jcvoid
    and renewedjobstatus = 3
    group by jcv.jcvoid

    update #jcvcount set errrencnt = cnt
    from #interm_count
    where #jcvcount.jcvoid = #interm_count.oid and #interm_count.counterid = 16

    -- Go and do the big update to the real jcview table
    update usd_jcview set
    waitingcnt = waitcnt,
    activecnt = actcnt,
    okcnt = #jcvcount.okcnt,
    errorcnt = errcnt,
    waitingrenewcnt = waitrencnt,
    activerenewcnt = actrencnt,
    okrenewcnt = okrencnt,
    errorrenewcnt = errrencnt
    from #jcvcount
    where objectid = jcvoid
    
    -- Cleanup 5
    -- Delete usd_jcview,usd_job_cont,usd_link_jc
    delete from usd_jcview from #jcview_jc_deleted where objectid = jcvoid
    delete from usd_link_jc from #jcview_jc_deleted where jcparent = jcoid
    delete from usd_link_jc from #jcview_jc_deleted where jcchild = jcoid
    delete from usd_job_cont from #jcview_jc_deleted where objectid = jcoid
    -- Update the class version
    update usd_class_version set modify_version = modify_version + 1
    where name = 'link_jc'
    or name = 'job_cont'
end
GO
CREATE  trigger usd_trg_d_ca_agent_tbl_ver
on ca_agent
for delete as
declare
    @agent_type int
begin
    set @agent_type = (select top 1 agent_type from inserted)
    exec usd_proc_u_tbl_ver 0, -1, @agent_type, 40
end
GO
/* Trigger that updates status based on ca_agent:last_run_date */
create trigger usd_trg_u_ca_agent
on ca_agent
for update as
begin
    if update(last_run_date)
    begin
        /* Remove some bits that we do not want */
        update ca_agent set derived_status_sd = vts.object_status
        from inserted, usd_v_target_status vts
        where ca_agent.object_uuid = inserted.object_uuid
        and inserted.object_uuid = vts.object_uuid
    end
end
GO
CREATE  trigger usd_trg_u_ca_agent_tbl_ver
on ca_agent
for update as
declare
    @agent_type int
begin
    if update(creation_user) or update(agent_name) or update(ip_address) or update(creation_date) or update(last_run_date) or update(registration_type) or update(agent_type) or update(proc_os_id) or update(description) or update(prev_manager) or update(agent_ref_count)
    begin
    set @agent_type = (select top 1 agent_type from inserted)
        exec usd_proc_u_tbl_ver 0, -1, @agent_type, 40
    end
end
GO
ALTER TABLE [dbo].[ca_agent] ADD CONSTRAINT [XPKca_agent] PRIMARY KEY CLUSTERED ([object_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_agent_idx0] ON [dbo].[ca_agent] ([agent_type]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_agent_idx1] ON [dbo].[ca_agent] ([unit_id], [domain_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_agent] ADD CONSTRAINT [$ca_ag_r0000086400000000] FOREIGN KEY ([server_uuid]) REFERENCES [dbo].[ca_server] ([server_uuid])
GO
ALTER TABLE [dbo].[ca_agent] ADD CONSTRAINT [FKca_agent_tnt] FOREIGN KEY ([tenant_id]) REFERENCES [dbo].[ca_tenant] ([id])
GO
GRANT SELECT ON  [dbo].[ca_agent] TO [aiadmin]
GRANT SELECT ON  [dbo].[ca_agent] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_agent] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_agent] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_agent] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_agent] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_agent] TO [dms_backup_group]
GRANT INSERT ON  [dbo].[ca_agent] TO [dms_backup_group]
GRANT DELETE ON  [dbo].[ca_agent] TO [dms_backup_group]
GRANT UPDATE ON  [dbo].[ca_agent] TO [dms_backup_group]
GRANT SELECT ON  [dbo].[ca_agent] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_agent] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_agent] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_agent] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_agent] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_agent] TO [uapmadmin]
GRANT INSERT ON  [dbo].[ca_agent] TO [uapmadmin]
GRANT DELETE ON  [dbo].[ca_agent] TO [uapmadmin]
GRANT UPDATE ON  [dbo].[ca_agent] TO [uapmadmin]
GRANT SELECT ON  [dbo].[ca_agent] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[ca_agent] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[ca_agent] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[ca_agent] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[ca_agent] TO [uapmbatch]
GRANT INSERT ON  [dbo].[ca_agent] TO [uapmbatch]
GRANT DELETE ON  [dbo].[ca_agent] TO [uapmbatch]
GRANT UPDATE ON  [dbo].[ca_agent] TO [uapmbatch]
GRANT SELECT ON  [dbo].[ca_agent] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[ca_agent] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[ca_agent] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[ca_agent] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[ca_agent] TO [uapmreporting]
GRANT SELECT ON  [dbo].[ca_agent] TO [uapmreporting_group]
GRANT SELECT ON  [dbo].[ca_agent] TO [upmuser_group]
GO