February 9, 2006 at 4:03 pm
Hi all
I have been ramming my head against a wall trying to get temporary tables or table variables to work within a trigger
I have tried
@h1 table(SW_MEMBER int,WKT text) which throws errors.
I have also tried
create table #h1(SW_MEMBER int, WKT text) which doesn't work as well.
I have looked through the documentation and can find no reference to triggers not being able to use these tables, so I am stumped.
What am I doing wrong?
February 9, 2006 at 4:07 pm
You need the DECLARE keyword with @table variables:
DECLARE @h1 table (SW_MEMBER int,WKT text)
February 9, 2006 at 4:15 pm
Sorry, just typed it wrong in here.
I did have
declare @h1 table(SW_MEMBER int, WKT text)
February 9, 2006 at 4:22 pm
February 9, 2006 at 4:23 pm
If I run this:
create table test
( RowID int )
go
Create trigger tr_test on test for insert as
begin
declare @h1 table(SW_MEMBER int,WKT text)
end
... it doesn't error. Maybe the error is coming from a different part of your code. What is the error, BTW ?
February 9, 2006 at 4:41 pm
OK. Here is the trigger code. Got to promise not to laugh though, it is not pretty and is slow, but it does what I need it to. I am in the process of fixing it up.
The error I get when trying to run in query analyzer is
"Server: Msg 156, Level 15, State 1, Procedure hazards_wkt_tr, Line 5
Incorrect syntax near the keyword 'table'."
I haven't written anything to the geoType variable yet, I was hoping to get that from the temporary table, so there is some code missing there.
ALTER trigger hazards_wkt_tr
on dbo.HAZARDS_GDA94AREAS
for insert,update AS
declare @id int, @textptr varbinary(16), @patternPtr int, @geoType char(30), @h1 table(SW_MEMBER int, WKT text), @h2 table(SW_MEMBER int, WKB image),
IF UPDATE(SW_GEOMETRY)
begin
while 1 = 1
BEGIN
select top 1 @id =SW_MEMBER
from inserted
where @id is null or SW_MEMBER > @id
order by SW_MEMBER
if @@rowcount < 1 break
EXEC('Insert INTO @h1 EXEC sp_spatial_query ''Select SW_MEMBER, HG_AsText(sw_geometry) as WKT from HAZARDS_GDA94AREAS where SW_MEMBER = ' + @id +'''')
EXEC('Update HAZARDS_GDA94AREAS SET WKT = @h1.WKT FROM @h1 where @h1.SW_MEMBER = HAZARDS_GDA94AREAS.SW_MEMBER')
if @geoType = 'Polygon'
BEGIN
while 1 = 1
Begin
Select
@textptr = textptr(WKT),
@patternPtr = patindex('%POLYGON%',WKT)-1
from @h1
where SW_MEMBER = @id
if @patternPtr < 1 break
updatetext @h1.WKT @textptr @patternPtr 7
end
select @textptr = textptr(WKT)
From @h1
where SW_MEMBER = @id and WKT like 'GEOMETRYCOLLECTION%'
if @@rowcount > 0
updatetext @h1.WKT @textptr 0 18 'MULTIPOLYGON'
END
if @geoType = 'Polyline'
BEGIN
while 1 = 1
Begin
Select
@textptr = textptr(WKT),
@patternPtr = patindex('%LINESTRING%',WKT)-1
from @h1
where SW_MEMBER = @id
if @patternPtr < 1 break
updatetext @h1.WKT @textptr @patternPtr 10
end
select @textptr = textptr(WKT)
From @h1
where SW_MEMBER = @id and WKT like 'GEOMETRYCOLLECTION%'
if @@rowcount > 0
updatetext @h1.WKT @textptr 0 18 'MULTILINESTRING'
END
if @geoType = 'Point'
BEGIN
while 1 = 1
Begin
Select
@textptr = textptr(WKT),
@patternPtr = patindex('%POINT%',WKT)-1
from @h1
where SW_MEMBER = @id
if @patternPtr < 1 break
updatetext @h1.WKT @textptr @patternPtr 5
end
select @textptr = textptr(WKT)
From @h1
where SW_MEMBER = @id and WKT like 'GEOMETRYCOLLECTION%'
if @@rowcount > 0
updatetext @h1.WKT @textptr 0 18 'POINT'
while 1 = 1
Begin
Select
@textptr = textptr(WKT),
@patternPtr = patindex('%((%',WKT)-1
from @h1
where SW_MEMBER = @id
if @patternPtr < 1 break
updatetext @h1.WKT @textptr @patternPtr 2 '('
end
while 1 = 1
Begin
Select
@textptr = textptr(WKT),
@patternPtr = patindex('%))%',WKT)-1
from @h1
where SW_MEMBER = @id
if @patternPtr < 1 break
updatetext @h1.WKT @textptr @patternPtr 2 ')'
end
END
EXEC('Insert INTO @h2 EXEC sp_spatial_query ''Select SW_MEMBER, HG_AsBinary(HG_GeometryFromText(WKT)) as WKB FROM @h1 where SW_MEMBER = ' + @id +'''')
EXEC('Update HAZARDS_GDA94AREAS SET WKB = @h2.WKB from @h2 WHERE @h2.SW_MEMBER = HAZARDS_GDA94AREAS.SW_MEMBER')
EXEC('Update HAZARDS_GDA94AREAS SET WKT = @h1.WKT from @h1 WHERE @h1.SW_MEMBER = HAZARDS_GDA94AREAS.SW_MEMBER')
END
end
Cheers
james
February 9, 2006 at 4:58 pm
You can't declare tables in the same declare statement as other tables/variables.
declare @id int,
@textptr varbinary(16), @patternPtr int,
@geoType char(30)
Declare @h1 table(SW_MEMBER int, WKT text)
Declare @h2 table(SW_MEMBER int, WKB image)
February 9, 2006 at 5:13 pm
You little beauty.
I read just about everything I could find on table variables, and no where did I see mentioned you have to specifically define in a new declare statement.
Thanks everyone for their help
February 9, 2006 at 5:23 pm
Although having solved that, I think all those "While 1 = 1" infinite loops are going to be slightly more of a problem ...
February 9, 2006 at 5:28 pm
Yeah, just found that.
The problem is I need to update the text in the WKT field. The field is type TEXT and I can't get around this. I need to go through the whole text value and replace every occurace of certain characters. I have to do this on every inserted / updated row (as long as SW_GEOMETRY is altered)
The infinite loops close when the cursor gets to the end of the text field. It looks as though table variables can't be used in cursors though.
If you have any ideas of a more elegant solution I would be most interested.
Cheers
February 9, 2006 at 5:36 pm
Actually, the table variable cannot be used in the updatetext statement.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply