CREATE TABLE [dbo].[ca_agent_component]
(
[agent_comp_id] [int] NOT NULL,
[object_uuid] [binary] (16) NOT NULL,
[agent_component_version] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[server_uuid] [binary] (16) NULL,
[auto_rep_version] [timestamp] NULL
) ON [PRIMARY]
GO
/****** Object:  Trigger dbo.r_del_agent_component    Script Date: 12/1/2005 11:51:19 PM ******/


CREATE trigger r_del_agent_component
    on ca_agent_component
    after delete
as
    declare @_action     as integer;
    declare @_server_uuid as binary(16);
    declare @_comp_id as integer;
begin
  select @_action        = 3;
    
    
    declare cur_del cursor for
        select object_uuid, agent_comp_id from deleted;
       
  open cur_del;
  fetch cur_del into @_server_uuid, @_comp_id; -- get first
  while @@fetch_status = 0
  begin
    
        execute p_integrity_component_reg @_action, @_server_uuid ,@_comp_id;
        fetch cur_del into @_server_uuid, @_comp_id; -- get next

  end; -- while
  close cur_del;
  deallocate cur_del;    
end
GO
/****** Object:  Trigger dbo.r_insert_agent_component    Script Date: 12/1/2005 11:51:23 PM ******/

/*
***********************************************
trigger for insert a new agent_component
*/

CREATE trigger r_insert_agent_component
    on ca_agent_component
    after insert
as
    declare @_action     as integer;
    declare @_server_uuid as binary(16);
    declare @_comp_id as integer;
begin
    select @_action        = 2;
    select @_server_uuid     = (select object_uuid from inserted);
    select @_comp_id     = (select agent_comp_id from inserted);
    
    execute p_integrity_component_reg @_action, @_server_uuid ,@_comp_id;

end
GO
SET QUOTED_IDENTIFIER OFF
GO

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

/*
*********************************************
trigger for creating ca_agent_component
*/

create trigger rule_set_backup_status
            on ca_agent_component
        after insert
    as
    begin
        declare @_object_uuid binary(16);
   
        select @_object_uuid = (select object_uuid from inserted);
        
        execute  backup_p_set_backup_status  @_object_uuid;

    end;


GO
SET QUOTED_IDENTIFIER ON
GO
create trigger uam_trg_ca_agent_comp_delete
    on ca_agent_component
    for delete
as
begin
    declare @object_uuid binary(16) ;
    declare @agent_comp_id integer;
    
    DECLARE am_cur CURSOR
        FOR SELECT object_uuid,agent_comp_id
            FROM deleted
            
    OPEN am_cur
    FETCH NEXT FROM am_cur INTO @object_uuid,@agent_comp_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF(@agent_comp_id=80 OR @agent_comp_id=86)
        BEGIN
          EXECUTE ca_am_update_agent_derived @object_uuid
        END
        FETCH NEXT FROM am_cur INTO @object_uuid,@agent_comp_id
    END
    CLOSE am_cur
    DEALLOCATE am_cur
end
GO
create trigger uam_trg_ca_agent_comp_insert
     on ca_agent_component
     after insert
as
begin
    declare @object_uuid binary(16) ;
    declare @agent_comp_id integer;
    
    DECLARE am_cur CURSOR
        FOR SELECT object_uuid,agent_comp_id
            FROM inserted
            
    OPEN am_cur
    FETCH NEXT FROM am_cur INTO @object_uuid,@agent_comp_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF(@agent_comp_id=80 OR @agent_comp_id=86)
        BEGIN
          EXECUTE ca_am_update_agent_derived @object_uuid
        END
        FETCH NEXT FROM am_cur INTO @object_uuid,@agent_comp_id
    END
    CLOSE am_cur
    DEALLOCATE am_cur
end
GO
CREATE  trigger usd_trg_d_agent_comp_usd_rel
on ca_agent_component
for delete as
declare
    @counted int
begin
    -- Does the agent exist?
    set @counted = (select count (*)
    from ca_agent where object_uuid in (select object_uuid from deleted))
    if(@counted = 0)
    begin
    -- It doesn't then just allow this as we are about to remove the whole computer
        return
    end

    set @counted = (select count(*)
    from deleted d, usd_applic app, usd_activity act
    where app.target = d.object_uuid
    and app.activity = act.objectid
    and (d.agent_comp_id = 40 or d.agent_comp_id = 46))
    if(@counted > 0)
    begin
        rollback transaction
        raiserror('Error 9018: Cannot remove the USD agent component because of Software Delivery jobs', 16, 1 )
    return
    end

    -- else update class version
    update usd_class_version set modify_version = modify_version + 1 where name = 'target'

    -- Update derived_status_sd
    update ca_agent set derived_status_sd = vts.object_status
    from deleted, usd_v_target_status vts
    where ca_agent.object_uuid = deleted.object_uuid
    and deleted.object_uuid = vts.object_uuid
end
GO
CREATE  trigger usd_trg_i_new_usd_target
on ca_agent_component
for insert as
declare
    @tcount int
begin
    /* The USD component may have been temporarily removed, that is */
    /* why the target may still exist */

    /* Optimize */
    if(@@ROWCOUNT = 1)
    begin
        set @tcount = (select count(*) from inserted where (agent_comp_id = 40 or agent_comp_id = 46))
        if (@tcount > 0)
        begin
            set @tcount = (select count(*) from usd_target where objectid = (select object_uuid from inserted where (agent_comp_id = 40 or agent_comp_id = 46)))
        if (@tcount = 0)
            begin
                /* Insert the new row, we know there is only one */
                insert into usd_target(objectid, calendar, download_method)
                select object_uuid, '', 0
                from inserted
                where (agent_comp_id = 40 or agent_comp_id = 46)

                /* Make sure we update the class version for the above operations */
                update usd_class_version set modify_version = modify_version + 1
                where name = 'target'
            end
        end
    end
    else
    begin
        /* Handle multirow */
        insert into usd_target(objectid, calendar, download_method)
        select object_uuid, '', 0
        from inserted
        where (agent_comp_id = 40 or agent_comp_id = 46)
        and object_uuid not in (select objectid from usd_target)

        /* Make sure we update the class version for the above operations */
        update usd_class_version set modify_version = modify_version + 1
        where name = 'target'
    end
    
    -- Update derived_status_sd
    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
GO
/****** Object:  Trigger dbo.usd_trg_u_ca_ag_comp_tbl_ver    Script Date: 12/1/2005 11:51:00 PM ******/
/* Rule on ca_agent_component, on insert, update change USD table version */
CREATE trigger usd_trg_u_ca_ag_comp_tbl_ver
on ca_agent_component
for insert, update as
declare
    @i_comp_id int
begin
    if update(agent_component_version)
    begin
        set @i_comp_id = (select top 1 agent_comp_id from inserted)
        exec usd_proc_u_tbl_ver 0, -1, 1, @i_comp_id
    end
end
GO
ALTER TABLE [dbo].[ca_agent_component] ADD CONSTRAINT [XPKca_agent_component] PRIMARY KEY CLUSTERED ([object_uuid], [agent_comp_id]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[ca_agent_component] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_agent_component] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_agent_component] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_agent_component] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_agent_component] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_agent_component] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_agent_component] TO [upmuser_group]
GO