Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 28th, 2004, 02:03 PM
Registered User
 
Join Date: May 2004
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?


 
Old May 28th, 2004, 02:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
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
 
Old May 28th, 2004, 10:41 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
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
 
Old May 29th, 2004, 05:01 PM
Friend of Wrox
 
Join Date: Oct 2003
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
 
Old June 1st, 2004, 11:07 AM
Registered User
 
Join Date: May 2004
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.

 
Old March 26th, 2013, 06:17 AM
Registered User
 
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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
UPDATE TRIGGER, INSERTED and DELETED tables kpkirilov SQL Server 2005 1 June 11th, 2008 11:19 AM
Query to retrieve the first 10 records inserted anup_daware Oracle ASP 0 August 6th, 2007 01: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 02:14 AM
time delay pab006 Classic ASP Basics 2 January 29th, 2004 05:16 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.