| Author |
Topic |
|
jp2code
Posting Yak Master
175 Posts |
 Posted - 2009-01-06 : 14:04:48
|
Can an SQL Trigger be created and distroyed on the fly?I've never created a trigger, so I certainly don't know how to create them on the fly.Here's the situation:We have a Pocket PC application that queries our server for the details of a given part number, and displays the results on the screen.If the status of that part number is changed somewhere on our manufacturing plant, the SQL data is updated, but the Operator has to run the Pocket PC application's query again to show the updated results.What I'd like to do is create a trigger whenever an Operator is given the status of a particular part number. If that part's status changes on the SQL Server, I want that updated data sent to my application. Then, whenever the Operator dismisses that part's screen, I want that trigger to be distroyed.Is this possible? How would I go about doing such a thing?Optionally, I could have a timer on the Pocket PC read from the Server at set intervals, but this is a little taxing on the small processor of a Pocket PC.Thanks for any help.
Avoid Sears Home Improvement |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2009-01-06 : 14:38:05
|
Why would a trigger be bad? What is a better alternative?How are triggers "written" to be created or dropped? Our DBA does not know how to write them, and I am a C# developer.
Avoid Sears Home Improvement |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-01-06 : 14:52:55
|
| Sounds to me like he should be using replication. This is what replication was designed to do...________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-01-06 : 17:25:05
|
| Isn't it just a single sql server and a bunch of distributed apps (on pocket PCs)?A "on the fly" trigger is NOT the way to go. You don't want concurrent users to be dropping and adding triggers to the same table. You also don't want to use a trigger to launch external processes. Any error for any reason will rollback the transaction that updated the part at the manufacturing plant. I'm sure they wouldn't like some yahoo with a pocket pc causing their updates to be lost.Sounds like you may need a small alerting service. This app would track who needs to be notified based on which operators have which parts currently "on screen". The service could poll the database if there is a modified date in the parts table. Or a permanent trigger could write a simple row to a table dedicated to serve the alerting service. I'm not sure how you are going to know when an operator "dismisses" a screen though...Be One with the OptimizerTG |
|
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2009-01-06 : 19:19:24
|
Well, following the blind man's suggestion (is this the blind leading the blind?), I posted another question entitled, "Replicate SQL CE with SQL Server?".Here's my thinking: I'll create a View that is based on a part number and try to get that view to "replicate" with a SQL CE table on the device... thought I don't know how to do that yet! :)
Avoid Sears Home Improvement |
|
|
|
silvershark
Starting Member
48 Posts |
Posted - 2009-01-06 : 19:26:03
|
| If you were to use a trigger, you might want to use a trigger that really all it does is activate a stored procedure based on certian criteria. IE: If there is a status of sold column ITEM_STATUS= S for sold then you could have your trigger watch the ITEM_STATUS column for any time a part number has a status of S to activate the stored procedure, and if ITEM_STATUS=A for Active then do nothing.Your stored procedure could be to update a view...Just an idea. |
|
|
|
|
On-The-Fly Triggers