CREATE TABLE [dbo].[al_metric_covered_product]
(
[license_metric_id] [int] NOT NULL,
[product_rights_id] [int] NOT NULL,
[selected] [smallint] 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_METRIC__VERSI__14270015] DEFAULT ('0 ')
) ON [PRIMARY]

GO
CREATE TRIGGER al_c_met_covrd_prod
ON  dbo.al_metric_covered_product
   AFTER INSERT
AS
begin

    declare @license_id int
    declare @product_uuid binary(16)
    declare @processable smallint
    declare @license_metric_id int
    declare @product_rights_id int
    declare @maintenance_terminated smallint
    declare @maintenance smallint
    declare @maintenance_future smallint
    declare @included smallint

    select  @license_metric_id = license_metric_id, @product_rights_id = product_rights_id
    from inserted

    select @license_id = license_id, @maintenance = al_license_metric.maintenance
    from al_license_metric, al_metric_covered_product
    where al_license_metric.license_metric_id = al_metric_covered_product.license_metric_id and al_metric_covered_product.license_metric_id = @license_metric_id
    
    select @product_uuid = product_uuid
    from al_metric_covered_product, al_product_rights
    where al_product_rights.product_rights_id = al_metric_covered_product.product_rights_id and al_metric_covered_product.product_rights_id = @product_rights_id

    select @maintenance_terminated = maintenance_terminated, @maintenance_future = maintenance_effective_future
    from al_license_flags
    where license_id = @license_id
    

    -- We have to determine processable without the missing_products_covered flag.
    -- if we can't process the license because other flags are set, then we don't want to block the license.
    -- if we determine that the license is in fact processable...go ahead and block the license.
    -- 12/13/07 JMP:  Removed missing_license_count and added includes_metrics to the calculation of @processable.

    SELECT  @processable = ~ (effective_future * -1 | al_license.inactive * - 1 | terminated * - 1 | (includes_metrics ^ 1) * -1) * - 1
    FROM al_license, al_license_flags
    WHERE al_license.license_id = al_license_flags.license_id and al_license.license_id = @license_id
    

    select @included = include
    from al_product_maintenance_info
    where license_id = @license_id
    
    if @maintenance = 1
        begin
            if @included = 0 or (@included = 1 and @maintenance_terminated = 1 or @maintenance_future =1)
                set @processable = 0
        end
    

    --insert into al_a_log (text) values('ADD TRIGGER:' + CONVERT(VARCHAR(12), @license_id) + ':'+ CONVERT(VARCHAR(12),@license_metric_id) + ':' + CONVERT(VARCHAR(40),@product_uuid))

    if @processable = 1
        exec al_link_prod_to_lic_metric @license_id, @license_metric_id, @product_uuid, 0    

end
GO
CREATE TRIGGER al_d_met_covrd_prod
ON  dbo.al_metric_covered_product
   AFTER DELETE
  AS
  begin

    declare @license_id int
    declare @product_uuid binary(16)
    declare @processable smallint
    declare @license_metric_id int
    declare @product_rights_id int
    declare @count int
    declare @enabled smallint

    select @count = count(*) from deleted
    select @count = count(*) from inserted
    
    select @license_metric_id = license_metric_id, @product_rights_id = product_rights_id
    from DELETED

    
    select @product_uuid = product_uuid, @license_id = license_id
    from  al_product_rights
    where  al_product_rights.product_rights_id = @product_rights_id
    

    select @enabled = enabled from al_license_metric where license_id = @license_id and license_metric_id = @license_metric_id

    if @enabled = 1
        exec al_unlink_prod_to_lic_metric @license_id, @license_metric_id, @product_uuid, 0    

  end
GO
ALTER TABLE [dbo].[al_metric_covered_product] ADD CONSTRAINT [pk_al_metric_covered_product] PRIMARY KEY CLUSTERED ([license_metric_id], [product_rights_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif1al_metric_covered_product] ON [dbo].[al_metric_covered_product] ([license_metric_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif2al_metric_covered_product] ON [dbo].[al_metric_covered_product] ([product_rights_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_metric_covered_product] ADD CONSTRAINT [AL_METRIC_COVERED_PRODUCT_FK01] FOREIGN KEY ([license_metric_id]) REFERENCES [dbo].[al_license_metric] ([license_metric_id])
GO
ALTER TABLE [dbo].[al_metric_covered_product] ADD CONSTRAINT [AL_METRIC_COVERED_PRODUCT_FK02] FOREIGN KEY ([product_rights_id]) REFERENCES [dbo].[al_product_rights] ([product_rights_id])
GO
GRANT SELECT ON  [dbo].[al_metric_covered_product] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_metric_covered_product] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_metric_covered_product] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_metric_covered_product] TO [swcmadmin]
GRANT SELECT ON  [dbo].[al_metric_covered_product] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[al_metric_covered_product] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[al_metric_covered_product] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[al_metric_covered_product] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[al_metric_covered_product] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[al_metric_covered_product] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[al_metric_covered_product] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[al_metric_covered_product] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[al_metric_covered_product] TO [uapmreporting_group]
GO