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
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
/*
************************************************
* 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
create trigger r_upd_dhw_assetsrcuid
     on [dbo].[ca_discovered_hardware]
     after update
as
begin
-- trigger which updates the tenant id of discovered hardware
-- just in case a discovered hardware is inserted and asset source uuid
-- was not set and now the asset_source uuid will be set/updated

    

    if (update(asset_source_uuid))
    begin

        update ca_discovered_hardware
                set tenant_id = a.tenant
        from ca_asset_source asrc, ca_logical_asset la, ca_asset a, inserted dhwupd
        where ca_discovered_hardware.asset_source_uuid = asrc.asset_source_uuid
        and  asrc.logical_asset_uuid = la.logical_asset_uuid
        and la.asset_uuid = a.asset_uuid
        and ca_discovered_hardware.dis_hw_uuid = dhwupd.dis_hw_uuid;
    end;

end;
GO
create trigger r_upd_dishwtid
     on [dbo].[ca_discovered_hardware]
     after update
as
begin
-- update tenant id if discovered hardware tenant id is set

    if ( update(tenant_id))
    begin
        declare @dishwuuid binary(16);
        declare @tid binary(16);

        DECLARE mycur CURSOR local
        FOR select dis_hw_uuid, tenant_id from inserted
            where tenant_id is not null;
        
        OPEN mycur
        FETCH NEXT FROM mycur INTO @dishwuuid, @tid;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXECUTE  sp_upd_tid4asset @dishwuuid,  @tid;
            FETCH NEXT FROM mycur INTO @dishwuuid, @tid;
        END

        CLOSE mycur
        DEALLOCATE mycur
        
    end;
end;
GO
create trigger r_upd_tenantid
     on [dbo].[ca_discovered_hardware]
     after insert
as
begin
-- trigger which updates the tenant id of discovered hardware
-- just in case a discovered hardware is inserted after the asset registration



    update ca_discovered_hardware
            set tenant_id = a.tenant
    from ca_asset_source asrc, ca_logical_asset la, ca_asset a, inserted dhwupd
    where ca_discovered_hardware.asset_source_uuid = asrc.asset_source_uuid
    and  asrc.logical_asset_uuid = la.logical_asset_uuid
    and la.asset_uuid = a.asset_uuid
    and ca_discovered_hardware.dis_hw_uuid = dhwupd.dis_hw_uuid;

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
CREATE TRIGGER [dbo].[t_cu_ca_discovered_hw]
ON [dbo].[ca_discovered_hardware]
AFTER UPDATE
AS
    if dbo.is_installed(2002) = 0
    return
                
    DECLARE @ModCount integer;
    DECLARE @DisHwUUID [binary](16);
    DECLARE @SysName varchar(10);
    DECLARE @DateInSeconds int;
    DECLARE @UpdateUser varchar(30);

    IF (UPDATE(host_name) or UPDATE(serial_number) or UPDATE(primary_mac_address) or
        UPDATE(asset_tag) or UPDATE(vendor_name))
    BEGIN
        Set @ModCount = 0;                
        Set @SysName = 'ITAM';
        Set @DateInSeconds = 0;
        Set @UpdateUser = 't_cu_ca_discovered_hardware';
        Set @DateInSeconds = (datediff(ss, '1/1/1970', getutcdate()));            

        insert into arg_reconcile_modification
                    (sys_name, reconcile_action,
                     dis_hw_uuid, own_resource_uuid,
                     tenant, subschema_id, creation_user, creation_date,
                     last_update_user, last_update_date, version_number)
        Select @SysName, 2,
               d.dis_hw_uuid, lnk.own_resource_uuid,
               d.tenant_id, 3, @UpdateUser, @DateInSeconds,
               @UpdateUser, @DateInSeconds, 0
          from inserted i
          join deleted d  
            on (d.dis_hw_uuid = i.dis_hw_uuid and
                ((dbo.is_different(d.host_name, i.host_name) = 1) or
                 (dbo.is_different(d.serial_number, i.serial_number) = 1) or
                 (dbo.is_different(d.primary_mac_address, i.primary_mac_address) = 1) or
                 (dbo.is_different(d.asset_tag, i.asset_tag) = 1) or
                 (dbo.is_different(d.vendor_name, i.vendor_name) = 1)))
          join arg_reconcile_links lnk
            on (d.dis_hw_uuid = lnk.dis_hw_uuid)
    END
GO
CREATE TRIGGER [dbo].[t_d_ca_discovered_hw]
ON [dbo].[ca_discovered_hardware]
AFTER DELETE
AS
BEGIN
DECLARE
        @vCount int,
        @vOwnResourceUUID binary(16),
        @vSysName varchar(10),
        @vDateInSeconds int,
        @vUpdateUser varchar(30),
        @vStartDays int,
        @vEndDays int,
        @vEndSeconds int;

    SET @vCount = 0;
        SET @vSysName = 'ITCM';
        SET @vDateInSeconds = 0;
        SET @vUpdateUser = 't_d_ca_discovered_hw';
        SET @vStartDays = 2440588;  /* 1/1/1970 in Julian format. */

--if update()

    /* Check whether the discovered hardware record is linked to an owned resource record. */
    /* If it is, then continue.                                                            */
    /* Count is checked first to avoid NO_DATA_FOUND error when using SELECT INTO.         */

    SELECT @vCount = count(*) FROM dbo.arg_reconcile_links as a, deleted as d WHERE a.dis_hw_uuid = d.dis_hw_uuid;

    IF @vCount = 1 BEGIN

        /* Capture the own_resource_uuid of the linked owned asset. */

        SELECT @vOwnResourceUUID = own_resource_uuid FROM dbo.arg_reconcile_links as a, deleted as d WHERE a.dis_hw_uuid = d.dis_hw_uuid;

        /* Delete the arg_reconcile_links record. */

        DELETE FROM dbo.arg_reconcile_links WHERE dis_hw_uuid in (select deleted.dis_hw_uuid from deleted);

        /* Check whether an arg_reconcile_modification record already exists for this asset. */
          /* If not, then continue.                                                            */

        SELECT @vCount = count(*)
        FROM dbo.arg_reconcile_modification as a, deleted as d  
        WHERE a.dis_hw_uuid = d.dis_hw_uuid
        AND a.sys_name = @vSysName
        AND a.reconcile_action =3;

        IF @vCount = 0 BEGIN

            /* Set up creation_date, last_update_date  */
            --prasu05 - verify for now adding dump values..
            --@vEndDays := TO_NUMBER(TO_CHAR(SYSDATE, 'J'));
            SET @vEndDays = 1;

            --@vEndSeconds := TO_NUMBER(TO_CHAR(SYSDATE, 'SSSSS'));
            SET @vEndSeconds = 2;

            --@vDateInSeconds := ((@vEndDays - @vStartDays) * 86400) + @vEndSeconds;
            SET @vDateInSeconds = 3;

            /* Create the arg_reconcile_modification record. */

            
        INSERT INTO dbo.arg_reconcile_modification
                   (sys_name,
                    reconcile_action,
                    own_resource_uuid,
                    dis_hw_uuid,
                    discovery_asset_name,
                    discovery_asset_serial_num,
                    discovery_asset_sys_vendor,
                    tenant,
                    subschema_id,
                    creation_user,
                    creation_date,
                    last_update_user,
                    last_update_date,
                    version_number)

            select
            @vSysName,
                3,
                @vOwnResourceUUID,
                deleted.dis_hw_uuid,
                deleted.host_name,
                deleted.serial_number,
                deleted.vendor_name,
                deleted.tenant_id,
                3,
                @vUpdateUser,
                @vDateInSeconds,
                @vUpdateUser,
                @vDateInSeconds,
                0 from deleted;
        END
    END
END
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
/* 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 [upmuser_group]
GO