Re: What's wrong with this trigger

Tech-Archive recommends: Fix windows errors by optimizing your registry

By using the profiler on SQL-Server, you should see exactly what Access is
trying to do and how to solve it.

..NET has been designed expressively to solve this kind of problem; so it's
no wonder that you don't have this problem with your .NET applications.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


<mh> wrote in message news:OBcb98yTFHA.548@xxxxxxxxxxxxxxxxxxxxxxx
> Note also that my issue is not quite the same as I'm not getting the same
> error, but I think the underlying cause could be similar. My error is
> 'Key column information is insufficient or incorrect. Too many rows were
> affected by update.'
>
> <mh> wrote in message news:OaTiI4yTFHA.3140@xxxxxxxxxxxxxxxxxxxxxxx
>>I don't have that option in this case. The purpose of the downstream
>>tables is to update another application with changes that were made. The
>>table that has the trigger is control data, so the results that are moved
>>over to the changes table are not the records that are in the table, but
>>records that are affected by changing the table. I use an identity column
>>to have the records in sequential order of when they were created, so the
>>application that picks up the changes can get the latest change. I don't
>>understand why capturing the identity as Malcolm suggested, doesn't work.
>>The solution he had, had a typo, because there was no closing parenthesis,
>>but I fixed that and still have the same error. If it was up to me, I
>>would scrap the Access app at this point, but I don't have time for the
>>redesign as of yet. The ONLY reason I'm having this issue is because of
>>Access, and the app that uses this data is .NET. It's very frustrating,
>>especially since a legacy app is preventing me from moving forward.
>>
>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
>> wrote in message news:OhEtFyyTFHA.2872@xxxxxxxxxxxxxxxxxxxxxxx
>>> Yes, insert triggers that change the @@identity value cause many trouble
>>> with ADP. See
>>> http://support.microsoft.com/default.aspx?scid=kb;EN-US;275090 for
>>> example.
>>>
>>> As suggested by Jeff in another posting, the easiest solution is to
>>> remove the identity value from the historical tables; for example by
>>> using the same values for the primary key as the ones used in the
>>> original tables. Another solution would be to use your own stored
>>> procedure to create the insert and then resynchronise the form.
>>>
>>> --
>>> Sylvain Lafontaine, ing.
>>> MVP - Technologies Virtual-PC
>>>
>>>
>>> <mh> wrote in message news:Os1vHcyTFHA.3952@xxxxxxxxxxxxxxxxxxxxxxx
>>>>I meant to say "SET NOCOUNT ON"...
>>>>
>>>> <mh> wrote in message news:%23C$KlZyTFHA.336@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>I have a problem that I think might be related to this, where I have an
>>>>>update trigger that inserts an audit row into a view. It works fine
>>>>>from SQL Server Enterprise Manager, but when I go into the table
>>>>>through the ADP, it gives me the error 'Key column information is
>>>>>insufficient or incorrect. Too many rows were affected by update.' I
>>>>>tried the trick of SET NOCOUNT OFF and caching the identity, however I
>>>>>am still receiving the same error. Not sure what else to try! The
>>>>>primary key is in fact an identity and so is the view that I'm
>>>>>inserting the audit row into.
>>>>>
>>>>> Any help you can provide would be greatly appreciated!
>>>>> MH
>>>>>
>>>>> "Jeff via AccessMonster.com" <forum@xxxxxxxxxxxxxxxxx> wrote in
>>>>> message news:468ab38d96a242479d01dd4ab0790c3a@xxxxxxxxxxxxxxxxxxxx
>>>>>> It only occurs when the tigger is enables. I can't seem to get the
>>>>>> EXECUTE
>>>>>> command to work though.
>>>>>>
>>>>>> EXECUTE (N'SELECT Identity (Int, ' + Cast(@myID As Varchar(10)) + ' ,
>>>>>> 1) AS
>>>>>> id
>>>>>> INTO #Tmp'
>>>>>>
>>>>>> What exactly does this do?
>>>>>>
>>>>>> --
>>>>>> Message posted via http://www.accessmonster.com
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Whats wrong with this trigger
    ... >table that has the trigger is control data, so the results that are moved ... >would scrap the Access app at this point, but I don't have time for the ... and the app that uses this data is .NET. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Whats wrong with this trigger
    ... the app that uses this data is .NET. ... > same values for the primary key as the ones used in the original tables. ... >>>update trigger that inserts an audit row into a view. ... >>>identity and so is the view that I'm inserting the audit row into. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Determine when App Receives Focus
    ... What would trigger this code? ... But assuming the IsActive tells you if the app has ... So, I implemented a timer that checks every 1 second, using debug.print ... without the loop, 0%. ...
    (microsoft.public.vb.general.discussion)
  • Re: Whats wrong with this trigger
    ... I actually had SET NOCOUNT OFF at the top, ... >>table that has the trigger is control data, so the results that are moved ... >>would scrap the Access app at this point, but I don't have time for the ... and the app that uses this data is .NET. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Write conflict error after adding trigger to table
    ... another app to pick up from another database. ... an insert against the view when the resulting where clause ends up with no ... >> checking if exists before inserting through the trigger fixed this issue. ... >>> Sylvain Lafontaine, ing. ...
    (microsoft.public.access.adp.sqlserver)