CREATE TABLE [dbo].[al_license_block]
(
[block_id] [int] NOT NULL,
[finite] [smallint] NULL,
[block_matrix] [image] NULL,
[license_count_array] [image] NULL,
[evaluate] [smallint] NULL,
[solution_matrix] [image] NULL,
[compliance] [smallint] NULL,
[product_criteria_indices] [image] NULL,
[processing] [smallint] NOT NULL CONSTRAINT [DF__al_licens__proce__79FF7BC3] DEFAULT ('0'),
[uses_remaining] [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__7AF39FFC] DEFAULT ('0')
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER dbo.al_c_new_prikey_lic_block
   ON  dbo.al_license_block
   INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @unique_id int
    DECLARE    @evaluate int
    DECLARE @finite smallint
    DECLARE @creationuser nvarchar(255)
    DECLARE @creationdate int
    DECLARE @lastupdateuser nvarchar(255)
    DECLARE @lastupdatedate int
    DECLARE @versionnumber int
    DECLARE @block_id int

    select @lastupdatedate = (datediff(ss, '1/1/1970', getutcdate()))     
    set @creationdate = @lastupdatedate


    SELECT  @evaluate = evaluate,
            @finite = finite,
            @creationuser = creation_user,
            @lastupdateuser = last_update_user,
            @block_id = block_id
    FROM INSERTED

    insert into al_license_block (block_id, evaluate, finite, processing, creation_user, creation_date, last_update_user, last_update_date, version_number)
                                                values(@block_id, @evaluate, @finite, 0, @creationuser, @creationdate, @lastupdateuser, @lastupdatedate, 0)
END

GO
CREATE TRIGGER al_u_license_block
   ON  dbo.al_license_block
   INSTEAD OF UPDATE
AS
   BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        --SET NOCOUNT ON;

   IF @@ROWCOUNT = 1
   BEGIN

    SET NOCOUNT ON;

    DECLARE @unique_id int
    DECLARE    @evaluate int
    DECLARE @finite smallint
    DECLARE @creationuser nvarchar(255)
    DECLARE @creationdate int
    DECLARE @lastupdateuser nvarchar(255)
    DECLARE @lastupdatedate int
    DECLARE @versionnumber int
    DECLARE @block_id int
    declare @processing int
    declare @old_evaluate int
    declare @uses_remaining int

    select @lastupdatedate = (datediff(ss, '1/1/1970', getutcdate()))     
    
    SELECT  @evaluate = evaluate,
            @lastupdateuser = last_update_user,
            @block_id = block_id,
            @processing = processing,
            @versionnumber = version_number,
            @finite = finite,
            @uses_remaining = uses_remaining
    FROM INSERTED

    SELECT  @old_evaluate = evaluate
    FROM DELETED
    
    if @versionnumber is not null
        set @versionnumber = @versionnumber + 1

    -- signal process must rollback changes and start over for re-evaluation.    
    if @old_evaluate > 1 and @evaluate = 1 and @processing = 1
        set @processing = 2

    -- if any process sets evaluate back to 0, that means it cannot be processing.
    if @evaluate = 0
        set @processing = 0

    update al_license_block
    set evaluate = @evaluate,
        finite = @finite,
        processing = @processing,
        uses_remaining = @uses_remaining,
        last_update_user = @lastupdateuser,
        version_number = @versionnumber,
        last_update_date = @lastupdatedate,
        block_matrix=b.block_matrix,
        solution_matrix=b.solution_matrix,
        license_count_array=b.license_count_array,
        compliance=b.compliance,
        product_criteria_indices=b.product_criteria_indices
    from al_license_block a join INSERTED b on a.block_id=b.block_id
    where a.block_id = @block_id
   END
   ELSE
   BEGIN
    update al_license_block
    set processing=i.processing, finite=i.finite, block_matrix=i.block_matrix,
        license_count_array=i.license_count_array, evaluate=i.evaluate,
        solution_matrix=i.solution_matrix, compliance=i.compliance,
        product_criteria_indices=i.product_criteria_indices, uses_remaining=i.uses_remaining,
        creation_user=i.creation_user, creation_date=i.creation_date,
        last_update_user=i.last_update_user, last_update_date=i.last_update_date,
        version_number=
            CASE
                WHEN i.version_number is null
                THEN 1
                ELSE
                i.version_number+1
            END
    from inserted i
    where al_license_block.block_id=i.block_id
   END

   END


GO
ALTER TABLE [dbo].[al_license_block] ADD CONSTRAINT [PK_al_license_block] PRIMARY KEY CLUSTERED ([block_id]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[al_license_block] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_license_block] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_license_block] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_license_block] TO [swcmadmin]
GRANT SELECT ON  [dbo].[al_license_block] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[al_license_block] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[al_license_block] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[al_license_block] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[al_license_block] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[al_license_block] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[al_license_block] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[al_license_block] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[al_license_block] TO [uapmreporting_group]
GO