CREATE TABLE [dbo].[al_license_metric]
(
[license_metric_id] [int] NOT NULL,
[license_id] [int] NOT NULL,
[license_count] [int] NULL,
[enabled] [smallint] NULL,
[maintenance] [smallint] NULL,
[source_flag] [smallint] NULL,
[multiplier] [smallint] NULL,
[based_on_uuid] [binary] (16) NULL,
[install_type_def_id] [int] NULL,
[based_on_def_id] [int] NULL,
[description] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[supported] [int] NOT NULL CONSTRAINT [DF__al_licens__suppo__13BF4DC6] DEFAULT ('1'),
[unsupported_license_metric_id] [int] NULL,
[creation_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] 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_licens__versi__14B371FF] DEFAULT ('0')
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER al_c_license_metric
ON  dbo.al_license_metric
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    declare @license_id int
    declare @processable smallint
    declare @count int
    declare @license_metric_id int
    declare @block_id int
    declare @product_uuid binary(16)
    declare @maintenance smallint
    declare @included smallint
    declare @maintenance_terminated smallint
    declare @enabled smallint

    select @license_id = license_id,
           @license_metric_id = license_metric_id,
           @maintenance = maintenance
    from INSERTED
    
--insert into al_a_log (text) values('insert')

    select @included = include
    from al_product_maintenance_info
    where license_id = @license_id

    select @count = count(*) from al_license_metric where license_id = @license_id

    if @count = 1
        update al_license_flags set includes_metrics = 1 where license_id = @license_id

    if @maintenance = 1
    begin
        if @included = 0 or (@included = 1 and @maintenance_terminated = 1)
            set @enabled = 0
    
        if @included = 1 and @maintenance_terminated = 0
            set @enabled = 1             
    end
    else
        set @enabled = 1


    update al_license_metric set enabled = @enabled where license_id = @license_id and license_metric_id = @license_metric_id


end

GO
CREATE TRIGGER al_d_license_metric
ON  dbo.al_license_metric
   AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    declare @license_id int
    declare @processable smallint
    declare @count int
    declare @license_metric_id int
    declare @block_id int
    declare @product_uuid binary(16)

    select @license_id = license_id,
           @license_metric_id = license_metric_id
    from DELETED
        
    -- notify flags that license no longer contains metrics
    -- 8/22/07 DD Added check to restore missing_license_count as we don't know if simple licenses alway have license counts.        
    select @count = count(*) from al_license_metric where license_id = @license_id

    if @count = 0
        update al_license_flags set includes_metrics = 0 where license_id = @license_id
    
end

GO
CREATE TRIGGER al_u_license_metric
   ON  dbo.al_license_metric
   INSTEAD OF UPDATE
AS
BEGIN
    declare @included smallint
    declare @maintenance_terminated smallint
    declare @processable smallint
    declare @count int
    declare @block_id int
    declare @product_uuid binary(16)

    declare @license_id int
    declare @license_metric_id  int
    declare @license_count int
    declare @enabled smallint
    declare @maintenance  smallint
    declare @source_flag smallint
    declare @creation_user nvarchar(255)
    declare @creation_date int
    declare @last_update_user nvarchar(255)
    declare @last_update_date int
    declare @version_number int
    declare @multiplier smallint
    declare @based_on_uuid binary(16)
    declare @install_type_def_id int
    declare @based_on_def_id int
    declare @description nvarchar(255)
    declare @old_enabled smallint
    declare @effective_future smallint

    SET NOCOUNT ON;

    select @old_enabled = enabled from deleted

    select @license_id = license_id,
           @license_metric_id = license_metric_id,
           @license_count = license_count,    
           @enabled = enabled,
           @maintenance = maintenance,
           @source_flag = source_flag,
           @creation_user = creation_user,
           @creation_date = creation_date,
           @last_update_user = last_update_user,
           @last_update_date = last_update_date,
           @version_number = version_number,
           @multiplier = multiplier,
           @based_on_uuid = based_on_uuid,
           @install_type_def_id = install_type_def_id,
           @based_on_def_id = based_on_def_id,
           @description = description
    from inserted

        
    select @included = include
    from al_product_maintenance_info
    where license_id = @license_id

    select @maintenance_terminated = maintenance_terminated, @effective_future = maintenance_effective_future
    from al_license_flags
    where license_id = @license_id
    
    if @maintenance = 1
    begin
        if @included = 0 or (@included = 1 and @maintenance_terminated = 1 or @effective_future =1)
            set @enabled = 0
    
        if @included = 1 and @maintenance_terminated = 0
            set @enabled = 1             
    end
    else
        set @enabled = 1
    
--    if @old_enabled = 1 and @enabled =0
--        exec unlink_license_metric @license_id, @license_metric_id, null
--
--    if @old_enabled = 0 and @enabled =1
--        exec link_license_metric @license_id, @license_metric_id, null

    update al_license_metric
    set    license_id = @license_id,
        license_metric_id=  @license_metric_id ,
        license_count= @license_count,    
            enabled = @enabled,
        maintenance =   @maintenance,
        source_flag= @source_flag,
        creation_user = @creation_user,
        creation_date =  @creation_date,
        last_update_user =  @last_update_user,
        last_update_date=  @last_update_date,
        version_number = @version_number,
        multiplier = @multiplier,
        based_on_uuid= @based_on_uuid,
        install_type_def_id= @install_type_def_id,
        based_on_def_id =@based_on_def_id,
        description =@description
    where license_id = @license_id and license_metric_id = @license_metric_id

    
END

GO
ALTER TABLE [dbo].[al_license_metric] ADD CONSTRAINT [PK_al_license_metric] PRIMARY KEY CLUSTERED ([license_metric_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_license_metric] ADD CONSTRAINT [al_license_metric_fk01] FOREIGN KEY ([license_id]) REFERENCES [dbo].[al_license] ([license_id])
GO
ALTER TABLE [dbo].[al_license_metric] ADD CONSTRAINT [al_license_metric_fk02] FOREIGN KEY ([install_type_def_id]) REFERENCES [dbo].[al_install_type_def] ([install_type_def_id])
GO
ALTER TABLE [dbo].[al_license_metric] ADD CONSTRAINT [al_license_metric_fk03] FOREIGN KEY ([based_on_def_id]) REFERENCES [dbo].[al_based_on_def] ([based_on_def_id])
GO
ALTER TABLE [dbo].[al_license_metric] ADD CONSTRAINT [al_license_metric_fk04] FOREIGN KEY ([unsupported_license_metric_id]) REFERENCES [dbo].[al_unsupported_metric_def] ([id])
GO
GRANT SELECT ON  [dbo].[al_license_metric] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_license_metric] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_license_metric] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_license_metric] TO [swcmadmin]
GRANT SELECT ON  [dbo].[al_license_metric] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[al_license_metric] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[al_license_metric] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[al_license_metric] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[al_license_metric] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[al_license_metric] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[al_license_metric] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[al_license_metric] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[al_license_metric] TO [uapmreporting_group]
GO