Negative number of rows inserted

(re-wrote, misunderstood)

If you want the remote ID, then the best way is to use a stored procedure on the remote server and return a result set.

Or don't redirect into a temp table/table variable. This isn't in scope on the remote server

See these on SO:

• https://stackoverflow.com/questions/4537560/get-identity-from-another-serverlinked-server/4537600#4537600
• https://stackoverflow.com/questions/2621887/sql-server-identity-issue/2621940#2621940

Remember that this is a lite version of SQL and does not support all operations. See the following link:

https://www.sqlite.org/lang_createtrigger.html

SQLite supports a BEFORE TRIGGER which can delete rows before further action is taken. The link also provides some cautions about how that works:

If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row that was to have been updated or deleted, then the result of the subsequent update or delete operation is undefined. Furthermore, if a BEFORE trigger modifies or deletes a row, then it is undefined whether or not AFTER triggers that would have otherwise run on those rows will in fact run.

See the link to learn other details about the SQLite triggers.

Of course you could write your SQLite code to delete the rows that you no longer need in your database before inserting new rows into your database. I would prefer that approach over a trigger since your are just getting rid of unwanted data.