Bulk Copy does not fire trigger set in the table

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

Import/Export (DTS) and Replication (2000)

Bulk Copy does not fire trigger set in the table

Author

Topic

alvinaw8
Starting Member

1 Post

Posted - 2002-03-22 : 06:12:14

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 !"

Jay99

468 Posts

Posted - 2002-03-22 : 08:54:11

Bulk load techniques do not fire the triggers because they are non-logged operations. You'll have to find another way to do what you want . . .

Jay
<O>

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-22 : 13:00:29

you could always bcp into a temp table, then insert from the temp table into the table you want ...

setBasedIsTheTruepath
<O>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-22 : 13:03:39

How do you bcp into a temp table? A global temp table maybe, but I can't see bcp being able to do a temp table.

BULK INSERT could work with a temp table.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-22 : 13:06:19

oh right, i should have said bulk insert, that's what he's using.

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources