Solved

Insert Trigger in SQL

Posted on 2014-12-31
8
113 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Join vs where 2 38
DATETIMEOFFSET feature 1 32
the whoisactive update 12 38
Ssis not sending failure message 2 2
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Backup & Restore 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.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
A short film showing how OnPage and Connectwise integration works.

929 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

14 Experts available now in Live!

Get 1:1 Help Now