Retrieving multiple datatables from a tableadapter insert query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have in my database a trigger on a particular table that writes entries to
an audit table upon inserts & updates, and returns a set of AuditId's.

When I insert into this table, I need to retrieve both the set of AuditId's
and the scope_identity() of the newly-inserted row - but these are returned
in the form of two distinct result sets.

From all I see in the dataset GUI, and programmatically (being able to fill
a datatable with results but not a dataset), I can't tell if it's possible.

Any ideas? Thanks a lot!
 
Sure! The query goes like this:

--Main Query--

insert into MyTable ( myname ) values ( 'Stephen' );
select scope_identity() as myid;

--The on insert trigger--

select * from MyTable

And this returns the newly-inserted identity as a temporary relation, as
well as the entire MyTable, so I end up with two distinct sets of results.
The tableadapter is not trapping both - it only traps the trigger results!

Thanks for your help,

Stephen
 
Back
Top