Hepl!! could a Trigger/Job solve my problem?

Question

Hepl!! could a Trigger/Job solve my problem?

jon-474332

SSC Eights!

Points: 839
More actions
October 12, 2007 at 12:32 am

#175887

Hi,
I'm lost... Being restrained by existing DB design, i need to cater for an exceptional case where the user will input (1)an effective start date and (2)value in one table (i.e. tCode).
On that effective start date itself, i need to copy the value into another table (i.e. tTax). Is there a way to do this programmatically?? Is it possible to detect the insert event and schedule a SQL job according to the effective start date specified by the user?? or is there an easier way out??
Any help/suggestion is greatly appreciated..
Thanks,
Jon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Jon

How about a job that runs every day at midnight and checks the values in the StartDate column against today's date and copies them over if it's identical? Here's some pseudo code since you haven't posted your table structure and I don't know the syntax of the date manipulation functions off by heart:

INSERT INTO Table2 (StartDate, tcode)

SELECT StartDate, tcode

FROM Table1

WHERE StartDate BETWEEN GETDATE() AND GETDATE() + 1

John

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

You may want to have a look at http://sql-server-performance.com/Community/forums/p/14315/82960.aspx

This would set up the job from inside a trigger. However, I'd agree with John that a nicer solution would be to use a trigger to insert the data and poll that table from an application, or alternatively use the Service Broker.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

jon-474332 SSC Eights! Points: 839 More actions · Answer 3

Thanks John for ya reply 🙂

Its a gd and easy way out but i've to schedule the job to run daily. Guess i'll do it your way or alternatively is to write a batch job and ask the user to schedule it themselves haha..

Thanks 😉

Jon

jon-474332 SSC Eights! Points: 839 More actions · Answer 4

Thanks Andras,

Been google-ing for sometime but fail to come across any forum thread/article. Guess i've been using the wrong keyword.. That thread is great.. now i've some ideas to kick-start..

With thanks, 🙂

Jon