Trigger Question 1

[tlaksh]

Hi,

I have a DTS that runs and inserts records to a table in SQL Server 7.0. This table has a trigger attached to it that fires on insert.

The trigger checks for the value in a column in the newly inserted rows and based on the result, executes the sp_sendmail procedure

Now, my problem is that this trigger works when i manually insert records from the query analyser and sends respective emails. But it does not send mails when the rows are inserted through the DTS.

Does anybody have a clue as to why this is happening. I have started the SQL mail server too.

Please help...Thanks in advance.

 

[Tim1]

Hiya,

It could be quite simple....how does DTS insert rows? If it uses an INSERT command, the trigger will fire. If it taps into the BCP command (bulk copy rows), this will not fire a trigger.

HTH

Tim

 

[tlaksh]

Hi Tim its me again.....

How can know as to what the DTS is using..Basically i am converting a text file data into table data through the dts.

Thanks again.

Laksh

 

[Tim1]

Hi Laksh,

I must confess that I am not familiar with DTS. Is is a program that you have purchased, or something that somebody has written for you?

If it is taking a large text file and importing it into a Sybase table, I suspect that it is using BCP to do so, but if it is a program that you have purchased, contact their techie's and ask exactly what it does, specifically if it uses BCP.

If it does, I am afraid that you are going to have to write an audit program, as there is no way to make triggers fire with BCP.

Tim

 

[fluteplr]

In the DTS task that does the load from the text file look at the options. If fast load is checked the DTS uses BCP to shove the data in bypassing things like triggers. You can uncheck the box an the trigger will run, but the performance may be unacceptable. You may want to use the fast load option and then fire a stored procedure that sends your emails. I have gone both routes in the past depending on the performance issues.

 

[tlaksh]

Hi fluteplr ...oops sorry no name mentioned in the mail

But anyways thanks a lot ...the solution worked..

Laksh