Trigger Question
-
What I'm wanting to do is take DNC requests from various clients and when I insert a record into my DNC table have a trigger fire that goes to my off-site locations and if the record is there, then mark it as DNC as well. My problem though is that I'm not sure exactly what the best way to do this.
I'm assuming a insert / update trigger would be the better option of the various ones I have looked at, but I cannot find a way to pass only the number being updated into the trigger in order to limit my time cost per update.
Flowcharting this problem would look like this:
Receive Number from Client to be DNC'ed (111-555-1212)
Insert number into Internal DNC table
If the number exists in my remote server then mark it as DNC
Anyone know of a clean method for doing stuff like this?
-
Is it necessary to have this reflected in real time? If it is, perhaps replication would be more suitable. If real-time isn't required, a batch solution, run at some convenient interval (each day, hour or whatever) may do the trick.
I would really recommend against having a trigger reaching out too far from where it lives. Doing that is imo just a disaster waiting to happen.
/Kenneth
-
It's not required to be in real time, but then this would bring me to another question of how would I send out multiple phone numbers to have them compared?
Currently my DNC list is ~50k numbers, each with a date of when they were added in. On any given day I can add between 1 and 30 DNC requests and would need to be able to batch a job that would only get the newest set. I have been working with a query to do this, but am having no luck.
Here is the query I'm using that isn't working:
Update D set D.CRC='N' from [192.168.5.100].Dialer.dbo.Dial D inner join [Internal DNC] on [Internal DNC].Phone=D.Phonenum where D.CRC not in ('P','N') and [Internal DNC].[Date]='3/21/05'
This query of course is assuming I'm looking at the entire DNC table and then trying to limit it only to the results with a date of '3/21/05' but hopefully whoever looks at this will get a good idea of what I'm doing and can point me in a direction that will work.
-
If you have the same phonenumber several times, only with different dates, and you want to find then 'newest', then...
select phonenum, MAX(date) as maxDate
from myTable
group by phonenum
...will find the 'newest' phone-date combo.
You can wrap that into a virtual table and join against it
update d
set d.crc = 'N'
from (
select phonenum, MAX(date) as maxDate
from myTable
group by phonenum
) x
join myDialTable d
on d.phonenum = x.phonenum
and d.date = x.maxDate
...though I'm not entirely sure that's what you're really trying to do.
Another option might be to keep the 'problem table' clear of all the dupes, so maxDate won't be an issue.
/Kenneth
-
I would agree with Kenneth here.
A better solution would be, perhaps, to have a second database. You can apply some logic into triggers that will update/insert into the second database. This second database can form part of a replicated system that only pushes out the data that you need at the remote sites.
I know that sounds like an upheaval but in the end you will have a lot more centralised control.
Any good?
Graeme
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply