Problem with Fire trigger statement while using BULK COPY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with Fire trigger statement while using BULK COPY

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-27 : 11:17:31

Alvin writes "Dear all

I need some help.

This is what our user want to activate each time this scenario happen :
User will input record into the flat file.
The system will then insert the record from this flat file into the temp table
The trigger in the temp table will then fire to create 9 new records in another table
in the system.
This process will facilate the user from entering 1 single record one time rather then 9 times into
the off the shelf application we have which is running on SQL 7.0

What I do was schedule this job to run each time the user have new entry request:

Insert into the temp table based on the flat file in server.
BULK INSERT TRAINING.dbo.[HYP_TBM_RATE_FEE]
FROM 'D:\CMSOpen4\Report\test.txt'
WITH ( FIELDTERMINATOR = '|' )

This statement will work fine except it does not fire the trigger set in the above table ? what went wrong ? I understand from the Web that there is is statement call fire_trigger which can be used in this BULK COPY syntax. But it seem not supported in SQL 7.0 as if I went to T-SQL help, this syntax is not there, is this true ?

I have tested the trigger set in this table was working as if I insert a single record manually using Query Analyzer, the trigger will fire & 9 records will be added into the desired table.

The Trigger set in the above table was :
/*

Author : Alvin AW
Date : 22 Mar 2002

Trigger that inserts data into HYP_TBM_RATE_FEE table
This is to facilitate the inputting of 9 records using the Application front-end module
The script will insert 9 record based on any individual records added into HYP_TBM_RATE_FEE table.
This script will check for exitense of matter_uno in TBM_RATE_FEE before creating the new record.
if record found, it will update this table with trigger_flag = 'Z' & date_trigger will be today date.
If no record found, it will create 9 records in TBM_RATE_FEE & update last_key field in
CMS_UNIQUE_TABLE based on the last row_uno used.

*/

CREATE trigger INS_HYP_TBM_RATE_FEE
on dbo.HYP_TBM_RATE_FEE for INSERT
AS BEGIN
set xact_abort on
Set ANSI_NULLS ON
Set ANSI_WARNINGS ON

/* Temp Variable Used for the Trigger */

declare @var_clnt_matt_code varchar(21)
declare @var_matter_uno integer
declare @counter integer
declare @last_key integer
declare @rec_exist integer

SELECT @var_clnt_matt_code = HYP_CLNT_MATT_CODE from inserted

SELECT @var_matter_uno = matter_uno from HBM_MATTER
WHERE CLNT_MATT_CODE = @var_clnt_matt_code

SELECT @rec_exist = 0

SELECT @rec_exist = COUNT(*) from TBM_RATE_FEE
WHERE MATTER_UNO = @var_matter_uno

IF (select TRIGGER_FLAG from inserted) = 'N' AND @rec_exist =
BEGIN
SELECT @counter = 1
WHILE @counter < 10
BEGIN
SELECT @last_key = lastkey from
CMS_UNIQUE_KEYS
WHERE TBNAME ='TBM_RATE_FEE'
INSERT INTO TBM_RATE_FEE VALUES
(@last_key + 1, '1', @var_matter_uno, 0, 0, '3', 0, @counter, 'Y', ' ', 'Y', ' ', 0, getdate(), 0, -100, 'SGD','N',getdate() )

UPDATE CMS_UNIQUE_KEYS
SET lastkey = @last_key + 1
WHERE tbname = 'TBM_RATE_FEE'

SET @counter = @counter + 1

END

UPDATE HYP_TBM_RATE_FEE
SET date_trigger = getdate(),
trigger_flag = 'Y',
comment = 'Rec updated without problem - ' + convert(char(10), @var_matter_uno)
WHERE HYP_CLNT_MATT_CODE = @var_clnt_matt_code
AND TRIGGER_FLAG <> 'Y'

END
END

/* END of trigger */

Please advise !
Thanks !"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-27 : 11:23:52

you may need to bulk insert into a temp table, and then insert from the temp table into the true destination table.

setBasedIsTheTruepath
<O>

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-03-27 : 12:04:36

I agree with Set Based, but it seems like FIRE_TRIGGERS should work according to the SQL 2000 BOL.

Try this:
BULK INSERT TRAINING.dbo.[HYP_TBM_RATE_FEE]
FROM 'D:\CMSOpen4\Report\test.txt'
WITH ( FIELDTERMINATOR = '|', FIRE_TRIGGERS )

quote:

All bulk copy operations (the BULK INSERT statement, bcp utility, and the bulk copy API) support a bulk copy hint, FIRE_TRIGGERS. If FIRE_TRIGGERS is specified on a bulk copy operation that is copying rows into a table, INSERT and INSTEAD OF triggers defined on the destination table are executed for all rows inserted by the bulk copy operation. By default, bulk copy operations do not execute triggers.

These considerations apply to bulk copy operations that specify FIRE_TRIGGERS:

Bulk copy operations that would usually be minimally logged are fully logged.

Triggers are fired once for each batch in the bulk copy operation. The inserted table passed to the trigger contains all of the rows inserted by the batch. Specify FIRE_TRIGGERS only when bulk copying into a table with INSERT and INSTEAD OF triggers that support multiple row inserts.

No result sets generated by the insert triggers are returned to the client performing the bulk copy operation.

Jay99

468 Posts

Posted - 2002-03-27 : 12:09:41

Is there and echo in here?

EDIT: The funniest part is that setBasedIsTheTruepath provided almost the exact same answer twice and he didn't even notice :) . . .

Jay
<O>

Edited by - Jay99 on 03/27/2002 12:11:33

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-27 : 13:01:47

quote:

The funniest part is that setBasedIsTheTruepath provided almost the exact same answer twice and he didn't even notice :) . . .

doh. my mind must be going ...

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources