CREATE TABLE [dbo].[inv_generalinventory_item]
(
[object_uuid] [binary] (16) NOT NULL,
[item_parent_id] [int] NOT NULL,
[item_name_id] [int] NOT NULL,
[item_root_id] [int] NULL,
[item_root_name_id] [int] NULL,
[item_parent_name_id] [int] NULL,
[item_index] [int] NULL,
[item_type] [int] NULL,
[item_format] [int] NULL,
[item_value_text] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[item_previous_value_text] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[item_value_long] [int] NULL,
[item_previous_value_long] [int] NULL,
[item_value_double] [float] NULL,
[item_previous_value_double] [float] NULL,
[item_date] [int] NULL,
[item_lock] [int] NULL,
[domain_uuid] [binary] (16) NULL,
[auto_rep_version] [timestamp] NULL,
[object_type] [int] NULL,
[tenant_id] [binary] (16) NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE TRIGGER al_cd_inv_generalinventory_itm ON inv_generalinventory_item
AFTER INSERT, DELETE
AS
BEGIN

declare @table_name varchar(30)
declare @update_date integer
declare @update_user varchar(30)
set @table_name = 'inv_generalinventory_item'
set @update_user = SYSTEM_USER + '(al_cd_inv_generalinventory_itm)'
set @update_date = datediff(ss, '1/1/1970', getutcdate())

if dbo.is_installed(2022) = 1    /* Only run if SWCM is installed. */
  begin
  
    insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], new_value, creation_user, creation_date, last_update_user, last_update_date)
    select object_uuid, @table_name, 'number_of_cpus', 1, item_value_long, @update_user, @update_date, @update_user, @update_date
      from inserted
     where item_parent_name_id = 2 and item_name_id = 9
    
    insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], new_value, creation_user, creation_date, last_update_user, last_update_date)
    select object_uuid, @table_name, 'hardware_device_type', 1, item_value_text, @update_user, @update_date, @update_user, @update_date
      from inserted
     where item_parent_name_id = 2 and item_name_id = 1
  
    insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], old_value, creation_user, creation_date, last_update_user, last_update_date)
    select object_uuid, @table_name, 'number_of_cpus', 2, item_value_long, @update_user, @update_date, @update_user, @update_date
      from deleted
     where item_parent_name_id = 2 and item_name_id = 9
    
    insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], old_value, creation_user, creation_date, last_update_user, last_update_date)
    select object_uuid, @table_name, 'hardware_device_type', 2, item_value_text, @update_user, @update_date, @update_user, @update_date
      from deleted
     where item_parent_name_id = 2 and item_name_id = 1
     
  end
  
END


GO

CREATE TRIGGER t_u_inv_generalinventory_item ON inv_generalinventory_item
AFTER UPDATE
AS
BEGIN

declare @table_name varchar(30)
declare @update_date integer
declare @update_user varchar(30)
set @table_name = 'inv_generalinventory_item'
set @update_user = SYSTEM_USER + '(t_u_inv_generalinventory_item)'
set @update_date = datediff(ss, '1/1/1970', getutcdate())

if update (item_value_text) or update (item_value_long) or update (item_value_double)
  begin
  
    if dbo.is_installed(2002) = 1    /* Only run this part if UAPM is installed. */
      begin    
    
    update ca_discovered_hardware
       set discovery_changes_switch = 1, last_update_date = @update_date, last_update_user = @update_user
     where discovery_changes_switch <> 1
       and dis_hw_uuid in (select distinct object_uuid
                             from inserted
                                where (item_parent_name_id = 2  and item_name_id = 7   and dbo.is_different(item_value_double, item_previous_value_double) = 1)
                                   or (item_parent_name_id = 2  and item_name_id = 9   and dbo.is_different(item_value_long, item_previous_value_long) = 1)
                                   or (item_parent_name_id = 5  and item_name_id = 17  and dbo.is_different(item_value_text, item_previous_value_text) = 1)
                                   or (item_parent_name_id = 6  and item_name_id = 1   and dbo.is_different(item_value_text, item_previous_value_text) = 1)
                                   or (item_parent_name_id = 6  and item_name_id = 13  and dbo.is_different(item_value_long, item_previous_value_long) = 1)
                                   or (item_parent_name_id = 31 and item_name_id = 132 and dbo.is_different(item_value_double, item_previous_value_double) = 1))
      end

  
    if dbo.is_installed(2022) = 1    /* Only run this part if SWCM is installed. */
      begin  
    
        insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], old_value, new_value, creation_user, creation_date, last_update_user, last_update_date)
        select object_uuid, @table_name, 'number_of_cpus', 0, item_previous_value_long, item_value_long, @update_user, @update_date, @update_user, @update_date
          from inserted
     where item_parent_name_id = 2 and item_name_id = 9 and dbo.is_different(item_value_long, item_previous_value_long) = 1
    
        insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], old_value, new_value, creation_user, creation_date, last_update_user, last_update_date)
        select object_uuid, @table_name, 'hardware_device_type', 0, item_previous_value_text, item_value_text, @update_user, @update_date, @update_user, @update_date
          from inserted
     where item_parent_name_id = 2 and item_name_id = 1 and dbo.is_different(item_value_text, item_previous_value_text) = 1
     
      end
  end
  
END

GO
ALTER TABLE [dbo].[inv_generalinventory_item] ADD CONSTRAINT [XPKinv_generalinventory_item] PRIMARY KEY CLUSTERED ([object_uuid], [item_name_id], [item_parent_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [inv_generalinventory_item_idx1] ON [dbo].[inv_generalinventory_item] ([auto_rep_version], [domain_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [inv_generalinventory_item_idx0] ON [dbo].[inv_generalinventory_item] ([item_parent_name_id], [item_name_id], [item_root_name_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [inv_generalinventory_browse] ON [dbo].[inv_generalinventory_item] ([item_root_name_id], [item_parent_name_id], [item_index], [domain_uuid], [item_type], [item_format], [item_name_id], [item_value_text], [item_value_long], [item_value_double]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[inv_generalinventory_item] ADD CONSTRAINT [FKinv_genitem_tnt] FOREIGN KEY ([tenant_id]) REFERENCES [dbo].[ca_tenant] ([id])
GO
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [aiadmin]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [ams_group]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [amsgroup]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[inv_generalinventory_item] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[inv_generalinventory_item] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[inv_generalinventory_item] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [swcmadmin]
GRANT INSERT ON  [dbo].[inv_generalinventory_item] TO [swcmadmin]
GRANT DELETE ON  [dbo].[inv_generalinventory_item] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[inv_generalinventory_item] TO [swcmadmin]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [uapmadmin]
GRANT INSERT ON  [dbo].[inv_generalinventory_item] TO [uapmadmin]
GRANT DELETE ON  [dbo].[inv_generalinventory_item] TO [uapmadmin]
GRANT UPDATE ON  [dbo].[inv_generalinventory_item] TO [uapmadmin]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[inv_generalinventory_item] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[inv_generalinventory_item] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[inv_generalinventory_item] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [uapmbatch]
GRANT INSERT ON  [dbo].[inv_generalinventory_item] TO [uapmbatch]
GRANT DELETE ON  [dbo].[inv_generalinventory_item] TO [uapmbatch]
GRANT UPDATE ON  [dbo].[inv_generalinventory_item] TO [uapmbatch]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[inv_generalinventory_item] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[inv_generalinventory_item] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[inv_generalinventory_item] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [uapmreporting]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [uapmreporting_group]
GRANT SELECT ON  [dbo].[inv_generalinventory_item] TO [upmuser_group]
GO