March 9, 2009 at 6:44 am
hi i have created one trigger for example on table1
trigger is for insert and after insert i m doing some calculation on that data
so when i m inserting one by one data into table1 trigger runs completely
but when imporing data from another table than it bypass this trigger or trigger not fire ....
anybody know abt this strange behaviour than pls tell me
Raj Acharya
March 9, 2009 at 6:58 am
I would start looking down this path
http://msdn.microsoft.com/en-us/library/ms187640(SQL.90).aspx
How are you bulk inserting the data?
March 9, 2009 at 7:20 am
Raj,
This is the third time I've seen this post and the second one I've seen that has answers. Please only post once so we don't duplicate answers across threads.
The other post that has an answer is likely your problem.
If you post the trigger code, we will be able to post more accurate solutions or suggestions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 9, 2009 at 7:43 am
Hi by mistake i have do two post so i m going to unsubscribe from one ....
Raj Acharya
March 9, 2009 at 7:46 am
hi but i m not importing data through bulk insert actually ... i m doing that through import and export wizard .....
so can u pls help out me for this
Raj Acharya
March 9, 2009 at 7:53 am
Like I asked earlier, can you post the trigger? Without seeing the code anything we say would be a guess. The main idea is that you probably are using variables in the trigger so it will only handle a single row while SQL Server works on sets. Checkout this article[/url]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 9, 2009 at 8:11 am
ALTER TRIGGER [dbo].[tr_insert]
ON [dbo].[table1]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @i INT
SELECT @i = COUNT(*) FROM inserted
SELECT @i
IF (@i > 1)
BEGIN
inser into table2
select * from table1 where date not in (select date from table2)
end
IF (@i = 1 )
begin
INSERT INTO [dbo].[table1]
([ReportDate]
,[EmployeeID]
,[CreatedDate]
,[CreatedBy])
select * from inserted
end
Raj Acharya
March 9, 2009 at 8:48 am
Did you check the link I posted above? I think you need to specify the fire_triggers. Setup your Import/Export job, save to disk (do not run). Open it in visual studio and go into the data flow task. Click on the destination server and (in the Properties section) add FIRE_TRIGGERS to the "FastLoadOptions" property.
This may be your issue.
March 9, 2009 at 9:44 am
Okay, I have to ask, what are you trying to accomplish with this trigger?
March 9, 2009 at 9:53 am
raj acharya (3/9/2009)
hi but i m not importing data through bulk insert actually ... i m doing that through import and export wizard .....so can u pls help out me for this
Import/Export uses either bcp or BULK INSERT (can't recall which) which does not fire triggers by default.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2009 at 3:41 am
Hi raj
I had a same problem as you and I solved the problem in SSIS using 'Data access mode' 'Table or view' and not 'Table or view - fast load'.
I think if you use the fast load option that the trigger isn't fired
Regards
April 14, 2009 at 4:51 am
jamie (3/9/2009)
Did you check the link I posted above? I think you need to specify the fire_triggers. Setup your Import/Export job, save to disk (do not run). Open it in visual studio and go into the data flow task. Click on the destination server and (in the Properties section) add FIRE_TRIGGERS to the "FastLoadOptions" property.This may be your issue.
Can you explain in more details? I checked that MSDN page, I don't,actually want to use the bcp utility to import the data(tried but was unable to do so) I want to use the import wizard. So I would greatly appreciate if you would detail on how I can setup the Import/Export job file, no idea how to get there and all the other steps you mentioned in there, just presume I don't know any information on how to do that.
Thank you
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply