CREATE TABLE [dbo].[ca_agent_prop]
(
[object_uuid] [binary] (16) NOT NULL,
[domain_uuid] [binary] (16) NOT NULL,
[creation_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] 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_agent___versi__04C657A2] DEFAULT ('0'),
[auto_rep_version] [timestamp] NULL,
[genModel] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockGenModel] [int] NULL,
[genType] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockGenType] [int] NULL,
[genTotMemory] [float] NULL,
[lockGenTotMemory] [int] NULL,
[genNumProcs] [float] NULL,
[lockGenNumProcs] [int] NULL,
[genRegAsset] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockGenRegAsset] [int] NULL,
[genNetName] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockGenNetName] [int] NULL,
[genNetIP] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockGenNetIP] [int] NULL,
[genOS] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockGenOS] [int] NULL,
[genProcType] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockGenProcType] [int] NULL,
[genProcSpeed] [int] NULL,
[lockGenProcSpeed] [int] NULL,
[genTotDisk] [float] NULL,
[lockGenTotDisk] [int] NULL,
[genBatchID] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockGenBatchID] [int] NULL,
[ndIP] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdIP] [int] NULL,
[ndMAC] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdMAC] [int] NULL,
[ndSwName] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdSwName] [int] NULL,
[ndPortName] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdPortName] [int] NULL,
[ndPortType] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdPortType] [int] NULL,
[ndPortDesc] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdPortDesc] [int] NULL,
[ndSwIP] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdSwIP] [int] NULL,
[ndSwLoc] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdSwLoc] [int] NULL,
[ndSwVendor] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdSwVendor] [int] NULL,
[ndSwSite] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdSwSite] [int] NULL,
[ndSwCust] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdSwCust] [int] NULL,
[ndSwFn] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdSwFn] [int] NULL,
[ndSwScDate] [nvarchar] (384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lockNdSwScDate] [int] NULL
) ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[t_u_ca_agent_prop]
ON [dbo].[ca_agent_prop]
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);
    DECLARE @Tenant [binary](16);
    DECLARE @OwnUUID [binary](16);

    Set @ModCount = 0;   
    Set @SysName = 'ITAM';
    Set @DateInSeconds = datediff(ss, '1/1/1970', getutcdate());
    Set @UpdateUser = 't_u_ca_agent_prop';

    IF (UPDATE(genModel) or UPDATE(genType) or UPDATE(genRegAsset))
    BEGIN        
        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.object_uuid, lnk.own_resource_uuid,
               hw.tenant_id, 1, @UpdateUser,
               @DateInSeconds, @UpdateUser, @DateInSeconds, 0
          from inserted i
          join deleted d  
            on (d.object_uuid = i.object_uuid and
                ((dbo.is_different(d.genModel, i.genModel) = 1) or
                 (dbo.is_different(d.genType, i.genType) = 1) or
                 (dbo.is_different(d.genRegAsset, i.genRegAsset) = 1)))
          join arg_reconcile_links lnk
            on d.object_uuid = lnk.dis_hw_uuid
          join ca_discovered_hardware hw
            on d.object_uuid = hw.dis_hw_uuid
    END

    IF (UPDATE(genOS) or UPDATE(genTotMemory) or UPDATE(genTotDisk) or
    UPDATE(genNumProcs) or UPDATE(genProcSpeed) or UPDATE(genProcType))
    BEGIN
            update ca_discovered_hardware
        set discovery_changes_switch = 1,
            last_update_user = @UpdateUser,
            last_update_date = @DateInSeconds
        where discovery_changes_switch <> 1
          and dis_hw_uuid in
         (select i.object_uuid
            from inserted i
            join deleted d
              on (d.object_uuid = i.object_uuid and
                    ((dbo.is_different(d.genOS, i.genOS) = 1) or
                      (dbo.is_different(d.genTotMemory, i.genTotMemory) = 1) or
                      (dbo.is_different(d.genTotDisk, i.genTotDisk) = 1) or
                      (dbo.is_different(d.genNumProcs, i.genNumProcs) = 1) or
                      (dbo.is_different(d.genProcSpeed, i.genProcSpeed) = 1) or
                      (dbo.is_different(d.genProcType, i.genProcType) = 1))))
    END
GO
ALTER TABLE [dbo].[ca_agent_prop] ADD CONSTRAINT [XPKca_agent_prop] PRIMARY KEY CLUSTERED ([object_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_ca_agent_prop12] ON [dbo].[ca_agent_prop] ([ndMAC]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_agent_prop] ADD CONSTRAINT [FKca_agent_prop_1] FOREIGN KEY ([object_uuid]) REFERENCES [dbo].[ca_agent] ([object_uuid]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ca_agent_prop] ADD CONSTRAINT [FKca_agent_prop_2] FOREIGN KEY ([domain_uuid]) REFERENCES [dbo].[ca_n_tier] ([domain_uuid])
GO
GRANT SELECT ON  [dbo].[ca_agent_prop] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_agent_prop] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_agent_prop] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_agent_prop] TO [ca_itrm_group]
GO