June 12, 2017 at 3:31 pm
Hello,
I have a vendor that is impossible to deal with, I'll just leave it at that. They won't allow me to do anything useful within our database so I have to come up with some outside of the box solutions.
We have a table called INVENTORY. Does exactly what you would think. When sales in the store are made, the application reduces QUANT_ON_HAND by the quantity of the item that was purchased. When replacement inventory arrives, the inventory manager updates it in the system and QUANT_ON_HAND gets updated with the amount on hand. The inventory manager needs to see history to be able to see who is making changes to the quantities. Since the application developers don't care to write the code to write to an audit table, their DBA uses triggers on some of the tables so we can see history. However, when I asked about this for the INVENTORY table, I was told that this table is hit thousands of times per day and would bring our database server down. Since I set this up last Thursday, I only have a little over 1,800 records in the history. They obviously need to learn but that is a point for another discussion.
My outside of the box solution was to use transactional replication to another database (on the same server) and then setup the trigger there. It all worked as expected but I'm missing the username that made the change. All of the history records that are inserted have the username (and HOST_NAME) for the SQL Agent (since that is what the publication and subscription are running under, I assume). In order for this to be completely usable, I need to know who made the change so the inventory manager knows who to speak with regarding discrepancies.
So, my question is this: How can I obtain the user name who is making the changes into the replicated database?
OR
Is there a better approach to getting the history that won't violate our "don't do anything in the database" license nonsense?
All relevant code minus sample records is here:
CREATE TABLE [SYSADM].[INVENTORY](
[ITEM_CODE] [varchar](30) NOT NULL,
[ITEM_ABBREV] [varchar](13) NULL,
[ITEM_NAME] [varchar](50) NULL,
[ITEM_CLASS] [varchar](10) NULL,
[ITEM_TYPE] [varchar](10) NULL,
[UPC] [varchar](15) NULL,
[DEPT] [varchar](10) NULL,
[ACCOUNT_CODE] [varchar](50) NULL,
[DEA_CODE] [varchar](10) NULL,
[MANUFACTURER_CODE] [varchar](10) NULL,
[DISPENSE_DOSE] [numeric](6, 2) NULL,
[DISPENSE_UNIT] [varchar](10) NULL,
[ITEM_UNIT] [varchar](10) NULL,
[CURRENT_COST] [numeric](9, 2) NULL,
[DISPENSE_PRICE] [numeric](9, 2) NULL,
[TAX_TYPE] [varchar](5) NULL,
[QUANT_ON_HAND] [numeric](9, 2) NULL,
[ORDER_POINT] [numeric](9, 2) NULL,
[LOCATION] [varchar](8) NULL,
[USERID] [varchar](8) NULL,
[STAMP] [datetime] NULL,
[ITEM_LOCATION] [varchar](8) NULL,
[ITEM_STAT] [varchar](10) NULL,
[account_code2] [varchar](50) NULL,
[extra1] [varchar](26) NULL,
[extra2] [varchar](26) NULL,
[extra3] [varchar](26) NULL,
[extra4] [varchar](26) NULL,
[extra5] [varchar](26) NULL,
[hide_receipt_f4] [varchar](1) NULL,
[inventory_identity] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[jurisdiction] [varchar](15) NULL,
[processing_type] [varchar](50) NULL,
[tag_term] [int] NULL,
[tag_term_avail] [int] NULL,
CONSTRAINT [inv_itc] PRIMARY KEY CLUSTERED
(
[ITEM_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY];
CREATE TABLE [SYSADM].[INVENTORY_HISTORY](
[ITEM_CODE] [varchar](30) NOT NULL,
[ITEM_ABBREV] [varchar](13) NULL,
[ITEM_NAME] [varchar](50) NULL,
[ITEM_CLASS] [varchar](10) NULL,
[ITEM_TYPE] [varchar](10) NULL,
[UPC] [varchar](15) NULL,
[DEPT] [varchar](10) NULL,
[ACCOUNT_CODE] [varchar](50) NULL,
[DEA_CODE] [varchar](10) NULL,
[MANUFACTURER_CODE] [varchar](10) NULL,
[DISPENSE_DOSE] [numeric](6, 2) NULL,
[DISPENSE_UNIT] [varchar](10) NULL,
[ITEM_UNIT] [varchar](10) NULL,
[CURRENT_COST] [numeric](9, 2) NULL,
[DISPENSE_PRICE] [numeric](9, 2) NULL,
[TAX_TYPE] [varchar](5) NULL,
[QUANT_ON_HAND] [numeric](9, 2) NULL,
[ORDER_POINT] [numeric](9, 2) NULL,
[LOCATION] [varchar](8) NULL,
[USERID] [varchar](8) NULL,
[STAMP] [datetime] NULL,
[ITEM_LOCATION] [varchar](8) NULL,
[ITEM_STAT] [varchar](10) NULL,
[account_code2] [varchar](50) NULL,
[extra1] [varchar](26) NULL,
[extra2] [varchar](26) NULL,
[extra3] [varchar](26) NULL,
[extra4] [varchar](26) NULL,
[extra5] [varchar](26) NULL,
[hide_receipt_f4] [varchar](1) NULL,
[inventory_identity] [int] IDENTITY(1,1) NOT NULL,
[jurisdiction] [varchar](15) NULL,
[processing_type] [varchar](50) NULL,
[tag_term] [int] NULL,
[tag_term_avail] [int] NULL,
[db_stamp] [datetime] NULL,
[db_user] [varchar](80) NULL,
[db_workstation] [varchar](80) NULL
) ON [PRIMARY];
ALTER TRIGGER [SYSADM].[tr_INVENTORY_HISTORY_Delete] ON [SYSADM].[INVENTORY] FOR DELETE AS
BEGIN
SET IDENTITY_INSERT SYSADM.INVENTORY_HISTORY ON;
INSERT INTO SYSADM.INVENTORY_HISTORY(ITEM_CODE,
ITEM_ABBREV,
ITEM_NAME,
ITEM_CLASS,
ITEM_TYPE,
UPC,
DEPT,
ACCOUNT_CODE,
DEA_CODE,
MANUFACTURER_CODE,
DISPENSE_DOSE,
DISPENSE_UNIT,
ITEM_UNIT,
CURRENT_COST,
DISPENSE_PRICE,
TAX_TYPE,
QUANT_ON_HAND,
ORDER_POINT,
[LOCATION],
USERID,
STAMP,
ITEM_LOCATION,
ITEM_STAT,
account_code2,
extra1,
extra2,
extra3,
extra4,
extra5,
hide_receipt_f4,
inventory_identity,
jurisdiction,
processing_type,
tag_term,
tag_term_avail,
db_stamp,
db_user,
db_workstation)
SELECT ITEM_CODE,
ITEM_ABBREV,
ITEM_NAME,
ITEM_CLASS,
ITEM_TYPE,
UPC,
DEPT,
ACCOUNT_CODE,
DEA_CODE,
MANUFACTURER_CODE,
DISPENSE_DOSE,
DISPENSE_UNIT,
ITEM_UNIT,
CURRENT_COST,
DISPENSE_PRICE,
TAX_TYPE,
QUANT_ON_HAND,
ORDER_POINT,
'DELETE',
USERID,
STAMP,
ITEM_LOCATION,
ITEM_STAT,
account_code2,
extra1,
extra2,
extra3,
extra4,
extra5,
hide_receipt_f4,
inventory_identity,
jurisdiction,
processing_type,
tag_term,
tag_term_avail,
GETDATE(),
RIGHT(SUSER_SNAME(), 80),
RIGHT(HOST_NAME(), 80)
FROM deleted;
SET IDENTITY_INSERT SYSADM.INVENTORY_HISTORY OFF;
END;
ALTER TABLE SYSADM.INVENTORY
ENABLE TRIGGER tr_INVENTORY_HISTORY_Delete;
ALTER TRIGGER [SYSADM].[tr_INVENTORY_HISTORY_InsertUpdate] ON [SYSADM].[INVENTORY] FOR INSERT, UPDATE AS
BEGIN
SET IDENTITY_INSERT SYSADM.INVENTORY_HISTORY ON;
INSERT INTO SYSADM.INVENTORY_HISTORY(ITEM_CODE,
ITEM_ABBREV,
ITEM_NAME,
ITEM_CLASS,
ITEM_TYPE,
UPC,
DEPT,
ACCOUNT_CODE,
DEA_CODE,
MANUFACTURER_CODE,
DISPENSE_DOSE,
DISPENSE_UNIT,
ITEM_UNIT,
CURRENT_COST,
DISPENSE_PRICE,
TAX_TYPE,
QUANT_ON_HAND,
ORDER_POINT,
[LOCATION],
USERID,
STAMP,
ITEM_LOCATION,
ITEM_STAT,
account_code2,
extra1,
extra2,
extra3,
extra4,
extra5,
hide_receipt_f4,
inventory_identity,
jurisdiction,
processing_type,
tag_term,
tag_term_avail,
db_stamp,
db_user,
db_workstation)
SELECT ITEM_CODE,
ITEM_ABBREV,
ITEM_NAME,
ITEM_CLASS,
ITEM_TYPE,
UPC,
DEPT,
ACCOUNT_CODE,
DEA_CODE,
MANUFACTURER_CODE,
DISPENSE_DOSE,
DISPENSE_UNIT,
ITEM_UNIT,
CURRENT_COST,
DISPENSE_PRICE,
TAX_TYPE,
QUANT_ON_HAND,
ORDER_POINT,
[LOCATION],
USERID,
STAMP,
ITEM_LOCATION,
ITEM_STAT,
account_code2,
extra1,
extra2,
extra3,
extra4,
extra5,
hide_receipt_f4,
inventory_identity,
jurisdiction,
processing_type,
tag_term,
tag_term_avail,
GETDATE(),
RIGHT(SUSER_SNAME(), 80),
RIGHT(HOST_NAME(), 80)
FROM inserted;
SET IDENTITY_INSERT SYSADM.INVENTORY_HISTORY OFF;
END;
ALTER TABLE SYSADM.INVENTORY
ENABLE TRIGGER tr_INVENTORY_HISTORY_InsertUpdate;
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply