Insert Trigger in SQL

Hello,

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] 
	FOR INSERT 
AS 

SET NOCOUNT ON

IF IS_MEMBER ('db_owner') = 0
BEGIN
   PRINT 'You cannot drop or alter tables.' 
   ROLLBACK TRANSACTION
END
ELSE
BEGIN
	INSERT INTO _tmp_520_522_confirmations
		SELECT OrderId,
			   OrderType,
			   BillToId,
			   ShipToId,
			   SalesRepJDEId,
			   OrderDate,
			   DateRequested,
			   DateRequestedUpdate,
			   CustomerPONumber,
			   HoldOrdersCode,
			   LastUpdated,
			   OpenLines,
			   TotalLines,
			   ShippingMethod,
			   PartialOrder
		FROM inserted
END

Open in new window

infotechelgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It looks like it should work.

Can you give an example of the new 30 rows added into Table A, and the new 100 rows added into Table B.
0
 
infotechelgAuthor Commented:
82 records were added in TABLE A today so far (note, this table has over 300,000 records in it).

26 new records were just added to that table (for a total of 108 added today). However, Table B (which was initially empty), now has 87 records instead of just the 26 that were added.

After doing a VLOOKUP, 61 of the 87 orders added into TABLE B were not even part of the original 108 in TABLE A that were added today.

Weird.

I'm going to keep digging into this one.

Here are the 26 new records added into TABLE A:
10315999
10316000
10316009
10316022
10316036
10316037
10316038
10316044
10316046
10316056
10316057
10316058
10316062
50050321
50050322
50050324
50050325
50050327
50050329
50050334
50050336
50050337
50050339
50050340
50050341
50050342
50050345
50050346
50050348
50050349
50050350
50050354
50050355
50050358
50050359
50050360
50050365
50050368
50050369
50050371
50050373
50050377
50050382
50050383
50050384
50050386
50050388
50050389
50050392
50050395
50050396
50050397
50050401
50050405
50050410
50050411
50050415
50050416
50050419

Open in new window


And the 87 added into TABLE B:
10315998
10315999
10316000
10316002
10316003
10316004
10316005
10316007
10316008
10316009
10316010
10316011
10316012
10316013
10316015
10316016
10316017
10316018
10316019
10316020
10316021
10316022
10316023
10316024
10316025
10316026
10316027
10316028
10316030
10316031
10316033
10316034
10316035
10316036
10316037
10316038
10316039
10316040
10316041
10316042
10316044
10316045
10316046
10316047
10316048
10316049
10316050
10316052
10316053
10316054
10316056
10316057
10316058
10316059
10316060
10316062
10316063
50050387
50050388
50050389
50050390
50050391
50050392
50050393
50050395
50050396
50050397
50050398
50050399
50050400
50050401
50050402
50050403
50050404
50050405
50050406
50050407
50050408
50050409
50050410
50050411
50050412
50050413
50050414
50050415
50050416
50050419

Open in new window

0
 
infotechelgAuthor Commented:
Crap, sorry. I posted the wrong records for the 26, but it's not letting me edit my comment for some reason.

Here are the 26 added:
10315999
10316000
10316009
10316022
10316036
10316037
10316038
10316044
10316046
10316056
10316057
10316058
10316062
50050388
50050389
50050392
50050395
50050396
50050397
50050401
50050405
50050410
50050411
50050415
50050416
50050419

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
infotechelgAuthor Commented:
Ugh, what's even more odd? I looked up 10315998 (one of the records added into TABLE B) in TABLE A, and it's not even in TABLE A anymore.

There's no way this trigger is MOVING records rather than COPYING them, is there?
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No.

I'm wondering if there is another trigger or stored procedure working then.

I would suggest adding a second trigger FOR DELETE and insert any deletes into a separate table so you can see what is going on.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
infotechelgAuthor Commented:
good idea. Thanks, Phillip.
0
 
Scott PletcherSenior DBACommented:
Do you really have everyone who INSERTs rows to a table as a full db_owner?  Yikes that's dangerous.

Be aware that if they are not db_owner, the INSERT will fail and cause the entire transaction to roll back.
0
 
infotechelgAuthor Commented:
This lead me down the path, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.