CREATE TABLE [dbo].[ca_discovered_hardware]
(
[dis_hw_uuid] [binary] (16) NOT NULL,
[host_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[domain_uuid] [binary] (16) NOT NULL,
[label] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[serial_number] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[primary_mac_address] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[asset_tag] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[usage_list] [binary] (32) NULL,
[last_update_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL CONSTRAINT [DF__ca_discov__versi__15FA39EE] DEFAULT ((0)),
[host_uuid] [char] (36) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[creation_date] [int] NULL,
[creation_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[class_id] [int] NULL CONSTRAINT [DF__ca_discov__class__16EE5E27] DEFAULT ((0)),
[primary_network_address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[disc_serial_number] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[asset_source_uuid] [binary] (16) NULL,
[auto_rep_version] [timestamp] NULL,
[system_id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[uri] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[vendor_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[directory_url] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[discovery_changes_switch] [smallint] NULL CONSTRAINT [DF__ca_discov__disco__17E28260] DEFAULT ((1)),
[vuln_risk] [int] NULL,
[is_inventory_managed] [tinyint] NULL,
[asset_group_id] [binary] (16) NULL,
[vm_service_version_number] [int] NULL,
[content_version_number] [int] NULL,
[last_inventory_report_date] [int] NULL,
[is_created_as_asset] [tinyint] NULL,
[primary_subnet_mask] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[external_host_key] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[tenant_id] [binary] (16) NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE TRIGGER al_d_ca_discovered_hw ON ca_discovered_hardware
AFTER DELETE
AS
BEGIN

if dbo.is_installed(2022) = 1    /* Only run if SWCM is installed. */
  begin
    
    delete from al_link_dis_hw_contact
    where dis_hw_uuid in (select dis_hw_uuid from deleted)
     
  end
  
END


GO
SET QUOTED_IDENTIFIER ON
GO
/*
************************************************
* delete ca_discovered hardware
*/
CREATE trigger  r_d_discovered_hardware
    on ca_discovered_hardware
    instead of delete
as
   declare @old_object_uuid as binary(16);
    declare @ret as integer;
begin
    declare cur_cl cursor local for
        select dis_hw_uuid from deleted

    open cur_cl;
    fetch cur_cl into @old_object_uuid; -- get first
    while @@fetch_status = 0
    begin
    
        execute  @ret=p_d_discovered_hardware_pre @old_object_uuid;
        if (@ret != 0)
        begin
            return;
        end;
        delete from ca_discovered_hardware where dis_hw_uuid=@old_object_uuid;
        execute  p_d_discovered_hardware @old_object_uuid;
        fetch cur_cl into @old_object_uuid;

     end; -- while
     close cur_cl;
     deallocate cur_cl;

end;
GO
/****** Object:  Trigger dbo.r_i_dis_hw_host_uuid    Script Date: 12/1/2005 11:51:21 PM ******/



/*
************************************
trigger : check host_uuid  of new computer
*/

CREATE trigger r_i_dis_hw_host_uuid
       on ca_discovered_hardware
       after insert
as
  declare @_olduuid char(36);
  declare @_newuuid char(36);
  declare @_dom_uuid binary(16);

    
begin
    select @_olduuid= null;
    select @_newuuid= (select host_uuid from inserted);
    select @_dom_uuid = (select domain_uuid from inserted);

    
    execute p_iu_dis_hw_host_uuid @_olduuid, @_newuuid, @_dom_uuid;
    
end
GO
create trigger r_purge_url
    on ca_discovered_hardware
    after update
as
begin
    if update(uri)
        begin
           --if  ( :new.uri !=:old.uri and :new.directory_url =:old.directory_url )
            --then
            --    :NEW.directory_url:=NULL;
            --end if;
            update ca_discovered_hardware set ca_discovered_hardware.directory_url=NULL
                    from inserted,deleted,ca_discovered_hardware
                    where     inserted.uri!=deleted.uri
                            and inserted.directory_url=deleted.directory_url
                            and inserted.dis_hw_uuid=deleted.dis_hw_uuid
                            and inserted.domain_uuid=deleted.domain_uuid
                            and inserted.dis_hw_uuid=ca_discovered_hardware.dis_hw_uuid
                            and inserted.domain_uuid=ca_discovered_hardware.domain_uuid;
        end;
end
GO
/****** Object:  Trigger dbo.r_u_ca_discovered_hardware    Script Date: 12/1/2005 11:51:25 PM ******/


/*
**********************************************************
    trigger on after update (host_name, host_uuid)
    on ca_discovered_hardware to track all changes in the
    ca_discovered_hardware about host_name
**********************************************************
*/
CREATE trigger r_u_ca_discovered_hardware
    on ca_discovered_hardware
    after update
as    
    if update(host_name) or update(host_uuid)
begin

    declare @dis_hw_uuid binary(16);
    declare @host_name nvarchar(255);
    declare @domain_uuid binary(16);
    declare @host_uuid char(36);

    declare cursor_ca_discovered_hardware cursor local for
        select dis_hw_uuid, host_name, domain_uuid, host_uuid from inserted

    open cursor_ca_discovered_hardware;
    fetch next from cursor_ca_discovered_hardware into @dis_hw_uuid, @host_name, @domain_uuid, @host_uuid; -- get first
    while @@fetch_status = 0
    begin
    
        execute p_urc_ab_ca_discvd_hw_updated @dis_hw_uuid, @host_name, @domain_uuid, @host_uuid;
        fetch next from cursor_ca_discovered_hardware into @dis_hw_uuid, @host_name, @domain_uuid, @host_uuid;

    end; -- while
    close cursor_ca_discovered_hardware;
    deallocate cursor_ca_discovered_hardware;

end
GO
CREATE trigger r_u_dis_hw_host_uuid
on ca_discovered_hardware
after update
as
declare @_olduuid char(36);
declare @_newuuid char(36);
declare @_dom_uuid binary(16);

begin

    if update(host_uuid)
        begin

            DECLARE cur_r_u_dis_hw_host_uuid CURSOR LOCAL
            FOR select deleted.host_uuid, inserted.host_uuid, deleted.domain_uuid
            from deleted, inserted
            where deleted.dis_hw_uuid= inserted.dis_hw_uuid    /* itrac 12093*/
                    and deleted.domain_uuid= inserted.domain_uuid    /* itrac 12093*/

            OPEN cur_r_u_dis_hw_host_uuid
            FETCH NEXT FROM cur_r_u_dis_hw_host_uuid INTO @_olduuid, @_newuuid, @_dom_uuid;
            WHILE @@FETCH_STATUS = 0
                BEGIN

                    execute p_iu_dis_hw_host_uuid @_olduuid, @_newuuid, @_dom_uuid;
                    FETCH NEXT FROM cur_r_u_dis_hw_host_uuid INTO @_olduuid, @_newuuid, @_dom_uuid;

                END

                CLOSE cur_r_u_dis_hw_host_uuid
                DEALLOCATE cur_r_u_dis_hw_host_uuid

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



/*
***********************************************
trigger on update on computer
*/
CREATE trigger r_upd_verno_dis_hw
        on ca_discovered_hardware
        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
/****** Object:  Trigger dbo.rule_d_so_removed_computer    Script Date: 12/1/2005 11:51:34 PM ******/




/*
******************************************
  trigger if a computer is deleted
*/
CREATE trigger rule_d_so_removed_computer
on ca_discovered_hardware
after delete
as

begin
    declare @old_object_uuid as binary(16);

    declare cur_cl cursor for
          select dis_hw_uuid from deleted;

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

     end; -- while
     close cur_cl;
     deallocate cur_cl;

end;
GO
/****** Object:  Trigger dbo.rule_i_new_so_computer    Script Date: 12/1/2005 11:51:39 PM ******/



/*
**********************************************
rule for insert of Class Discoverd Hardware
*/
CREATE trigger rule_i_new_so_computer
    on ca_discovered_hardware
    after insert
as    
begin
    declare @_obj_uuid     binary(16);
    declare @_clsid integer;
    declare @_uri nvarchar(255);

    select @_obj_uuid     = (select dis_hw_uuid from inserted);
    select @_clsid = 1000;
    select @_uri = (select creation_user from inserted);
             
    execute  proc_i_new_so_object  @_obj_uuid, @_clsid, @_uri;
end;
GO
create trigger t_ca_agent_status_rc_update_usage_list
    on ca_discovered_hardware
    after update
as
if update ( usage_list )
    begin
        /* get the uuid */
        declare @l_agent_uuid binary(16);
        set @l_agent_uuid = (select dis_hw_uuid from INSERTED);
        exec p_ca_rc_agent_status_update @l_agent_uuid;
    end;
GO

/****** Object:  Trigger dbo.t_d_ca_discovered_hw    Script Date: 12/1/2005 11:51:52 PM ******/
--GRANT EXECUTE ON ca_merge_class_for_logical_asset TO PUBLIC;
--GO

/****************************************************************************************/
/*  Change Control                                                                      */
/*                                                                                      */
/*  Change Date Changes By  Version                                                     */
/*  =========== ==========  =======                                                     */
/*  17-mar-2005 mulwi01     Star 14015015 CAMDB - UAPM SQL PROC                         */
/*                                                                                      */
/****************************************************************************************/


CREATE TRIGGER t_d_ca_discovered_hw ON ca_discovered_hardware
FOR DELETE AS
    
    /* Check whether any Reconciliation tasks are tracking discovery deletions. */
    /* If none are, then don't continue.                                        */
        
    if (SELECT count(*) FROM arg_reconcile_task WHERE discovery_deleted_indicator <> 0) = 0
        return
    
    declare @own_resource_uuid binary(16)
    declare @max_id integer
    declare @mod_rec_count integer
    declare @sys_name varchar(30)
    declare @date_in_seconds integer
    declare @dis_hw_uuid binary(16)
    declare @update_user varchar(30)
    declare @host_name nvarchar(255)
    declare @serial_number nvarchar(64)
    declare @vendor_name nvarchar(255)
    
    set @mod_rec_count = 0
    set @date_in_seconds = 0
    set @sys_name = 'Unicenter AM'
    set @update_user = 't_d_ca_discovered_hw'
    
    declare delete_list cursor for
      select dis_hw_uuid, host_name, serial_number, vendor_name from deleted
       
    open delete_list
    
    fetch next from delete_list into @dis_hw_uuid, @host_name, @serial_number, @vendor_name
    
    while @@fetch_status = 0
        begin
    
            /* Check whether the discovered asset record is linked to an owned resource record. */
        /* If not, skip this record.                                                        */
        
        select @own_resource_uuid = (SELECT oa.own_resource_uuid
                                  FROM ca_asset_source ds
                             INNER JOIN ca_asset_source os
                                 ON os.logical_asset_uuid = ds.logical_asset_uuid and os.subschema_id = 1
                             INNER JOIN ca_owned_resource oa
                                 ON oa.asset_source_uuid = os.asset_source_uuid
                               WHERE ds.asset_source_uuid = @dis_hw_uuid)
                
        if @own_resource_uuid is null
            goto Skip_Record
            
        /* Check whether an arg_reconcile_modification record already exists for this asset. */
        /* If so, skip this record.                                                          */
        
        select @mod_rec_count = (SELECT count(*)
                       FROM arg_reconcile_modification
                      WHERE dis_hw_uuid = @dis_hw_uuid)
           
        if @mod_rec_count > 0
            goto Skip_Record
      
            /* Set up creation_date, last_update_date  */
        
            set @date_in_seconds = (SELECT DATEDIFF(second, cast('1/1/1970' as datetime), getdate()))
        
        /* Get value to put into reconcile_modification_id. */
    
        select @max_id = (SELECT max(reconcile_modification_id)
                    FROM arg_reconcile_modification
                   WHERE sys_name = @sys_name)
    
        if @max_id is null
            set @max_id = 0
        else
            set @max_id = @max_id + 1
            
      
        /* Create the arg_reconcile_modification record. */
            
        INSERT INTO arg_reconcile_modification
               (sys_name,
                reconcile_modification_id,
                reconcile_action,
                own_resource_uuid,
                dis_hw_uuid,
                discovery_asset_name,
                discovery_asset_serial_num,
                discovery_asset_sys_vendor,
                creation_user,
                creation_date,
                last_update_user,
                last_update_date,
                version_number)
            
           VALUES
               (@sys_name,
                @max_id,
                3,
                @own_resource_uuid,
                @dis_hw_uuid,
                @host_name,
                @serial_number,
                @vendor_name,
                @update_user,
                @date_in_seconds,
                @update_user,
                @date_in_seconds,
                0)
                
        Skip_Record:
    
            fetch next from delete_list into @dis_hw_uuid, @host_name, @serial_number, @vendor_name
            
        end
        
    close delete_list
    
    deallocate delete_list     



GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE TRIGGER t_u_ca_discovered_hw ON ca_discovered_hardware
FOR UPDATE AS

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

if update (host_name)
    begin
    
        declare @dis_hw_uuid binary(16)
        declare @new_host_name nvarchar(255)
        declare @old_host_name nvarchar(255)

        declare change_list cursor for
          select inserted.dis_hw_uuid, inserted.host_name, deleted.host_name from inserted, deleted
           where inserted.discovery_changes_switch = 0 and inserted.dis_hw_uuid = deleted.dis_hw_uuid
   
        open change_list

        fetch next from change_list into @dis_hw_uuid, @new_host_name, @old_host_name

        while @@fetch_status = 0
            begin

               /*  Only process if the host_name value has changed to something different. */

           if @new_host_name <> @old_host_name
                   begin

                       /* Set the discovery_changes_switch 'on'. */

                       update ca_discovered_hardware
                      set discovery_changes_switch = 1
                        where dis_hw_uuid = @dis_hw_uuid
                   end

                fetch next from change_list into @dis_hw_uuid, @new_host_name, @old_host_name
        
            end
    
        close change_list

        deallocate change_list

    end

GO
SET QUOTED_IDENTIFIER ON
GO
/* Start of locks for dependent tables */

/* End of lines added to convert to online lock */

/* ************************** 11682 begin  **************/

CREATE  trigger usd_trg_u_ca_dis_hw_tbl_ver
on ca_discovered_hardware
for update as
begin
    if update(primary_network_address) or update(host_uuid)
    begin
        exec usd_proc_u_tbl_ver 0, -1, 1, 40
    end
end
GO
ALTER TABLE [dbo].[ca_discovered_hardware] ADD CONSTRAINT [XPKca_discovered_hardware] PRIMARY KEY CLUSTERED ([dis_hw_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_disc_hardware_idx_05] ON [dbo].[ca_discovered_hardware] ([asset_source_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_disc_hardware_idx_01] ON [dbo].[ca_discovered_hardware] ([domain_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_disc_hardware_idx_07] ON [dbo].[ca_discovered_hardware] ([external_host_key]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_disc_hardware_idx_06] ON [dbo].[ca_discovered_hardware] ([host_name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_disc_hardware_idx_04] ON [dbo].[ca_discovered_hardware] ([host_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_disc_hardware_idx_03] ON [dbo].[ca_discovered_hardware] ([label]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_disc_hardware_idx_02] ON [dbo].[ca_discovered_hardware] ([last_update_date]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_discovered_hardware] ADD CONSTRAINT [$ca_di_r000002ac00000000] FOREIGN KEY ([asset_source_uuid]) REFERENCES [dbo].[ca_asset_source] ([asset_source_uuid])
GO
ALTER TABLE [dbo].[ca_discovered_hardware] ADD CONSTRAINT [$ca_di_r00000a2600000000] FOREIGN KEY ([domain_uuid]) REFERENCES [dbo].[ca_n_tier] ([domain_uuid])
GO
ALTER TABLE [dbo].[ca_discovered_hardware] ADD CONSTRAINT [FKca_dishw_tnt] FOREIGN KEY ([tenant_id]) REFERENCES [dbo].[ca_tenant] ([id])
GO
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [aiadmin]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [ams_group]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [amsgroup]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_discovered_hardware] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_discovered_hardware] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [dms_backup_group]
GRANT INSERT ON  [dbo].[ca_discovered_hardware] TO [dms_backup_group]
GRANT DELETE ON  [dbo].[ca_discovered_hardware] TO [dms_backup_group]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware] TO [dms_backup_group]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_discovered_hardware] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_discovered_hardware] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [uapmadmin]
GRANT INSERT ON  [dbo].[ca_discovered_hardware] TO [uapmadmin]
GRANT DELETE ON  [dbo].[ca_discovered_hardware] TO [uapmadmin]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware] TO [uapmadmin]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[ca_discovered_hardware] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[ca_discovered_hardware] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [uapmbatch]
GRANT INSERT ON  [dbo].[ca_discovered_hardware] TO [uapmbatch]
GRANT DELETE ON  [dbo].[ca_discovered_hardware] TO [uapmbatch]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware] TO [uapmbatch]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[ca_discovered_hardware] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[ca_discovered_hardware] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[ca_discovered_hardware] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [uapmreporting]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [uapmreporting_group]
GRANT SELECT ON  [dbo].[ca_discovered_hardware] TO [upmuser_group]
GO