July 3, 2009 at 2:57 am
Hello All,
Need the solution of my problem.
I have a table Countries and it has three different triggers on it like one is 'TR_Insert' second id 'TR_Update' and third is 'TR_Delete' and every one will cause to start a specific SQL Job by using the command SP_Start_JOB 'insert country job' and delete and update jobs respectively.
it works when i used one task within one transaction. but it does not work when i used all of them or more than one task within a transaction and it gives the message like 'Job is already running'.. what i need to do if i have to use two inserts or two deletes or two updates within one transaction and i want the control of execution back to the code.
example:
Begin Tran
Update countries
set name = 'country name'
where id = 1
update countries
set name = 'second country name'
where id = 2
commit
is there any way to achieve this ??
Thanks,
Noman
July 3, 2009 at 3:27 am
There are so many things wrong with this approach, a lot of which you've already recognized.
You've completely blown away any transactional integrity. How can you guarantee that the job succeeds even without the problems you've mentioned.
You need to delve into the job and see exactly what it's doing first.
Depending on what the job does, it may be possible to move the code into the trigger, or you may need to look at other solutions. It's not possible to make firm suggestions until we know what the job does.
July 3, 2009 at 3:32 am
Thanks, Actually i know this is not a good approach but it was interesting that how i can pull my control back from this situation. Although i am also trying to incorporate the job code into the trigger or working on alternate better solution.
Thanks again for your quick response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply