CREATE TABLE [dbo].[ci_hardware_workstation]
(
[phys_mem] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mem_capacity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hard_drive_capacity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[proc_type] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[proc_speed] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[disk_type] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cd_rom_type] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[graphics_card] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[scsi_card] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[modem_type] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[modem_card] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[net_card] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[monitor] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[printer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[number_slot_proc] [int] NULL,
[number_proc_inst] [int] NULL,
[slot_total_mem] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[slot_mem_used] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[bios_ver] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[processor_count] [int] NULL,
[security_patch_level] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[active_date] [int] NULL,
[retire_date] [int] NULL,
[priority] [int] NULL,
[SLA] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[leased_or_owned_status] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[proj_code] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[contract_number] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lease_effective_date] [int] NULL,
[lease_termination_date] [int] NULL,
[lease_renewal_date] [int] NULL,
[lease_cost_per_month] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[purchase_amount] [int] NULL,
[mtce_type] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[maintenance_period] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mtce_contract_number] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[maintenance_fee] [int] NULL,
[del] [int] NOT NULL CONSTRAINT [DF__ci_hardware__del__5849823D] DEFAULT ('0'),
[purchase_amountc] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[own_resource_uuid] [binary] (16) NOT NULL,
[creation_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL,
[organization_for_contact] [binary] (16) NULL
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.ca_tr_del_ci_hardware_workstat
ON dbo.ci_hardware_workstation
FOR DELETE AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ci_hardware_workstation (
AUDIT_TRAIL_ID,
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
phys_mem,
mem_capacity,
hard_drive_capacity,
proc_type,
proc_speed,
disk_type,
cd_rom_type,
graphics_card,
scsi_card,
modem_type,
modem_card,
net_card,
monitor,
printer,
number_slot_proc,
number_proc_inst,
slot_total_mem,
slot_mem_used,
bios_ver,
processor_count,
security_patch_level,
active_date,
retire_date,
priority,
SLA,
leased_or_owned_status,
proj_code,
contract_number,
lease_effective_date,
lease_termination_date,
lease_renewal_date,
lease_cost_per_month,
purchase_amount,
mtce_type,
maintenance_period,
mtce_contract_number,
maintenance_fee,
del,
purchase_amountc,
own_resource_uuid,
creation_user,
last_update_user,
creation_date,
last_update_date,
version_number,
organization_for_contact) SELECT
IDENT_CURRENT('aud_ca_owned_resource') +1,
@audit_product,old.last_update_user, 'DELETE', datediff(ss, '1/1/1970', getutcdate()),
old.phys_mem,
old.mem_capacity,
old.hard_drive_capacity,
old.proc_type,
old.proc_speed,
old.disk_type,
old.cd_rom_type,
old.graphics_card,
old.scsi_card,
old.modem_type,
old.modem_card,
old.net_card,
old.monitor,
old.printer,
old.number_slot_proc,
old.number_proc_inst,
old.slot_total_mem,
old.slot_mem_used,
old.bios_ver,
old.processor_count,
old.security_patch_level,
old.active_date,
old.retire_date,
old.priority,
old.SLA,
old.leased_or_owned_status,
old.proj_code,
old.contract_number,
old.lease_effective_date,
old.lease_termination_date,
old.lease_renewal_date,
old.lease_cost_per_month,
old.purchase_amount,
old.mtce_type,
old.maintenance_period,
old.mtce_contract_number,
old.maintenance_fee,
old.del,
old.purchase_amountc,
old.own_resource_uuid,
old.creation_user,
old.last_update_user,
old.creation_date,
old.last_update_date,
old.version_number,
old.organization_for_contact
FROM deleted old
GO
CREATE TRIGGER dbo.ca_tr_ins_ci_hardware_workstat
ON dbo.ci_hardware_workstation
FOR INSERT AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ci_hardware_workstation (
AUDIT_TRAIL_ID,
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
phys_mem,
mem_capacity,
hard_drive_capacity,
proc_type,
proc_speed,
disk_type,
cd_rom_type,
graphics_card,
scsi_card,
modem_type,
modem_card,
net_card,
monitor,
printer,
number_slot_proc,
number_proc_inst,
slot_total_mem,
slot_mem_used,
bios_ver,
processor_count,
security_patch_level,
active_date,
retire_date,
priority,
SLA,
leased_or_owned_status,
proj_code,
contract_number,
lease_effective_date,
lease_termination_date,
lease_renewal_date,
lease_cost_per_month,
purchase_amount,
mtce_type,
maintenance_period,
mtce_contract_number,
maintenance_fee,
del,
purchase_amountc,
own_resource_uuid,
creation_user,
last_update_user,
creation_date,
last_update_date,
version_number,
organization_for_contact) SELECT
IDENT_CURRENT('aud_ca_owned_resource'),
@audit_product, new.last_update_user, 'INSERT', datediff(ss, '1/1/1970', getutcdate()),
new.phys_mem,
new.mem_capacity,
new.hard_drive_capacity,
new.proc_type,
new.proc_speed,
new.disk_type,
new.cd_rom_type,
new.graphics_card,
new.scsi_card,
new.modem_type,
new.modem_card,
new.net_card,
new.monitor,
new.printer,
new.number_slot_proc,
new.number_proc_inst,
new.slot_total_mem,
new.slot_mem_used,
new.bios_ver,
new.processor_count,
new.security_patch_level,
new.active_date,
new.retire_date,
new.priority,
new.SLA,
new.leased_or_owned_status,
new.proj_code,
new.contract_number,
new.lease_effective_date,
new.lease_termination_date,
new.lease_renewal_date,
new.lease_cost_per_month,
new.purchase_amount,
new.mtce_type,
new.maintenance_period,
new.mtce_contract_number,
new.maintenance_fee,
new.del,
new.purchase_amountc,
new.own_resource_uuid,
new.creation_user,
new.last_update_user,
new.creation_date,
new.last_update_date,
new.version_number,
new.organization_for_contact
FROM inserted new
GO
CREATE TRIGGER dbo.ca_tr_upd_ci_hardware_workstat
ON dbo.ci_hardware_workstation
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_ci_hardware_workstation (
AUDIT_TRAIL_ID,
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
phys_mem,
mem_capacity,
hard_drive_capacity,
proc_type,
proc_speed,
disk_type,
cd_rom_type,
graphics_card,
scsi_card,
modem_type,
modem_card,
net_card,
monitor,
printer,
number_slot_proc,
number_proc_inst,
slot_total_mem,
slot_mem_used,
bios_ver,
processor_count,
security_patch_level,
active_date,
retire_date,
priority,
SLA,
leased_or_owned_status,
proj_code,
contract_number,
lease_effective_date,
lease_termination_date,
lease_renewal_date,
lease_cost_per_month,
purchase_amount,
mtce_type,
maintenance_period,
mtce_contract_number,
maintenance_fee,
del,
purchase_amountc,
own_resource_uuid,
creation_user,
last_update_user,
creation_date,
last_update_date,
version_number,
organization_for_contact) SELECT
IDENT_CURRENT('aud_ca_owned_resource'),
@audit_product, new.last_update_user, 'UPDATE', datediff(ss, '1/1/1970', getutcdate()),
new.phys_mem,
new.mem_capacity,
new.hard_drive_capacity,
new.proc_type,
new.proc_speed,
new.disk_type,
new.cd_rom_type,
new.graphics_card,
new.scsi_card,
new.modem_type,
new.modem_card,
new.net_card,
new.monitor,
new.printer,
new.number_slot_proc,
new.number_proc_inst,
new.slot_total_mem,
new.slot_mem_used,
new.bios_ver,
new.processor_count,
new.security_patch_level,
new.active_date,
new.retire_date,
new.priority,
new.SLA,
new.leased_or_owned_status,
new.proj_code,
new.contract_number,
new.lease_effective_date,
new.lease_termination_date,
new.lease_renewal_date,
new.lease_cost_per_month,
new.purchase_amount,
new.mtce_type,
new.maintenance_period,
new.mtce_contract_number,
new.maintenance_fee,
new.del,
new.purchase_amountc,
new.own_resource_uuid,
new.creation_user,
new.last_update_user,
new.creation_date,
new.last_update_date,
new.version_number,
new.organization_for_contact
  FROM inserted new
GO
ALTER TABLE [dbo].[ci_hardware_workstation] ADD CONSTRAINT [PK__ci_hardware_work__40870745] PRIMARY KEY NONCLUSTERED ([own_resource_uuid]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[ci_hardware_workstation] TO [cmdb_admin]
GRANT INSERT ON  [dbo].[ci_hardware_workstation] TO [cmdb_admin]
GRANT DELETE ON  [dbo].[ci_hardware_workstation] TO [cmdb_admin]
GRANT UPDATE ON  [dbo].[ci_hardware_workstation] TO [cmdb_admin]
GRANT SELECT ON  [dbo].[ci_hardware_workstation] TO [public]
GRANT INSERT ON  [dbo].[ci_hardware_workstation] TO [public]
GRANT DELETE ON  [dbo].[ci_hardware_workstation] TO [public]
GRANT UPDATE ON  [dbo].[ci_hardware_workstation] TO [public]
GRANT SELECT ON  [dbo].[ci_hardware_workstation] TO [service_desk_admin_group]
GRANT INSERT ON  [dbo].[ci_hardware_workstation] TO [service_desk_admin_group]
GRANT DELETE ON  [dbo].[ci_hardware_workstation] TO [service_desk_admin_group]
GRANT UPDATE ON  [dbo].[ci_hardware_workstation] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[ci_hardware_workstation] TO [service_desk_ro_group]
GO