Triggers, @@Identity and TADOQuery

From: Andrew Venmore (avenmoreatananzidotcodotza)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 13:10:23 +0200

Hi All

I am trying to set up simple auditing functionality for my users on
MSSQL using the brute-force, trigger way.

I create a trigger on MyTable which writes records into MyAuditTable.
I soon learn not to use/return @@Identity in my stored procs as it
contains the identity of the last record written (in MyAuditTable),
but rather to use scope_identity() or ident_current('MyTable').

I have now also discovered that using TADOQuery.Append/Post also
presents a problem. In the following scenario:

MyQuery.Append;
MyQuery.FieldByName('SomeField').AsString := 'Something';
MyQuery.Post;

MyQuery.FieldByName('MyTableID').AsInteger will now have the wrong
value (the ID of MyAuditTable).

Observing the throughput with SQL Profiler, I see that the data is
appended, then "SELECT @@IDENTITY" is called, which I presume is used
to update the ID field in the dataset.

Stepping into ADODB.pas, I find that the above commands are issued
from within MSADO15.DLL (RecordSet.Update()).

Does anyone know if there is any way to alter this behaviour, or if
there is any possible solution/workaround (besides not using
append/post).

Thanks for any input.
Andrew Venmore
[avenmoreatananzidotcodotza]

Relevant Pages

  • Re: Synchronisation MSSQL -> Postgres
    ... einbinden und direkt in einem Trigger oder einer stored procedure ... ODBC' klappt auch, ich habe mich beim Test an folgender Beschreibung ... kennt jemand eine gute Beschreibung für Trigger in MSSQL (für ...
    (de.comp.datenbanken.misc)
  • Re: trigger each row
    ... > into the trigger instead? ... It's a pitty that MSSQL doesn't support row-level triggers at all. ...
    (microsoft.public.sqlserver.programming)
  • Performance question on triggers
    ... MSSQL 2000 Given a table ... x,a,b,c float ... by using a trigger: ... set nocount off ...
    (microsoft.public.sqlserver.programming)
  • Re: Creating a trigger in Pervasive SQL 2000i
    ... I've looked at the manual and created a trigger. ... In MSSQL, when I create a trigger I can see the file ... But in psql I can't find it. ... the pop-up window said "Stored Procedure was succesfully ...
    (comp.databases.btrieve)