Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 28th, 2004, 03:03 PM
Registered User
 
Join Date: May 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default delay trigger until all records are inserted

I have a trigger that works fine, based on records being inserted into a table. The problem is thousands of records may be added to the table at a time, and I don't want the trigger going off that many times. Just once after the records are added would be fine. Is there any way to do this?


Reply With Quote
  #2 (permalink)  
Old May 28th, 2004, 03:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Then trigger cannot be of any help to you, as it runs for every insert/update/delete operation based on which it is supposed to run.

May be after every such bulk inserts, you might want to invoke a stored procedure to do that job.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection
Reply With Quote
  #3 (permalink)  
Old May 28th, 2004, 11:41 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Disable the trigger before the bulk insert, then after the records have been inserted, just run some code to do the tasks that the trigger would do.



Sal
Reply With Quote
  #4 (permalink)  
Old May 29th, 2004, 06:01 PM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cairo, , Egypt.
Posts: 336
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to alyeng2000
Default

Continue to What is Said--->
you can do as follows

ALTER TABLE trig_example DISABLE TRIGGER trig1
"Execute Insert Statments"
ALTER TABLE trig_example ENABLE TRIGGER trig1
"Execute What Trigger have to do for each of Inserted Statments"



Ahmed Ali
Software Developer
Reply With Quote
  #5 (permalink)  
Old June 1st, 2004, 12:07 PM
Registered User
 
Join Date: May 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks... looks like my best solution is to leave it as is since system users are adding the record via batch processes (generating purchase orders). It works, so I don't have to.

Reply With Quote
  #6 (permalink)  
Old March 26th, 2013, 07:17 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Work Around It

I had kind of a similar situation where before I processed the records inserted into the table with the trigger, I wanted to make sure all the relevant related data in relational tables was also there.

My solution was to create a scratch table which was populated by the insert trigger on the first table.

The scratch table had a updated flag, (default set to 0), and an insert get date() date field, and the relevant identifier from the main table.

I then created a scheduled process to loop over the scratch table and perform whatever process I wanted to perform against each record individually, and updating the 'updated flag' as each record was processed.

BUT, here is where I was a wee bit clever, in the loop over process looking for records in the scratch table that had a 'update flag = 0', I also added the AND clause of 'AND datediff(mi, Updated_Date, getdate())> 5'. So the record would not actually be processed until 5 minutes AFTER it was inserted into the scratch table.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
UPDATE TRIGGER, INSERTED and DELETED tables kpkirilov SQL Server 2005 1 June 11th, 2008 12:19 PM
Query to retrieve the first 10 records inserted anup_daware Oracle ASP 0 August 6th, 2007 02:50 AM
Double records inserted when using Insert Sach Classic ASP Databases 4 March 7th, 2006 02:29 PM
extracting last set of inserted records s_a_ravi Oracle 3 April 14th, 2005 03:14 AM
time delay pab006 Classic ASP Basics 2 January 29th, 2004 05:16 PM



All times are GMT -4. The time now is 11:25 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.