Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
david_m
Starting Member
6 Posts |
 Posted - 2012-09-07 : 05:41:39
|
| Hi,New to the forum, so apologies if the post has been made on the wrong board (If it has please let me know, and i'll re-post).Ok, i'm keen to stop a manual process within the company I work for which works in the following way; Stock is Delivered, Stock Level rises from x to y, User delivering onto database system sends group email to others to alert them that this is done. Unfortunately due to human error the email doesn't always get sent and people are unaware that stock is available.So, the plan is to do the following, write a trigger that checks the stock table (STK_STOCK) in the physical field (STK_PHYSICAL) on an hourly basis. It needs to check that (STK_PHYSICAL) has changed from 0 to positive, whilst taking into account current allocations (STK_ALLOCATIONS) i.e if 1,000 units are coming in and 1,001 are allocated the trigger should not pick it up.Once the trigger has checked the table for changes, I need it to populate a temporary table with those changed - But, I need it to delete the contents of the previous temporary table (Another trigger running slightly before I think would work).After this, I need to create a SQL Alert that checks the temporary table for records, and then email an Operator with these changes on a time scale that I see fit.Any idea?David |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-09-07 : 05:45:35
|
A trigger isn't fired based on time. A trigger is fired based on action on the table.If you want to execute some SQL each hour for example then you can create a scheduled SQL Server Agent Job.
Too old to Rock'n'Roll too young to die. |
 |
|
|
david_m
Starting Member
6 Posts |
Posted - 2012-09-07 : 08:13:30
|
| Ok, so I can create a trigger to run when STK_PHYSICAL changes from 0 to positive. Then i can run an agent job based on the temp table hourly? |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-09-07 : 08:37:30
|
| It sounds like you should be able to accomplish all you need in a stored procedure - whcih you execute regularly on the server.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
SQL Alerts/Triggers/Temporary Tables