CREATE TABLE [dbo].[usp_owned_resource]
(
[owned_resource_uuid] [binary] (16) NOT NULL,
[nr_prim_skt_id] [int] NULL,
[nr_pr_id] [int] NULL,
[nr_wrty_st_dt] [int] NULL,
[nr_wrty_end_dt] [int] NULL,
[nr_exp_dt] [int] NULL,
[nr_sla_id] [int] NULL,
[nr_nx_string1] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nr_nx_string2] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nr_nx_string3] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nr_nx_string4] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nr_nx_string5] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nr_nx_string6] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nr_nx_ref_1] [binary] (16) NULL,
[nr_nx_ref_2] [binary] (16) NULL,
[nr_nx_ref_3] [binary] (16) NULL,
[nr_financial_id] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nr_service_type] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nr_argis_id] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nr_bms] [int] NULL,
[nr_bmlabel] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[nr_bm_rep] [int] NULL,
[baseline_uuid] [binary] (16) NULL,
[service_impact] [int] NULL,
[zbusiness_approver_group] [binary] (16) NULL,
[zstakeholders_group] [binary] (16) NULL,
[ztechnical_approver_group] [binary] (16) NULL,
[CREATION_USER] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CREATION_DATE] [int] NULL,
[LAST_UPDATE_USER] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LAST_UPDATE_DATE] [int] NULL,
[VERSION_NUMBER] [int] NULL CONSTRAINT [DF__USP_OWN_RES_VERSION] DEFAULT ('0')
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.ca_tr_del_usp_owned_resource
ON dbo.usp_owned_resource
FOR DELETE AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_usp_owned_resource (
AUDIT_TRAIL_ID,
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
owned_resource_uuid,
nr_prim_skt_id,
nr_pr_id,
nr_wrty_st_dt,
nr_wrty_end_dt,
nr_exp_dt,
nr_sla_id,
nr_nx_string1,
nr_nx_string2,
nr_nx_string3,
nr_nx_string4,
nr_nx_string5,
nr_nx_string6,
nr_nx_ref_1,
nr_nx_ref_2,
nr_nx_ref_3,
nr_financial_id,
nr_service_type,
nr_argis_id,
nr_bms,
nr_bmlabel,
nr_bm_rep,
baseline_uuid,
service_impact,
zbusiness_approver_group,
zstakeholders_group,
ztechnical_approver_group,
CREATION_USER,
CREATION_DATE,
LAST_UPDATE_USER,
LAST_UPDATE_DATE,
VERSION_NUMBER) SELECT
IDENT_CURRENT('aud_ca_owned_resource') +1,
@audit_product,old.last_update_user, 'DELETE', datediff(ss, '1/1/1970', getutcdate()),
old.owned_resource_uuid,
old.nr_prim_skt_id,
old.nr_pr_id,
old.nr_wrty_st_dt,
old.nr_wrty_end_dt,
old.nr_exp_dt,
old.nr_sla_id,
old.nr_nx_string1,
old.nr_nx_string2,
old.nr_nx_string3,
old.nr_nx_string4,
old.nr_nx_string5,
old.nr_nx_string6,
old.nr_nx_ref_1,
old.nr_nx_ref_2,
old.nr_nx_ref_3,
old.nr_financial_id,
old.nr_service_type,
old.nr_argis_id,
old.nr_bms,
old.nr_bmlabel,
old.nr_bm_rep,
old.baseline_uuid,
old.service_impact,
old.zbusiness_approver_group,
old.zstakeholders_group,
old.ztechnical_approver_group,
old.CREATION_USER,
old.CREATION_DATE,
old.LAST_UPDATE_USER,
old.LAST_UPDATE_DATE,
old.VERSION_NUMBER
FROM deleted old
GO
CREATE TRIGGER dbo.ca_tr_ins_usp_owned_resource
ON dbo.usp_owned_resource
FOR INSERT AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_usp_owned_resource (
AUDIT_TRAIL_ID,
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
owned_resource_uuid,
nr_prim_skt_id,
nr_pr_id,
nr_wrty_st_dt,
nr_wrty_end_dt,
nr_exp_dt,
nr_sla_id,
nr_nx_string1,
nr_nx_string2,
nr_nx_string3,
nr_nx_string4,
nr_nx_string5,
nr_nx_string6,
nr_nx_ref_1,
nr_nx_ref_2,
nr_nx_ref_3,
nr_financial_id,
nr_service_type,
nr_argis_id,
nr_bms,
nr_bmlabel,
nr_bm_rep,
baseline_uuid,
service_impact,
zbusiness_approver_group,
zstakeholders_group,
ztechnical_approver_group,
CREATION_USER,
CREATION_DATE,
LAST_UPDATE_USER,
LAST_UPDATE_DATE,
VERSION_NUMBER) SELECT
IDENT_CURRENT('aud_ca_owned_resource'),
@audit_product, new.last_update_user, 'INSERT', datediff(ss, '1/1/1970', getutcdate()),
new.owned_resource_uuid,
new.nr_prim_skt_id,
new.nr_pr_id,
new.nr_wrty_st_dt,
new.nr_wrty_end_dt,
new.nr_exp_dt,
new.nr_sla_id,
new.nr_nx_string1,
new.nr_nx_string2,
new.nr_nx_string3,
new.nr_nx_string4,
new.nr_nx_string5,
new.nr_nx_string6,
new.nr_nx_ref_1,
new.nr_nx_ref_2,
new.nr_nx_ref_3,
new.nr_financial_id,
new.nr_service_type,
new.nr_argis_id,
new.nr_bms,
new.nr_bmlabel,
new.nr_bm_rep,
new.baseline_uuid,
new.service_impact,
new.zbusiness_approver_group,
new.zstakeholders_group,
new.ztechnical_approver_group,
new.CREATION_USER,
new.CREATION_DATE,
new.LAST_UPDATE_USER,
new.LAST_UPDATE_DATE,
new.VERSION_NUMBER
FROM inserted new
GO
CREATE TRIGGER dbo.ca_tr_upd_usp_owned_resource
ON dbo.usp_owned_resource
FOR UPDATE AS
DECLARE
        @audit_product nvarchar(64),
        @last_update_user nvarchar(64),
        @version_number int


        SET @audit_product = APP_NAME()
SELECT  @version_number = version_number, @last_update_user = last_update_user FROM inserted

IF @version_number != -1
INSERT INTO dbo.aud_usp_owned_resource (
AUDIT_TRAIL_ID,
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
owned_resource_uuid,
nr_prim_skt_id,
nr_pr_id,
nr_wrty_st_dt,
nr_wrty_end_dt,
nr_exp_dt,
nr_sla_id,
nr_nx_string1,
nr_nx_string2,
nr_nx_string3,
nr_nx_string4,
nr_nx_string5,
nr_nx_string6,
nr_nx_ref_1,
nr_nx_ref_2,
nr_nx_ref_3,
nr_financial_id,
nr_service_type,
nr_argis_id,
nr_bms,
nr_bmlabel,
nr_bm_rep,
baseline_uuid,
service_impact,
zbusiness_approver_group,
zstakeholders_group,
ztechnical_approver_group,
CREATION_USER,
CREATION_DATE,
LAST_UPDATE_USER,
LAST_UPDATE_DATE,
VERSION_NUMBER) SELECT
IDENT_CURRENT('aud_ca_owned_resource'),
@audit_product, new.last_update_user, 'UPDATE', datediff(ss, '1/1/1970', getutcdate()),
new.owned_resource_uuid,
new.nr_prim_skt_id,
new.nr_pr_id,
new.nr_wrty_st_dt,
new.nr_wrty_end_dt,
new.nr_exp_dt,
new.nr_sla_id,
new.nr_nx_string1,
new.nr_nx_string2,
new.nr_nx_string3,
new.nr_nx_string4,
new.nr_nx_string5,
new.nr_nx_string6,
new.nr_nx_ref_1,
new.nr_nx_ref_2,
new.nr_nx_ref_3,
new.nr_financial_id,
new.nr_service_type,
new.nr_argis_id,
new.nr_bms,
new.nr_bmlabel,
new.nr_bm_rep,
new.baseline_uuid,
new.service_impact,
new.zbusiness_approver_group,
new.zstakeholders_group,
new.ztechnical_approver_group,
new.CREATION_USER,
new.CREATION_DATE,
new.LAST_UPDATE_USER,
new.LAST_UPDATE_DATE,
new.VERSION_NUMBER
  FROM inserted new
GO
ALTER TABLE [dbo].[usp_owned_resource] ADD CONSTRAINT [XPKusp_owned_resource] PRIMARY KEY CLUSTERED ([owned_resource_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [usp_owned_resource_x0] ON [dbo].[usp_owned_resource] ([nr_argis_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [usp_owned_resource_x1] ON [dbo].[usp_owned_resource] ([nr_bms]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[usp_owned_resource] TO [service_desk_admin_group]
GRANT INSERT ON  [dbo].[usp_owned_resource] TO [service_desk_admin_group]
GRANT DELETE ON  [dbo].[usp_owned_resource] TO [service_desk_admin_group]
GRANT UPDATE ON  [dbo].[usp_owned_resource] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[usp_owned_resource] TO [service_desk_ro_group]
GRANT SELECT ON  [dbo].[usp_owned_resource] TO [swcmadmin]
GRANT INSERT ON  [dbo].[usp_owned_resource] TO [swcmadmin]
GRANT DELETE ON  [dbo].[usp_owned_resource] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[usp_owned_resource] TO [swcmadmin]
GO