Solved

Insert Trigger in SQL

Posted on 2014-12-31
8
109 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
  • 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 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 69

Expert Comment

by:ScottPletcher
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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 seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now