Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insert Trigger in SQL

Posted on 2014-12-31
8
Medium Priority
?
123 Views
Last Modified: 2015-02-11
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

0
Comment
Question by:infotechelg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40525560
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
 

Author Comment

by:infotechelg
ID: 40525697
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
 

Author Comment

by:infotechelg
ID: 40525702
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:infotechelg
ID: 40525714
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40525946
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
 

Author Comment

by:infotechelg
ID: 40525970
good idea. Thanks, Phillip.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40531819
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
 

Author Closing Comment

by:infotechelg
ID: 40604172
This lead me down the path, thanks.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question