Trigger only fires on last row of INSERT from derived table

#1

DECLARE result CURSOR FOR
SELECT address, boro FROM Address WHERE address_id =
@@IDENTITY

This will get the row for the current value of @@identity
which will be the last row entered.
The trigger is fired once for the insert statement.
To run for each value loop though the inserted table
(which will also have an address_id so you don't need the
cursor).

Quote:

-----Original Message-----
I have an AFTER INSERT trigger that works fine except
with
my DTS package. When executing a stored proc that does
an
insert from a derived table the trigger only fires for
the
last row.

My DTS pakage has the following 3 steps:

1. Bulk load data from text file into temporary table
load_nys_doh

2. Run the following stored proc:

CREATE PROCEDURE load_nys_doh_sp AS

INSERT INTO Address
SELECT straddr, city, state, zip, borough, sourceadd,
sourcecity, sourcest,
sourcezip, '', '', '', '', '', '', '', '', '', '', ''
FROM load_nys_doh

GO

3. TRUNCATE TABLE load_nys_doh

This is the trigger:

-----------------BEGIN TRIGGER----------------------
CREATE TRIGGER geo_support
ON Address
AFTER INSERT

AS

DECLARE @object int
DECLARE @hr int
DECLARE @values varchar(1000)
DECLARE @address varchar(200)
DECLARE @boro char(2)
DECLARE @i int
DECLARE @count int
DECLARE @geo_bbl varchar(10)
DECLARE @geo_bin varchar(10)
DECLARE @geo_street_number varchar(10)
DECLARE @geo_street_name varchar(100)
DECLARE @geo_boro_number char(1)
DECLARE @geo_boro_name varchar(50)
DECLARE @geo_zip varchar(10)
DECLARE @x varchar(50)
DECLARE @y varchar(50)
DECLARE @geo_error_code varchar(10)
DECLARE @geo_error_msg varchar(200)

PRINT 'here we go'

--create geo support AcitveX component

EXEC @hr = sp_OACreate 'pcgeoAXdll.axGeo', @object OUT
IF @hr = 0
PRINT 'sp_OACreate'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END

--get the just insert values to pass to geo support

DECLARE result CURSOR FOR
SELECT address, boro FROM Address WHERE address_id =
@@IDENTITY

OPEN result

FETCH NEXT FROM result
INTO @address, @boro
PRINT '@address=' + @address + ' @boro=' + @boro

CLOSE result
DEALLOCATE result

--if you want to activate axGeo debugging uncomment the
following section
/*
EXEC @hr = sp_OASetProperty @object, 'DebugSetting', 1
IF @hr = 0
PRINT 'sp_OASetProperty DebugSetting'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
*/
--set ActiveX Address property

EXEC @hr = sp_OASetProperty @object, 'Address', @address
IF @hr = 0
PRINT 'sp_OASetProperty Address'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END

--set ActiveX Boro property

EXEC @hr = sp_OASetProperty @object, 'Boro', @boro
IF @hr = 0
PRINT 'sp_OASetProperty Boro'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END

--execute ActiveX NormalizeAndGeoCode

EXEC @hr = sp_OAMethod @object, 'NormalizeAndGeoCode',
@values OUT
IF @hr = 0
PRINT 'sp_OAMethod'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END

--cludgey loop through return values

PRINT @values

SET @i = CHARINDEX('|', @values)
SET @count = 0

WHILE (@i > 0)
BEGIN
SET @count = @count + 1
PRINT @count
PRINT SUBSTRING(@values, 1, @i - 1)

IF @count = 1
SET @geo_error_code = SUBSTRING(@values, 1, @i - 1)
IF @count = 2
SET @geo_error_msg = SUBSTRING(@values, 1, @i - 1)
IF @count = 3
SET @geo_street_number = SUBSTRING(@values, 1, @i - 1)
IF @count = 4
SET @geo_street_name = SUBSTRING(@values, 1, @i - 1)
IF @count = 5
SET @geo_boro_number = SUBSTRING(@values, 1, @i - 1)
IF @count = 6
SET @geo_boro_name = SUBSTRING(@values, 1, @i - 1)
IF @count = 7
SET @geo_zip = SUBSTRING(@values, 1, @i - 1)
IF @count = 8
SET @geo_bbl = SUBSTRING(@values, 1, @i - 1)
IF @count = 9
SET @geo_bin = SUBSTRING(@values, 1, @i - 1)
IF @count = 10
SET @x = SUBSTRING(@values, 1, @i - 1)

SET @values = SUBSTRING(@values, @i + 1, LEN(@values) -
@i)
SET @i = CHARINDEX( '|', @values)

IF @i = 0
SET @y = @values

END

--add the appropriate values

UPDATE Address
SET geo_street_number = @geo_street_number,
geo_street_name = @geo_street_name,
geo_bbl = @geo_bbl, geo_bin = @geo_bin, geo_boro_number
= @geo_boro_number,
geo_boro_name = @geo_boro_name, geo_zip = @geo_zip,
x_coord = @x, y_coord = @y,
geo_error_code = @geo_error_code, geo_error_msg =
@geo_error_msg
WHERE address_id = @@IDENTITY

GO
-- -------------------END TRIGGER----------------------

.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Trigger only fires on last row of INSERT from derived table

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Trigger only fires on last row of INSERT from derived table - 09-18-2003 , 06:40 PM