Triggers
-
I a couple of months i will need to make a trigger (I am not sure if i even need a trigger, maybe just a procedure) that will notifie workers of a work request from a web application.
Example would be,
Search Criteria (Between Here)
A user wanted a (PAINTER) for some (INTERIOR) work at thier home at the (ZIP CODE) OF DALLAS, TX. 75070. (Locate the nearest (4 painter's) (within 30 miles,) by (zip code)
In addition if another person from the same town was to fill out the form with the same Creteria that would not receive the last 4 painters chosen by the query, the rows would rotate and the NEXT 4 PAINTERS would get the call for work.
I have a month or so before i really need to start working with this, is there any way that we can start a disscussion on this topic, and someone tell me a good place to study up on this type of need.
Thank you,
Erik....
OH yea the workers would be notified by email...
Dam again!
-
If I had this same project I would do the following:
- Modify the table containing the painter's information let's just say it's called "tblPainters" by adding the following fields:
- LastSelected_timestamp, datetime, defaultvalue= GETDATE(), nullable=no (use smalldatetime if you don't need the time down to the milleseconds today or 5 years from now)
- Create a new table called "tblWorkRequestHist" containing the requestor's information, the referenced painters provided by the storedproc from step 3 (procSelectUniquePainters), and a timestamp.
- Painter_ID (foriegn key), bigint, nullable=no
- Requestor_ID (foreign key), bigint, nullable=no
- REQ_timestamp datetime, defaultvalue=GETDATE(), nullable=no
- you may consider storing additional info here like the painter's email address, or requestor's email, and contact info in case of future updates. If you have cascading updates set you'll be updating this history table and you probably don't want to be doing that. You could just decide to turn off cascading updates and cascading deletes and use the data from the two tables so you aren't storing duplicate info.
- Write a stored proc called "procSelectUniquePainters".
- Set up a table type variable to use as a temporary table, instead of creating a temp table (it's supposed to more efficient than an actual temp table)
- Example:
DECLARE @temp TABLE(
Painter_ID bigint,
Distance_inMiles int,
Requestor_ID bigint,
REQ_timestamp datetime)
- Select the painters ids, set the requestor id, and timestamp using a SELECT INTO the temptable variable that haven't been selected within the last hour within the zipcode and distance specified. Note that the distance is calculated using a UDF(User Defined Function) that takes two parameters. Since this select statement returns a small recordset it is acceptable to use a UDF.
- Example:INSERT INTO @temptable
Select TOP 4 Painter_ID,
dbo.fnDistance_inMiles(Painter_Zipcode, Requestor_Zipcode) as Distance_inMiles, @req_id as Requestor_ID, GETDATE() as REQ_timestamp --note req_id is determined elsewhere in the proc.FROM tblPainters
WHERE DATEDIFF(hh,LastSelected_timestamp, GETDATE()) >= 1 --timestamp must be greater than or equal to 1 hour from present time.
and Painter_Zipcode = @Requestors_zipcode
GROUP BY Painter_ID
HAVING MIN(
dbo.fnDistance_inMiles) <= 30 - Then insert these records into your new table "tblWorkRequestHist".
- Next to Email the request for work to the painter... I would use a scheduled DTS package that ran nightly or at whatever scheduled intervals your customers demand. Utilize the Send Mail Task, and Execute SQL Task which should call a stored procedure to select the records from "tblWorkRequestHist" and group by the Painter_ID so that the painter only receives one email with all the work requests (if you want to). More on this later if you need it.
Hope this helps
Scubagirl
SCUBAGIRL -
Thank you very much! I am greatfull for the time that you have taken to write this for me! Thank very much! I am going to print this out and study it, i still have a little while before i have to implement this, so i am glad that you have helped so that i can start right away and study this.
Any helpful hints are suggestions, i look forward to them all! And sure i would like more information about the email process.
Thank YOU,
Erik...
Dam again!
-
Hi Erik,
You are very welcome. I hope my plan works for you. Just remember there's always many ways to accomplish things and this is just my way. You may end up finding another method as well. If you discover any problems just post and I will try to check it now and then. Regarding the Send Mail task I didn't mean to mislead you but I know more about setting up a DTS package than using the actual Send Mail Task because we don't actually have SQL Mail turned on in our production server. I'm trying to get the Admin to get it going but we have LOTUS NOTES and he hasn't been able to set it up. If you end up doing it differently share the knowledge. Also if you haven't worked with UDF's much they are really useful and simple to set up. Don't forget to use your BOOKS ONLINE help, and Google for help also when you get stuck. (You probably already know that). One of the things I read online was that a best practice for sending mail was to use the extended stored proc called xp_sendmail. You may want to research that some more. Good luck.
Jill
SCUBAGIRL -
Sorry for the late responce, I have been working on one control all week and i am just about to pull my hair out, but then again i am finally getting desired results from the little demon so i am happy.
I am very thankful for your help with the code and i look forward to working with it. And i will be tapping on your shoulder when i start implementing it.
Thanks again,
Eik...
Dam again!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply