Post reply

timing an issue - when and how

  • November 8, 2006 at 7:34 pm

    #115047

    Hi,

    I have a scenario which hopefully you might be able to shed some light on getting a solution. So essentially the application inserts data into Tab1, then Tab2 & I want to stop this process under specific circumstances

    So I created an instead of insert trigger to fire when my circumstances are met - if the circumstances are not met the records are inserted as normal. This works okay.

    However I have a problem with the second insert into the Tab2 - this occurs after the initial insert, so I can't say don't insert into Tab1 then delete from Tab2 where the values from Tab1 = the values from Tab2 (because they arn't in there yet)

    Now, I could possibly set up another trigger on Tab2, to fire when the data is inserted into there, but this is just another administrative overhead - so ideally was wondering if there is any nice way to keep it all packaged up under one area.

    I tried DELAY in the trigger on Tab1, hoping that the app would carry on and write to Tab2 but it didn't as it was obviously waiting for the processing of the trigger on Tab1 to complete - so that didn't assist at all.

    Is the second trigger the only way to go? Let me know if my explaination is really bad.

    Thanks in advance

    Troy

  • November 13, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • November 13, 2006 at 11:14 am

    #671411

    Troy,

    I may need more information about your application, so I will have to make some assumptions.  I read your post twice to make sure I am understanding what you are asking here: You want to insert into Tab1 and Tab2; if certain conditions are true, execute both inserts, otherwise, insert nothing.  Is that right?  If so, it sounds to me that a simple TRANSACTION can handle this, rather than a trigger.  I am assuming you have the data for both inserts at the time of the first insert and you can query the database of your specific circumstance. 

    Jon

  • November 19, 2006 at 9:01 pm

    #672854

    Hi Jon,

    Thanks very much for your response - and I apologise for not getting back here sooner.

    In any light the problem is that the application is writing to 2 different tables in 2 seperate transactions. I don't have any control over the transactions (or any other aspect of the application) which is why I am trying to do a dodgy work around by altering the data which the application is trying to write.

    Anyhow, I have managed this with the trigger on the second table, which then checks the first table also - it works but was hoping for a more consolidated solution. But in any light we have requested that the developer (of the application) make a programatic change to do what I am trying to do with the triggers - so to some extent I am off the hook.

    Thanks again for your response!

    Cheers

    Troy

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

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