Need Advise - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Advise

Author

Topic

dlbjr
Starting Member

1 Post

Posted - 2010-10-08 : 11:33:42

I have a field with a certain value that needs to be edited before sync with other system.

Current value sample: 312-34.56 The first number can be 3 or 4 characters long
Needeed Value sample: 0312-34.56 The first number must be 4 characters long

Here is a trigger I have written to do the trick. Please let me know if this will work before I release to production.

CREATE TRIGGER trig_local_inventory_fix_Inv_ID
ON Local_Inventory
FOR INSERT, UPDATE
BEGIN
SET NOCOUNT ON;

DECLARE @Inv_Id varchar(50),
@new_Inv_Id varchar(50),
@RecordID bigint

SELECT @Inv_Id = i.Inv_ID, @RecordID = i.RecordID
FROM inserted i

@new_Inv_Id =Right( '0000' + Substring(@Inv_Id,1,Charindex('-',@Inv_Id)-1),4)
@new_Inv_Id = @new_Inv_Id + '-' + Substring(@Inv_Id,Charindex('-',@Inv_Id)+1, Len(@Inv_Id))

UPDATE Local_Inventory
SET Inv_Id = @new_Inv_Id
WHERE (RecordID = @RecordID)

END
GO

dlbjr

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-08 : 11:41:13

You have to consider the possibility of more than one row at a time in inserted!
No need to use variables, I think you can do that update in only one step.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-08 : 11:46:22

Test this:
update t
set Inv_Id = Right( '0000' + Substring(i.Inv_Id,1,Charindex('-',i.Inv_Id)-1),4)+ '-' + Substring(i.Inv_Id,Charindex('-',i.Inv_Id)+1, Len(i.Inv_Id))
from Local_Inventory as t
join inserted as i
on i.RecordID = t.RecordID

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources