Solved

How to fire trigger when loading data into a table using BULK INSERT?

Posted on 2012-04-11
13
2,205 Views
Last Modified: 2012-04-16
I have the following trigger:

USE [CDR_CUCM7_1_5]
GO

CREATE TRIGGER [dbo].[trgAfterInsert_CDR_Record] ON [dbo].[CDR] 
AFTER INSERT
AS
BEGIN
DECLARE @Epoch_Date Datetime


	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
SET @Epoch_Date = (SELECT i.dateTimeOrigination FROM inserted i)
update [dbo].[CDR] 
set HumanDATETIME=dbo.fn_Epoch2HumanDate(@Epoch_Date);


END

Open in new window


Which supposed to to update the coulmn HumanDATETIME (in the same table) with the inserted value of coulmn 'dateTimeOrigination' after converting it from epoch format to human readable format using my function dbo.fn_Epoch2HumanDate.

bearing in mind that i'm using only Bulk Insert to fill the CDR table, the trigger doesn't fire. How can I modify it to work for multiple record inserts (BULK INSERT)?

thanks in advance
0
Comment
Question by:zakwithu2012
13 Comments
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Firstly, your trigger is flawed - it only works for single row inserts, because of the
  SET @Epoch_Date = (SELECT i.dateTimeOrigination FROM inserted i)
line. You would have to change that anyway.

Secondly, do you really want to have the HumanDATETIME column set to the same value for ALL rows each time? Doesn't make much sense.

Thirdly, bulk insert does not allow for triggering at all. But you could change the trigger to an update trigger, and then issue an update on the CDR table (at least where the HumanDATETIME column is empty/NULL/whatever) after doing the Bulk Insert. On the other hand - you could call the correct update after Bulk Insert anyway.
0
 
LVL 2

Expert Comment

by:kamalranjan
Comment Utility
Hi,

It seems your code is not handling multiple records. See the line below which needs to be changed to get the output in a cursor instead of a variable:

SET @Epoch_Date = (SELECT i.dateTimeOrigination FROM inserted i)

And then process each record in that cursor.

Refer the links below for more info:

http://stackoverflow.com/questions/7983352/sql-server-trigger-not-executing-for-multiple-rows

http://stackoverflow.com/questions/407935/how-to-test-for-multiple-row-actions-in-a-sql-server-trigger

http://stackoverflow.com/questions/7258748/after-insert-trigger-looping

http://stackoverflow.com/questions/662010/handling-multiple-records-in-a-ms-sql-trigger


Kamal
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
Please see Solution from answer below for similar issue with one comment - if your BULK INSERT data set is huge you should expect to have a performance issue with the target table and if it is a online heavily used table you should think what the trigger does and maybe take a different approach like do it in a SQL job after the BULK INSERT instead of a AFTER INSERT trigger:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27635554.html
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
kamalranjan &  lcohan,

Please only post if you have to add something to the thread. My comment includes both of yours. And further, just providing a bunch of links does not help usually.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
I have been inprecise in one regard: Bulk operations usually disable triggers, but you can override that by supplying the switch FIRE_TRIGGERS in BULK INSERT (for details see http://msdn.microsoft.com/en-us/library/ms187640.aspx). You will still have to fix the "single-row" mentioned above.
0
 

Author Comment

by:zakwithu2012
Comment Utility
Thank you ALL Qlemo, kamalranjan and  lcohan...

Yes, I forgot to copy the WHERE part of the update query. It should be like this:

....
update [dbo].[CDR]
set HumanDATETIME=dbo.fn_Epoch2HumanDate(@Epoch_Date)
where dateTimeOrigination =@Epoch_Date ;
....

let me explain my main requirement you might find different approach rather than using triggers:

CDR table (98 Columns) is a very large table growing by about 70000 records/day ... data is being loaded from flat text files using bulk insert with file format
more details about this CDR table i posted here
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27640962.html

my problem now is there is one field 'dateTimeOrigination' comes with epoch date format which undesired for me. i created new column HumanDATETIME to save the converted datetime in it.Simply i want to convert the epoch date ON THE FLY while inserting and save it in the new HumanDATETIME column.
i create this user function which take care of converting from epoch to human format and it works fine (no need to spend effort on this).
Is there any way to achieve this?

Another approach which i tried to achieve this by setting default value in HumanDATETIME but it didn't work and i want your comment please on this:

I can normally put default value in  like getdate() any datetime fields and it works fine when inserting.
i did the same for my case ... i put the default value of the field 'HumanDATETIME' to be
dbo.fn_Epoch2HumanDate(dateTimeOrigination )

Open in new window


it didn't work.
can anyone tell me is it possible to play on default value as above and what is the required  correction to make it work?


thank you again
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
The default should work, as BULK INSERT honours that. But it requires that the data field in the source file is unoccupied or missing.
However, instead of using a trigger or default value, I recommend to perform:
   bulk insert ...
   update [dbo].[CDR] set HumanDATETIME=dbo.fn_Epoch2HumanDate(dateTimeOrigination)
where HumanDATETIME is null @Epoch_Date;

Open in new window

HumanDATETIME needs to be NULL for new rows to get it working.
0
 

Author Comment

by:zakwithu2012
Comment Utility
Hi
I didn't understand this:
where HumanDATETIME is null @Epoch_Date;

the part "where HumanDATETIME is null " is ok but this part "@Epoch_Date;" is not.
can you elaborate please.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Typo, sorry. The line ends with the "is null", the remainder is wrong:
   bulk insert ...
   update [dbo].[CDR] set HumanDATETIME=dbo.fn_Epoch2HumanDate(dateTimeOrigination)
where HumanDATETIME is null;

Open in new window

0
 

Author Comment

by:zakwithu2012
Comment Utility
i resolved my issue using the computed column.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
In which way does the accepted answer apply to "i resolved my issue using the computed column"?
0
 

Author Comment

by:zakwithu2012
Comment Utility
i got the idea from one of the provided links.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Could you please post an example for the knowledgebase and hence to help others with similar issues?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

771 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

11 Experts available now in Live!

Get 1:1 Help Now