SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Bulk Copy does not fire trigger set in the table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alvinaw8
Starting Member

1 Posts

Posted - 03/22/2002 :  06:12:14  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/22/2002 :  08:54:11  Show Profile  Reply with Quote
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>
Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

USA
992 Posts

Posted - 03/22/2002 :  13:00:29  Show Profile  Reply with Quote
you could always bcp into a temp table, then insert from the temp table into the table you want ...

setBasedIsTheTruepath
<O>
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 03/22/2002 :  13:03:39  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

USA
992 Posts

Posted - 03/22/2002 :  13:06:19  Show Profile  Reply with Quote
oh right, i should have said bulk insert, that's what he's using.

setBasedIsTheTruepath
<O>
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000