CREATE TABLE [dbo].[al_norm_manufacturer]
(
[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_M__VERSI__3CBF0154] DEFAULT ('0 '),
[tenant] [binary] (16) NULL,
[deleted] [int] NULL CONSTRAINT [DF__al_norm_m__delet__4A6C9D49] DEFAULT ('0'),
[added] [int] NULL CONSTRAINT [DF__al_norm_m__added__4B60C182] DEFAULT ('0'),
[source_type_id] [int] NOT NULL CONSTRAINT [DF__al_norm_m__sourc__4C54E5BB] DEFAULT ('0')
) ON [PRIMARY]

GO
CREATE trigger [t_cd_al_norm_manufacturer] on [dbo].[al_norm_manufacturer]    
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_manufacturer';
    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,
                        '11',
                        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_company cc on cc.company_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,
                        '11',
                        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_company cc on cc.company_uuid = inserted.content_uuid) ins, arg_reconcile_task where (arg_reconcile_task.is_deleted = '0');
    END    
END
GO
ALTER TABLE [dbo].[al_norm_manufacturer] ADD CONSTRAINT [pk_al_norm_manufacturer] PRIMARY KEY CLUSTERED ([uuid]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [idx_al_norm_manufacturer8] ON [dbo].[al_norm_manufacturer] ([uuid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_norm_manufacturer] ADD CONSTRAINT [AL_NORM_MANUFACTURER_FK01] FOREIGN KEY ([content_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[al_norm_manufacturer] ADD CONSTRAINT [FK_AL_NORM_MANUFACTURER_TENANT] FOREIGN KEY ([tenant]) REFERENCES [dbo].[ca_tenant] ([id])
GO