Current setup:
I have a table "Items" with the schema SKU,isAvaiable. I have a web scraper that pulls availability of those SKUs from the wholesaler every night and changes the boolean column isAvailable to true or false.

Problem:
My inventory completely relies on the wholesaler. I know the wholesaler has errors they refuse to fix, so I need to manually change the IsAvailable boolean. I do not want to edit the web scraper.

My Solution:
Add a new column "AvailableOveride". Add a trigger to not allow changes to IsAvailable if AvailableOveride is not null. Also add a rule to make IsAvailable = AvailableOveride when AvailableOveride is set. That way AvailableOveride could be null and do nothing. AvailableOveride could be true to show always in stock. Or AvailableOveride could be false to show always out of stock.


Is this a horrible solution? Is there a better way? I'm not a huge trigger pro, but this should be simple to write.

i must have missed something. what's the point of the trigger at all? just change your SELECT for when you read the data.

eg: select sku, nvl(AvailableOveride, IsAvailable) from items;

you can also wrap this in a view for convenience

This post was edited by carteblanche on Feb 10 2014 12:25am

what information on those errors do you have?
how do you get an update on those stock items where the errors are?

The errors are them saying something is in stock, but they don't send it every week so I know they don't have it. Also, we may have 5 of them, but they are out of stock (not their error, but we need to say we have it)



Forgot a crucial piece of the puzzle. I also have a program at the end that pulls from the db and sends to out. I do not want to change this program either.


My other thought would be to run a program after the web scaper and have that program change IsAvailable to the correct values. I decided this option is less appealing because the data is incorrect for a small window of time.

so why don't you rename the table to something else, then use a view whose name is the table that's getting selected by this program?