I have a table that gets records added to it ("orders" table) periodically throughout the day. When that happens, I'd like those records to get copied to another table (let's call it "Table B").
I wrote the following trigger, but it is not behaving as I thought it should. What's happening is, let's say there are 150 records in the table. The job that runs adds 30 new records, but Table B will have 100 records added to it instead of 30. It's almost as if it's adding records that currently exist in the "orders" table in addition to the ones that are newly inserted.
ALTER TRIGGER [dbo].[tr_copy_new_orders_to_520_522] ON [dbo].[orders]
SET NOCOUNT ON
IF IS_MEMBER ('db_owner') = 0
PRINT 'You cannot drop or alter tables.'
INSERT INTO _tmp_520_522_confirmations