December 9, 2005 at 1:30 pm
I would like to take the data from a text field and parse it then the data in the field changes. I've figured out how to create an "instead of" trigger so that I can access the data, and I've figured out how to define a stored procedure that can accept text input. What I don't no how to do is get the text field value into the Stored Procedure. I've tested this by doing a substring(textzField,1,8000) but there are cases where my fields have more characters than that.
My trigger logic looks lie:
create TRIGGER gt_i_setLinks
ON generalText instead of insert
AS
BEGIN
DECLARE @linkId int
DECLARE @linkType varchar(5)
declare @userName varchar(80)
declare @textValue varchar(8000) ' This is a placeholder
set @linkId = (select id from inserted)
set @linkType = 'gt'
set @userName = (select crUser from inserted)
set @textValue = (select substring(entry,1,8000) from inserted)
' the above is my text field.
EXEC sp_checkSoldierLink @linkId, @linkType, @userName, @textValue
END
I hope what I am trying to do is clear. Any help would be appreciated.
Thanks
marc
December 9, 2005 at 4:16 pm
It looks like you want to make the data in the inserted table available in your stored procedure and you know that you cannot access inserted table from a sp, so you are attempting to pass the values in using variables. The problem is that the inserted table can grow beyond the size that your variable can hold.
The only method that pops into my head that may work would be to use a global temporary table to hold the values from the inserted table. Instead of saying SET @textValue = (select ...... from inserted), you could say 'select * into ##tempTable from inserted'. This will create a global temporary table with the same columns and data as the insered table.
I know this will work with a global table. I am not sure if this will work with a non-global temp table, but this is something you may want to play around with. If you have to make it a global table, you will need to come up with a method to define unique table names so that each call to the trigger can work off of its own table.
December 9, 2005 at 4:46 pm
Thanks for the suggestion, but I'm not sure what it would get me. Somehow I have to be able to get the data into the parameter for the Stored Procedure. The SP can accept a text variable as a parameter, so I've been counting on some way to get the text data field from the trigger into the SP parameter. The fact that a SP can accept a text variable, but there is no way to populate it (other than through external calls) seems to limit the functionality somewhat.
Inside the SP I loop through the text and parse out certain strings. I could do it from the data in the table, but patIndex only finds the first occurance, and that's a limitation I'd have with either the inserted data or the temp table.
I could loop through in 8000 character chunks and parse it out from there, but that doesn't seem to be an effective way of doing this.
December 12, 2005 at 11:06 am
The use of a temporary table would eliminate the need for patIndex. You would have to redesign your stored procedure to work off of the temp table instead of using patIndex to search through your text string.
Can you explain what you are expecting as an end result from your stored procedure?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy