CREATE TABLE [dbo].[al_product_def]
(
[product_uuid] [binary] (16) NOT NULL,
[product_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[inactive] [int] NULL CONSTRAINT [DF__al_produc__inact__600A9F96] DEFAULT ('0'),
[product_type] [int] NULL,
[family_uuid] [binary] (16) NOT NULL,
[version_label] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[major_version] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[minor_version] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[edition_uuid] [binary] (16) NULL,
[language_uuid] [binary] (16) NULL,
[platform_uuid] [binary] (16) NULL,
[release_date] [datetime] NULL,
[sunset_date] [datetime] NULL,
[unspsc_uuid] [binary] (16) NULL,
[class_uuid] [binary] (16) NOT NULL CONSTRAINT [DF__al_produc__class__60FEC3CF] DEFAULT (CONVERT([binary](16),(0),(0))),
[supported] [int] NOT NULL CONSTRAINT [DF__al_produc__suppo__61F2E808] DEFAULT ('1'),
[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_produc__versi__62E70C41] DEFAULT ('0'),
[source_type_id] [int] NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER al_c_product_tr ON al_product_def
AFTER INSERT
AS
BEGIN
    DECLARE @productuuid binary(16)
    DECLARE @creationuser nvarchar(255)
    DECLARE @creationdate int
    DECLARE @lastupdateuser nvarchar(255)
    DECLARE @lastupdatedate int

    SELECT
    @productuuid    = product_uuid,
    @creationuser    = creation_user,
    @creationdate    = creation_date,
    @lastupdateuser = last_update_user,
    @lastupdatedate = last_update_date
    FROM inserted

    INSERT INTO al_product_def_flags(product_uuid, missing_sw_def_link, suite_or_bundle, creation_user, creation_date, last_update_user, last_update_date, version_number )VALUES(@productuuid,1,0,@creationuser,@creationdate,@lastupdateuser,@lastupdatedate, 0)

END

GO
CREATE TRIGGER al_d_product_tr ON al_product_def
AFTER DELETE
AS
BEGIN
    DECLARE @productuuid binary(16)

    SET @productuuid = (Select product_uuid from deleted)
    DELETE FROM al_product_def_flags
    WHERE product_uuid = @productuuid

END

GO
ALTER TABLE [dbo].[al_product_def] ADD CONSTRAINT [PK_al_product_def] PRIMARY KEY CLUSTERED ([product_uuid]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [al_product_def_idx_01] ON [dbo].[al_product_def] ([product_name]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_product_def] ADD CONSTRAINT [al_product_def_fk02] FOREIGN KEY ([product_type]) REFERENCES [dbo].[al_product_type] ([id])
GO
ALTER TABLE [dbo].[al_product_def] ADD CONSTRAINT [FK_al_prod_def_al_prod_family] FOREIGN KEY ([family_uuid]) REFERENCES [dbo].[al_product_family] ([family_uuid])
GO
ALTER TABLE [dbo].[al_product_def] ADD CONSTRAINT [FK_al_product_def_class] FOREIGN KEY ([class_uuid]) REFERENCES [dbo].[al_product_class] ([class_uuid])
GO
ALTER TABLE [dbo].[al_product_def] ADD CONSTRAINT [FK_al_product_def_edition] FOREIGN KEY ([edition_uuid]) REFERENCES [dbo].[al_edition] ([edition_uuid])
GO
ALTER TABLE [dbo].[al_product_def] ADD CONSTRAINT [FK_al_product_def_platform] FOREIGN KEY ([platform_uuid]) REFERENCES [dbo].[al_platform] ([platform_uuid])
GO
ALTER TABLE [dbo].[al_product_def] ADD CONSTRAINT [FK_al_product_def_source_type] FOREIGN KEY ([source_type_id]) REFERENCES [dbo].[ca_source_type] ([source_type_id])
GO
ALTER TABLE [dbo].[al_product_def] ADD CONSTRAINT [FK_al_product_def_suppt] FOREIGN KEY ([supported]) REFERENCES [dbo].[al_product_support_def] ([id])
GO
ALTER TABLE [dbo].[al_product_def] ADD CONSTRAINT [FK_al_product_def_unspsc] FOREIGN KEY ([unspsc_uuid]) REFERENCES [dbo].[al_unspsc_code] ([unspsc_uuid])
GO
GRANT SELECT ON  [dbo].[al_product_def] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_product_def] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_product_def] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_product_def] TO [swcmadmin]
GRANT SELECT ON  [dbo].[al_product_def] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[al_product_def] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[al_product_def] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[al_product_def] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[al_product_def] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[al_product_def] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[al_product_def] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[al_product_def] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[al_product_def] TO [uapmreporting_group]
GO