August 24, 2006 at 2:28 pm
I have two 3rd party apps, one passes data to the other in a built in interface. ONe of hte fields I need populated isn't in the interface. The value I need is in a field that does get passed. I want to create an insert trigger on the receiving table when the field I need to populate is empty (there are other processes that also insert where that field is filled in), go to the other field and parse out the value I need, it will always be at the beginning of the field if it exists (sometimes it may not be there). Once I have that data, move it to the desired field and have the insert complete.
Below is what I have for the trigger. I'm gettign two errors -
Server: Msg 156, Level 15, State 1, Procedure UPD_CUST_PART, Line 22
Incorrect syntax near the keyword 'set'.
Server: Msg 156, Level 15, State 1, Procedure UPD_CUST_PART, Line 23
Incorrect syntax near the keyword 'set'.
If I comment out two of the set lines, the errors go away, of course that doesn't do me any good. Since I'm a newbie, any help is appreciated. I looked in BOL, it says to "Use the SET statement to assign a value that is not NULL to a declared variable."
Thanks
CREATE TRIGGER UPD_CUST_PART ON dbo.sdata
FOR INSERT
AS
IF EXISTS
(
Select 'True'
from INSERTED
where custpart = ''
)
BEGIN
DECLARE @temppart VARCHAR(300)
DECLARE @part CHAR(25)
DECLARE @memo TEXT
Select @memo = descmemo
from inserted
--pull out the customer Part number if there is one
IF ( left(@memo, 14) = "Customer Part:" )
set @temppart = left(@memo, (len((charindex(@memo,char(13))))
set @temppart = right(@temppart, ((len(@temppart))-((charindex(@temppart,':')+1)))
set @part = left(@temppart, 25)
Update inserted
set fcustpart = @part
END
Thanks
August 24, 2006 at 2:43 pm
"inserted" is a virtual table containing the row or rows being inserted. You can't Update it. You need to update dbo.sdata and join it to inserted on the primary key column or columns.
Secondly, you've coded a solution that is not set-based and assumes only single row inserts. This will fail on any transaction that inserts 2 or more rows to dbo.sdata.
August 24, 2006 at 2:50 pm
OK, I think I understand the updating part.
At this time they will be single transactions, but you never know about the future. How would you do a set based solution for this type of thing?
Lastly, what about the error messages, I can't create the trigger yet.
Thanks
Again
Thanks
August 25, 2006 at 8:46 am
Thanks PW, I've rewritten it to be set based, plus now its much simplier.
CREATE TRIGGER UPD_CUST_PART ON dbo.data
FOR INSERT
AS
BEGIN
UPDATE data
SET custpart =
left(RIGHT(LEFT(cast(s.textfld as varchar(500)),(CHARINDEX(CHAR(13),cast(s.textfld as varchar(500)))-1)),((CHARINDEX(CHAR(13),CAST(s.textfld as varchar(500)))-1)-15)),25)
FROM inserted i
JOIN data s
ON i.identity_column = s.identity_column
WHERE s.custpart = ''
AND LEFT(CAST(s.textfld as varchar(1000)), 14) = 'Customer Part:'
END
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply