CREATE TABLE [dbo].[al_link_product_def]
(
[product_uuid] [binary] (16) NOT NULL,
[secondary_product_uuid] [binary] (16) NOT 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__AL_LINK_P__VERSI__3592E0D8] DEFAULT ('0 '),
[source_type_id] [int] NOT NULL
) ON [PRIMARY]

GO
CREATE TRIGGER [al_c_link_prod_tr] ON [dbo].[al_link_product_def]
AFTER INSERT
AS
BEGIN
    DECLARE @recs int
    DECLARE @productuuid binary(16)
    DECLARE @blockid int
    DECLARE @secondary_product_uuid binary(16)

    SET @productuuid = (select product_uuid from inserted)
    SET @secondary_product_uuid = (select secondary_product_uuid from inserted)

    SET @recs =
    (
    SELECT COUNT(0)
    FROM al_link_product_def
    WHERE  product_uuid = @productuuid
    )

    IF (@recs = 1)
    BEGIN
        UPDATE al_product_def_flags
        SET suite_or_bundle = 1
        WHERE  product_uuid = @productuuid
    END
    
    
    -- Update evaluate flag for license block of the current product.
        
    SELECT @blockid = block_id
      FROM al_link_product_block
     WHERE product_uuid = @secondary_product_uuid
            
    -- is blocked - has records in al_link_product_block
    IF (@blockid is not null)
    BEGIN
        UPDATE al_license_block
        SET evaluate = 1
        WHERE block_id = @blockid
        AND (evaluate = 0 or evaluate = 2)
    END
    
    
    -- Update evaluate flag for license blocks of any/all associated suite/bundle parent products.

    UPDATE al_license_block
       SET evaluate = 1
     WHERE (evaluate = 0 or evaluate = 2)
       AND block_id IN (SELECT lb.block_id
                  FROM al_link_product_block lb
                INNER JOIN al_link_product_def pd ON pd.product_uuid = lb.product_uuid
                 WHERE pd.secondary_product_uuid = @secondary_product_uuid)
                    
END
GO
CREATE TRIGGER [al_d_link_prod_tr] ON [dbo].[al_link_product_def]
AFTER DELETE
AS
BEGIN
    DECLARE @recs int
    DECLARE @productuuid binary(16)
    DECLARE @blockid int
    DECLARE @secondary_product_uuid binary(16)
    DECLARE deleted_products cursor for SELECT product_uuid, secondary_product_uuid from deleted
           
    open deleted_products
    fetch next from deleted_products into @productuuid, @secondary_product_uuid
        
    while @@fetch_status = 0
        BEGIN
        SET @recs = (select count(*) from al_link_product_def where product_uuid = @productuuid)
    
        IF (@recs = 0)
        BEGIN
            UPDATE al_product_def_flags
            SET suite_or_bundle = 0
            WHERE  product_uuid = @productuuid
        END
        
        -- Update evaluate flag for license block of the current product.
                
        SELECT @blockid = block_id
          FROM al_link_product_block
         WHERE product_uuid = @secondary_product_uuid
                    
        -- is blocked - has records in al_link_product_block
        IF (@blockid is not null)
        BEGIN
            UPDATE al_license_block
            SET evaluate = 1
            WHERE block_id = @blockid
            AND (evaluate = 0 or evaluate = 2)
        END
        
        
        -- Update evaluate flag for license blocks of any/all associated suite/bundle parent products.
    
        UPDATE al_license_block
           SET evaluate = 1
         WHERE (evaluate = 0 or evaluate = 2)
           AND block_id IN (SELECT lb.block_id
                      FROM al_link_product_block lb
                    INNER JOIN al_link_product_def pd ON pd.product_uuid = lb.product_uuid
                         WHERE pd.secondary_product_uuid = @secondary_product_uuid)
        
        fetch next from deleted_products into @productuuid, @secondary_product_uuid
    END
            
    close deleted_products
    deallocate deleted_products     
                    
END
GO
ALTER TABLE [dbo].[al_link_product_def] ADD CONSTRAINT [pk_al_link_product_def] PRIMARY KEY CLUSTERED ([product_uuid], [secondary_product_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif1al_link_product_def] ON [dbo].[al_link_product_def] ([product_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif2al_link_product_def] ON [dbo].[al_link_product_def] ([secondary_product_uuid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_link_product_def] ADD CONSTRAINT [AL_LINK_PRODUCT_DEF_FK01] FOREIGN KEY ([product_uuid]) REFERENCES [dbo].[al_product_def] ([product_uuid])
GO
ALTER TABLE [dbo].[al_link_product_def] ADD CONSTRAINT [AL_LINK_PRODUCT_DEF_FK02] FOREIGN KEY ([secondary_product_uuid]) REFERENCES [dbo].[al_product_def] ([product_uuid])
GO
ALTER TABLE [dbo].[al_link_product_def] ADD CONSTRAINT [al_link_product_def_fk03] FOREIGN KEY ([source_type_id]) REFERENCES [dbo].[ca_source_type] ([source_type_id])
GO
GRANT SELECT ON  [dbo].[al_link_product_def] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_link_product_def] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_link_product_def] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_link_product_def] TO [swcmadmin]
GRANT SELECT ON  [dbo].[al_link_product_def] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[al_link_product_def] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[al_link_product_def] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[al_link_product_def] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[al_link_product_def] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[al_link_product_def] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[al_link_product_def] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[al_link_product_def] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[al_link_product_def] TO [uapmreporting_group]
GO