Edit conflicts between table and trigger "Mark" <asd[ at ]notMe.com> 08.03.2006 09:36:24 Hi folks I probably shouldn't be posting this here but... Using Access XP / MSDE I am in the process of migrating a very complex mdb/mde to ADP. This will be a two stage process (due to urgency), the first stage being migration of the backend and some core functionaility to MSDE/mde and the second migrating the remaining functionality to MSDE/ade. I have a table which has a trigger set for insert/update which modifies one of it's own values based on various conditions. The problem is that if I try to modify a record through a linked table in an mde I get a write conflict (even though I am defitely the only person on the database). If I edit the record through Enterprise Manager directly I don't get an error at all. Anyone know how this can be avoided or if the error is just a symtom of something else entirely? Many thanks Mark

Re: Edit conflicts between table and trigger "Malcolm Cook" <malcook[ at ]newsgroup.nospam> 08.03.2006 17:17:42 Mark, Things to try (in order): 1) make sure the trigger does a 'set nocount on' first thing 2) add a timestamp to the table 3) tell us what happens, and if it does not go way, then send the CREATE TABLE statement, the error message, the text of the trigger 4) maybe remove any bit fields on the table (recast them as smallint) 5) ???? -- Malcolm Cook Stowers Institute for Medical Research - Kansas City, MO USA "Mark" <asd[ at ]notMe.com> wrote in message news:sAxPf.73411$494.17985[ at ]newsfe2-gui.ntli.net... [Quoted Text] > Hi folks > > I probably shouldn't be posting this here but... > > Using Access XP / MSDE > > I am in the process of migrating a very complex mdb/mde to ADP. This will be a two stage process (due to urgency), the first stage > being migration of the backend and some core functionaility to MSDE/mde and the second migrating the remaining functionality to > MSDE/ade. > > I have a table which has a trigger set for insert/update which modifies one of it's own values based on various conditions. The > problem is that if I try to modify a record through a linked table in an mde I get a write conflict (even though I am defitely the > only person on the database). If I edit the record through Enterprise Manager directly I don't get an error at all. > > Anyone know how this can be avoided or if the error is just a symtom of something else entirely? > > Many thanks > > Mark >

Re: Edit conflicts between table and trigger "Mark" <asd[ at ]notMe.com> 08.03.2006 17:32:53 Malcolm Thankyou very much :[ at ]) option 1 was the solution Mark "Malcolm Cook" <malcook[ at ]newsgroup.nospam> wrote in message news:ObLa3QtQGHA.4920[ at ]tk2msftngp13.phx.gbl... [Quoted Text] > Mark, > > Things to try (in order): > > 1) make sure the trigger does a 'set nocount on' first thing > 2) add a timestamp to the table > 3) tell us what happens, and if it does not go way, then send the CREATE > TABLE statement, the error message, the text of the trigger > 4) maybe remove any bit fields on the table (recast them as smallint) > 5) ???? > > -- > Malcolm Cook > Stowers Institute for Medical Research - Kansas City, MO USA > > >> I have a table which has a trigger set for insert/update which modifies >> one of it's own values based on various conditions. The problem is that >> if I try to modify a record through a linked table in an mde I get a >> write conflict (even though I am defitely the only person on the >> database). If I edit the record through Enterprise Manager directly I >> don't get an error at all. >> >> Anyone know how this can be avoided or if the error is just a symtom of >> something else entirely? >> >> Many thanks >> >> Mark >> > >

Re: Edit conflicts between table and trigger "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> 16.03.2006 02:54:01 Hi Malcolm, I don't use a lot of triggers, and the ones I do are mostly one-liners. Can you elaborate on the "set nocount on" issue a bit. I'm familiar with what it does, but why is it a concern in triggers? Do I need to turn nocount off at the end, or is state preserved after the trigger completes? To be honest, it's been so long since I designed my triggers, that I don't even remember if I did that, or knew to do that at the time...tomorrow I'll have to go back and check, of course. :) Thanks, Rob "Malcolm Cook" <malcook[ at ]newsgroup.nospam> wrote in message news:ObLa3QtQGHA.4920[ at ]tk2msftngp13.phx.gbl... [Quoted Text] > Mark, > > Things to try (in order): > > 1) make sure the trigger does a 'set nocount on' first thing > 2) add a timestamp to the table > 3) tell us what happens, and if it does not go way, then send the CREATE > TABLE statement, the error message, the text of the trigger > 4) maybe remove any bit fields on the table (recast them as smallint) > 5) ???? > > -- > Malcolm Cook > Stowers Institute for Medical Research - Kansas City, MO USA > > > "Mark" <asd[ at ]notMe.com> wrote in message > news:sAxPf.73411$494.17985[ at ]newsfe2-gui.ntli.net... >> Hi folks >> >> I probably shouldn't be posting this here but... >> >> Using Access XP / MSDE >> >> I am in the process of migrating a very complex mdb/mde to ADP. This will >> be a two stage process (due to urgency), the first stage being migration >> of the backend and some core functionaility to MSDE/mde and the second >> migrating the remaining functionality to MSDE/ade. >> >> I have a table which has a trigger set for insert/update which modifies >> one of it's own values based on various conditions. The problem is that >> if I try to modify a record through a linked table in an mde I get a >> write conflict (even though I am defitely the only person on the >> database). If I edit the record through Enterprise Manager directly I >> don't get an error at all. >> >> Anyone know how this can be avoided or if the error is just a symtom of >> something else entirely? >> >> Many thanks >> >> Mark >> > >

Re: Edit conflicts between table and trigger "Malcolm Cook" <malcook[ at ]newsgroup.nospam> 20.03.2006 23:38:54 Robert, Failing to issue SET NOCOUNT ON results in ADP's confusing the row count message as the result set. I can't put my finger on it any better, but tahtis it in a nutshell. Cheers, malcolm "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> wrote in message news:%23P1p2UKSGHA.2088[ at ]TK2MSFTNGP14.phx.gbl... [Quoted Text] > Hi Malcolm, > > I don't use a lot of triggers, and the ones I do are mostly one-liners. Can you elaborate on the "set nocount on" issue a bit. > I'm familiar with what it does, but why is it a concern in triggers? Do I need to turn nocount off at the end, or is state > preserved after the trigger completes? > > To be honest, it's been so long since I designed my triggers, that I don't even remember if I did that, or knew to do that at the > time...tomorrow I'll have to go back and check, of course. :) > > > > Thanks, > Rob > > "Malcolm Cook" <malcook[ at ]newsgroup.nospam> wrote in message news:ObLa3QtQGHA.4920[ at ]tk2msftngp13.phx.gbl... >> Mark, >> >> Things to try (in order): >> >> 1) make sure the trigger does a 'set nocount on' first thing >> 2) add a timestamp to the table >> 3) tell us what happens, and if it does not go way, then send the CREATE TABLE statement, the error message, the text of the >> trigger >> 4) maybe remove any bit fields on the table (recast them as smallint) >> 5) ???? >> >> -- >> Malcolm Cook >> Stowers Institute for Medical Research - Kansas City, MO USA >> >> >> "Mark" <asd[ at ]notMe.com> wrote in message news:sAxPf.73411$494.17985[ at ]newsfe2-gui.ntli.net... >>> Hi folks >>> >>> I probably shouldn't be posting this here but... >>> >>> Using Access XP / MSDE >>> >>> I am in the process of migrating a very complex mdb/mde to ADP. This will be a two stage process (due to urgency), the first >>> stage being migration of the backend and some core functionaility to MSDE/mde and the second migrating the remaining >>> functionality to MSDE/ade. >>> >>> I have a table which has a trigger set for insert/update which modifies one of it's own values based on various conditions. The >>> problem is that if I try to modify a record through a linked table in an mde I get a write conflict (even though I am defitely >>> the only person on the database). If I edit the record through Enterprise Manager directly I don't get an error at all. >>> >>> Anyone know how this can be avoided or if the error is just a symtom of something else entirely? >>> >>> Many thanks >>> >>> Mark >>> >> >> > >

Re: Edit conflicts between table and trigger "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> 21.03.2006 20:58:27 Thanks Malcolm...that may explain some of the odd behaviour I'm getting out of the ADP once in a while. Do I have to do a SET NOCOUNT OFF at the end, or will it reset it automatically? Rob "Malcolm Cook" <malcook[ at ]newsgroup.nospam> wrote in message news:uzFz0dHTGHA.5464[ at ]TK2MSFTNGP09.phx.gbl... [Quoted Text] > Robert, > > Failing to issue SET NOCOUNT ON results in ADP's confusing the row count > message as the result set. I can't put my finger on it any better, but > tahtis it in a nutshell. > > Cheers, > > malcolm > > "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> wrote in message > news:%23P1p2UKSGHA.2088[ at ]TK2MSFTNGP14.phx.gbl... >> Hi Malcolm, >> >> I don't use a lot of triggers, and the ones I do are mostly one-liners. >> Can you elaborate on the "set nocount on" issue a bit. I'm familiar with >> what it does, but why is it a concern in triggers? Do I need to turn >> nocount off at the end, or is state preserved after the trigger >> completes? >> >> To be honest, it's been so long since I designed my triggers, that I >> don't even remember if I did that, or knew to do that at the >> time...tomorrow I'll have to go back and check, of course. :) >> >> >> >> Thanks, >> Rob >> >> "Malcolm Cook" <malcook[ at ]newsgroup.nospam> wrote in message >> news:ObLa3QtQGHA.4920[ at ]tk2msftngp13.phx.gbl... >>> Mark, >>> >>> Things to try (in order): >>> >>> 1) make sure the trigger does a 'set nocount on' first thing >>> 2) add a timestamp to the table >>> 3) tell us what happens, and if it does not go way, then send the CREATE >>> TABLE statement, the error message, the text of the trigger >>> 4) maybe remove any bit fields on the table (recast them as smallint) >>> 5) ???? >>> >>> -- >>> Malcolm Cook >>> Stowers Institute for Medical Research - Kansas City, MO USA >>> >>> >>> "Mark" <asd[ at ]notMe.com> wrote in message >>> news:sAxPf.73411$494.17985[ at ]newsfe2-gui.ntli.net... >>>> Hi folks >>>> >>>> I probably shouldn't be posting this here but... >>>> >>>> Using Access XP / MSDE >>>> >>>> I am in the process of migrating a very complex mdb/mde to ADP. This >>>> will be a two stage process (due to urgency), the first stage being >>>> migration of the backend and some core functionaility to MSDE/mde and >>>> the second migrating the remaining functionality to MSDE/ade. >>>> >>>> I have a table which has a trigger set for insert/update which modifies >>>> one of it's own values based on various conditions. The problem is that >>>> if I try to modify a record through a linked table in an mde I get a >>>> write conflict (even though I am defitely the only person on the >>>> database). If I edit the record through Enterprise Manager directly I >>>> don't get an error at all. >>>> >>>> Anyone know how this can be avoided or if the error is just a symtom of >>>> something else entirely? >>>> >>>> Many thanks >>>> >>>> Mark >>>> >>> >>> >> >> > >