March 23, 2011 at 9:43 pm
Hey guys, i'm reasonably new to triggers and i must be missing something 🙂
I've created a stored procedure which does some funky string manipulations and returns a varchar.
I want to call this inside a trigger, so every time a string is entered, it gets "cleaned" and put in the table.
ALTER TRIGGER [dbo].[Test_Processing_trigger]
ON [TestDB].[dbo].[Test_table]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @IP varchar(200)
set @IP = (select IP from INSERTED)
declare @NewIP varchar(200)
exec @NewIP = dbo.test_stored_procedure @IP
begin
updatea
set IP = @NewIP
fromdbo.Test_table a
joininserted b
ona.key = b.key
end
END
now, every time i enter a value in the table, it replaces it with 0.
but if i run the sp by iteself, i get 10.10.10.10
eg:
declare @NewIP varChar(200)
declare @IP varchar(200)
set @IP = '9.9.9.9,11.11.11.11,10.10.10.10,ff00::0000:aaaa:bbbb:0000'
exec @NewIP = dbo.sp_Woodside_String_IP_Selection_V1 @IP
Results: 10.10.10.10
(as desired)
Any ideas why the trigger is returning 0?
i could understand if it was NULL or an error... but not 0 ??
March 23, 2011 at 11:53 pm
Might help if you also post the code for the stored procedure so we can see what it is doing.
March 24, 2011 at 4:23 am
exec @NewIP = dbo.sp_Woodside_String_IP_Selection_V1 @IP
The syntax you've used there populates @NewIP with the return code from the stored procedure (or 0 if you have no RETURN statement in the procedure)
I suspect what you should be doing is returning the new ip address as an output parameter, a bit like this
exec dbo.sp_Woodside_String_IP_Selection_V1 @IP, @NewIP output
But, as Lynn said, we need to see the code for the stored procedure to really help.
March 24, 2011 at 7:33 pm
lol at me changing the the name of the sp in the first code block and not the second one... *sigh*.
anyways, i noted that i should have an OUTPUT in the sp and gave that a shot, now i get a conversion error:
"Conversion failed when converting the varchar value '10.10.10.10' to data type int."
(whether i run it from query window or in the trigger)
Not entirely sure i setup the output properly, but i can't see anywhere where I've used an INT?
Thanks for your assistance guys.
Code for sp:
(the error references the CHARINDEX line near the bottom.)
USE [Test_db]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
-- Description:
-- Purpose:
-- Author:
-- Date: 20100323
--
ALTER PROCEDURE [dbo].[test_stored_procedure]
-- Add the parameters for the stored procedure here
@parameter varchar(200),
@NewIP varchar(200) output
AS
BEGIN
--===== Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(200) --The string value of the element
)
--===== Suppress the auto-display of rowcounts to keep them from being
-- mistaken as part of the result set.
SET NOCOUNT ON
--===== Add start and end , to the Parameter so we can handle
-- single elements
SET @Parameter = ','+@Parameter +','
--===== Join the Tally table to the string at the character level and
-- when we find a comma, insert what's between that comma and
-- the next comma into the Elements table
INSERT INTO @Elements (Value)
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma
--===== Display the split as a single result set
-- SELECT * FROM @Elements
--===== Logic around checking the strings and return the right section:
BEGIN
-- if there's four addresses, give us the third
If ((Select value from @Elements where Number = 4) is not NULL)
begin
set @NewIP = (Select value from @Elements where Number = 3)
return @NewIP
end
ELSE
-- otherwise, check the two addresses to see if the second is IPv6 and return the IPv4 address:
IF (CHARINDEX(':',(Select value from @Elements where Number = 2))>0) --if second ip has ':' (IPv6), take the first
begin
set @NewIP = (select value from @Elements where Number = 1)
return @NewIP
end
ELSE
begin
set @NewIP = (select value from @Elements where Number = 2)
return @NewIP
end
END
END
And now i'm calling it like:
declare @IP varchar(200)
declare @NewIP varchar(200)
set @IP = '9.9.9.9,11.11.11.11,10.10.10.10,ff00::1234:aaaa:bbbb:1234'
exec test_stored_procedure @IP, @NewIP output
March 24, 2011 at 7:39 pm
Returns need to be an INT, that's why i got the conversion error. So i must be doing that wrong...
Changed the returns to selects and it works in the query window, but back to having 0 in the trigger.
I really don't get why the query work but trigger fails...
March 24, 2011 at 7:54 pm
Ok, i do laugh at myself sometimes.... 😀
so, the trigger was calling it
exec @NewIP = dbo.sp_Woodside_String_IP_Selection_V1 @IP, @NewIP = @test-2 OUTPUT
When Ian did point out i should call like:
exec dbo.sp_Woodside_String_IP_Selection_V1 @IP, @NewIP output
Which now works perfectly.
Thanks for the help guys, much appreciated.
March 25, 2011 at 5:50 pm
lukus_g (3/24/2011)
Which now works perfectly.
Try inserting more than one row in a single INSERT and see why that might not be true. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 27, 2011 at 10:59 pm
Hey Jeff,
Luckily, i only ever add one row at a time... which as i type it sounds a little RBAR... but that's how i'm fed the data.
ps: love the Zidane vs rbar avatar mate!
March 28, 2011 at 12:07 am
lukus_g (3/27/2011)
Hey Jeff,Luckily, i only ever add one row at a time... which as i type it sounds a little RBAR... but that's how i'm fed the data.
ps: love the Zidane vs rbar avatar mate!
I just wanted to make sure you knew that trigger isn't going to be worth a hill of beans if you ever insert more than one row at a time. Any interest in fixing that?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2011 at 7:44 pm
Well, i don't need to know for this implementation, but i'm very interested for my personal knowledge.
Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply