June 23, 2015 at 1:29 pm
hello,
i am setting up a process for the dataload, external windows service insert a record to dbo.dataload table with a status as 'Ready'
create table dbo.dataload
(Xmlcol xml
,Loadstatus varchar(15)
,Starttime datetime
,completedtime datetime)
now i use a SQL job to check the status column, pick xml and load to appropriate tables. instead of SQL job can i use a trigger whenever there is a insert it fires sp to load data would there be any disadvantage. or any other solution that could be better
June 24, 2015 at 1:47 pm
mxy (6/23/2015)
hello,i am setting up a process for the dataload, external windows service insert a record to dbo.dataload table with a status as 'Ready'
create table dbo.dataload
(Xmlcol xml
,Loadstatus varchar(15)
,Starttime datetime
,completedtime datetime)
now i use a SQL job to check the status column, pick xml and load to appropriate tables. instead of SQL job can i use a trigger whenever there is a insert it fires sp to load data would there be any disadvantage. or any other solution that could be better
While you could use a trigger, you would need the trigger to handle any number of inserted records. If the procedure took any amount of time, and another insert came in, you might have the trigger firing a second time before the first one is complete, which could be rather messy. Sometimes, a batch-based methodology is better than a trigger, simply because it has the time to deal with whatever showed up in a given timeframe, whereas a trigger has to deal with ALL inserts, no matter how many are taking place in a single INSERT statement.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 24, 2015 at 4:55 pm
thanks for ur response. if thousand records fire at a time it would cause blocking or does it cause triggers to fail.
2) what would you suggest a sql job to check status col every minute ?
June 24, 2015 at 6:20 pm
mxy (6/24/2015)
thanks for ur response. if thousand records fire at a time it would cause blocking or does it cause triggers to fail.2) what would you suggest a sql job to check status col every minute ?
A trigger fires once for each INSERT statement, which does not necessarily mean once for each record. If you have 1,000 users each invoking 1 INSERT statement within a short enough timeframe, you might well have the trigger running concurrently with itself.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 24, 2015 at 6:43 pm
firing 1000 times do you think would that be a problem or any better alternative solution would you suggest
June 24, 2015 at 7:18 pm
mxy (6/24/2015)
firing 1000 times do you think would that be a problem or any better alternative solution would you suggest
If you have 1,000 users firing off INSERT statements in a relatively short time-frame (as in less than a minute), then a trigger is the wrong solution to begin with.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply