trigger to update last sold date in HQ item table

I have no idea about HQ

However, in my opinion creating trigger can slow down database executions. Be careful with that

May be it would be better if you create a stored procedure to contain the update logic and then schedule the stored procedure to run hourly or daily ?

rgds, Joie

Hi Sad - my first suggestion would be to (if its in the budget) work with a RMS-certified partner that can help you build and maintain SQL queries....as SQL is nice to use but it can be tricky to manage. Joie makes a good point regarding triggers, as they can affect the performance of your database. Here's one possible option you can look into (without recurring to a trigger)

The problem with the itemdynamic table is that it tracks several "last sold" dates, one for each store, so you have to "pick" the most recent "last sold" date.

I would first create a view that summarizes for you the "last sold" date for all stores, then join that view to your item table on an update query that you would run as a batch file via the windows scheduler program.

Here is the syntax for the view:

create view item_last_sold as select id.itemid, i.itemlookupcode, MAX(id.lastsold) as last_sold from itemdynamic id inner join item i on id.itemid = i.id group by id.itemid, i.itemlookupcode

Test the successful creation of the view by running select * from item_last_sold

Here is the update query you can save on a notepad, save it as a .bat file, then schedule the .bat file to run after your stores poll with HQ, using the windows scheduler:

update item set item.lastsold = item_last_sold.last_sold from item inner join item_last_sold on item.id = item_last_sold.itemid

If you can, test these on a testing database first, and remember to backup your database prior to running the queries.

But, really, this is someth> thank you joie