CREATE TABLE [dbo].[al_norm_os]
(
[uuid] [binary] (16) NOT NULL,
[collected_value] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[content_id] [int] 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_O__VERSI__2D12A970] DEFAULT ('0 '),
[tenant] [binary] (16) NULL
) ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[t_cd_al_norm_os] ON [dbo].[al_norm_os]
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_os';
    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_id,
                        discovery_asset_name,
                        tenant,
                        creation_user,
                        creation_date,
                        last_update_user,
                        last_update_date,
                        version_number)
                select  @SysName,
                        '9',
                        del.content_id,
                        del.collected_value,
                        arg_reconcile_task.tenant,
                        @UpdateUser,
                        @DateInSeconds,
                        @UpdateUser,
                        @DateInSeconds,
                        0                     
                        from (select content_id, collected_value from deleted inner join ca_resource_operating_system cros on cros.id = deleted.content_id) 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_id,
                        discovery_asset_name,
                        tenant,
                        creation_user,
                        creation_date,
                        last_update_user,
                        last_update_date,
                        version_number)
                select  @SysName,
                        '9',
                        ins.content_id,
                        ins.collected_value,
                        arg_reconcile_task.tenant,
                        @UpdateUser,
                        @DateInSeconds,
                        @UpdateUser,
                        @DateInSeconds,
                        0                     
                        from (select content_id, collected_value from inserted inner join ca_resource_operating_system cros on cros.id = inserted.content_id) ins, arg_reconcile_task where (arg_reconcile_task.is_deleted = '0');
    END    
END
GO
ALTER TABLE [dbo].[al_norm_os] ADD CONSTRAINT [pk_al_norm_os] PRIMARY KEY CLUSTERED ([uuid]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [idx_al_norm_os84] ON [dbo].[al_norm_os] ([uuid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_norm_os] ADD CONSTRAINT [AL_NORM_OS_FK01] FOREIGN KEY ([content_id]) REFERENCES [dbo].[ca_resource_operating_system] ([id])
GO
ALTER TABLE [dbo].[al_norm_os] ADD CONSTRAINT [FK_AL_NORM_OS_TENANT] FOREIGN KEY ([tenant]) REFERENCES [dbo].[ca_tenant] ([id])
GO