CREATE TABLE [dbo].[al_norm_system_model]
(
[uuid] [binary] (16) NOT NULL,
[collected_value] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[content_uuid] [binary] (16) NOT NULL,
[creation_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] NULL,
[last_update_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NOT NULL CONSTRAINT [DF__AL_NORM_S__VERSI__36470DEF] DEFAULT ('0 '),
[tenant] [binary] (16) NULL,
[collected_manufacturer] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[collected_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
CREATE trigger [t_cd_al_norm_system_model] on [dbo].[al_norm_system_model]
after delete, insert
as
BEGIN
    
    DECLARE @SysName varchar(10);    
    DECLARE @DateInSeconds int;
    DECLARE @UpdateUser varchar(30);
    DECLARE @tenant binary(16);

    SET @SysName = 'ITAM';
    SET @DateInSeconds = datediff(ss, '1/1/1970', getutcdate());
    SET @UpdateUser = 't_cd_al_norm_system_model';
    IF EXISTS ( SELECT 1 FROM deleted)
    BEGIN
    -- handle all deleted records: create a modification record for each tenant found in arg_reconcile_task.
    insert into arg_reconcile_modification(
                        sys_name,
                        reconcile_action,
                        object_uuid,
                        discovery_asset_name,
                        tenant,
                        creation_user,
                        creation_date,
                        last_update_user,
                        last_update_date,
                        version_number)
                select  @SysName,
                        '10',
                        del.content_uuid,
                        del.collected_value,
                        arg_reconcile_task.tenant,
                        @UpdateUser,
                        @DateInSeconds,
                        @UpdateUser,
                        @DateInSeconds,
                        0                     
                        from (select content_uuid, collected_value from deleted inner join ca_model_def cmd on cmd.model_uuid = deleted.content_uuid) del, arg_reconcile_task where (arg_reconcile_task.is_deleted = '0');
    END
    ELSE
    BEGIN
        -- handle all inserted records: create a modification record for each tenant found in arg_reconcile_task.
        insert into arg_reconcile_modification(
                        sys_name,
                        reconcile_action,
                        object_uuid,
                        discovery_asset_name,
                        tenant,
                        creation_user,
                        creation_date,
                        last_update_user,
                        last_update_date,
                        version_number)
                select  @SysName,
                        '10',
                        ins.content_uuid,
                        ins.collected_value,
                        arg_reconcile_task.tenant,
                        @UpdateUser,
                        @DateInSeconds,
                        @UpdateUser,
                        @DateInSeconds,
                        0                     
                        from (select content_uuid, collected_value from inserted inner join ca_model_def cmd on cmd.model_uuid = inserted.content_uuid) ins, arg_reconcile_task where (arg_reconcile_task.is_deleted = '0');
    END    
END
GO
ALTER TABLE [dbo].[al_norm_system_model] ADD CONSTRAINT [pk_al_norm_system_model] PRIMARY KEY CLUSTERED ([uuid]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [idx_al_norm_system_model79] ON [dbo].[al_norm_system_model] ([uuid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_norm_system_model] ADD CONSTRAINT [AL_NORM_SYSMODEL_FK01] FOREIGN KEY ([content_uuid]) REFERENCES [dbo].[ca_model_def] ([model_uuid])
GO
ALTER TABLE [dbo].[al_norm_system_model] ADD CONSTRAINT [FK_AL_NORM_SYSTEM_MODEL_TENANT] FOREIGN KEY ([tenant]) REFERENCES [dbo].[ca_tenant] ([id])
GO