CREATE TABLE [dbo].[ols_area_def]
(
[area_uuid] [binary] (16) NOT NULL,
[area_id] [int] NOT NULL,
[label] [nvarchar] (255) COLLATE Latin1_General_CI_AI NOT NULL,
[description] [nvarchar] (255) COLLATE Latin1_General_CI_AI NULL,
[creation_user] [nvarchar] (255) COLLATE Latin1_General_CS_AS_KS_WS NULL,
[creation_date] [int] NULL,
[last_update_user] [nvarchar] (255) COLLATE Latin1_General_CS_AS_KS_WS NULL,
[last_update_date] [int] NULL
) ON [PRIMARY]
GO
/*
****************************************************
trigger for deleting a area definition
    step 1) delete the object permission
    step 2) re-calculate&set new area mask
*/
CREATE trigger [dbo].rule_d_so_removed_area
  on ols_area_def
  after delete

as
begin
    declare @area_uuid binary(16);
    declare @area_id int;
    set nocount on;

    DECLARE cur_rsoa CURSOR
        FOR select area_uuid, area_id from deleted

    OPEN cur_rsoa
    FETCH NEXT FROM cur_rsoa INTO @area_uuid, @area_id
    WHILE @@FETCH_STATUS = 0
    BEGIN

        execute  proc_d_so_removed_object @area_uuid

        -- update area permissons because a raea was deleted/unused now
        execute ols_sp_area_changed 0, @area_id;

        FETCH NEXT FROM cur_rsoa INTO @area_uuid, @area_id;

    END

    CLOSE cur_rsoa
    DEALLOCATE cur_rsoa

    -- apply area mask
    execute ols_sp_applyAreaMask @obj_uuid=null;
    
    

end;
GO
CREATE trigger [dbo].rule_i_new_so_area
on ols_area_def
after insert
as
/*
*********************************************************
*********************************************************
* Procedures and rules to manage area objects
*********************************************************
*********************************************************
*/




/*
****************************************************
trigger for creating a new area
    step 1) create the object permission
    step 2) calculate&set new area mask and
*/

begin
    declare @_obj_uuid     binary(16);
    declare @_clsid integer;
    declare @_uri nvarchar(255);
    declare @area_id int;

    set nocount on;
    select @_clsid = 3;

    DECLARE cur_nsoa CURSOR
        FOR select area_uuid, creation_user, area_id  from inserted

    OPEN cur_nsoa
    FETCH NEXT FROM cur_nsoa INTO @_obj_uuid, @_uri, @area_id    -- get first

    WHILE @@FETCH_STATUS = 0
    BEGIN

        execute  proc_i_new_so_object @_obj_uuid, @_clsid, @_uri;

        -- update area permissions because a new area was created
        execute ols_sp_area_changed 1, @area_id;

        FETCH NEXT FROM cur_nsoa INTO @_obj_uuid, @_uri, @area_id;      -- get next

    END

    CLOSE cur_nsoa
    DEALLOCATE cur_nsoa

    -- apply area mask
    execute ols_sp_applyAreaMask @obj_uuid=null;

end;
GO
ALTER TABLE [dbo].[ols_area_def] ADD CONSTRAINT [XPK_ols_area_def] PRIMARY KEY CLUSTERED ([area_uuid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ols_area_def] ADD CONSTRAINT [UQ__ols_area_def__5C842775] UNIQUE NONCLUSTERED ([area_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ols_area_def] ADD CONSTRAINT [UQ__ols_area_def__5B90033C] UNIQUE NONCLUSTERED ([label]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[ols_area_def] TO [ams_group]
GRANT SELECT ON  [dbo].[ols_area_def] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ols_area_def] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ols_area_def] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ols_area_def] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ols_area_def] TO [ca_itrm_group_ams]
GO