Trigger for SQL Server 2008

I was provided with a SQL Trigger yesterday by one of your members which works.

I have since discovered the program which inserts the data has a bug whereby it's creating a carriage return and a space before the data.

I was wondering if l can include in the below trigger script a workaround, whereby it removes the left space and carriage return first before the update?

The data type is TEXT. I'm not sure why the developer of the database is using TEXT.

Thanks


CREATE TRIGGER TRANSHEADERS_CustomerRef_Update  
ON Transheaders  
FOR INSERT AS  
BEGIN  
    UPDATE TRANSHEADERS
Set ZCustomerRef = ExtraText
Where AccountID = 'CAWWWEBXXXX'
jspcAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harisrashidCommented:
Use LTRIm function to remove spaces and Replace function to remove other characters.
0
jspcAuthor Commented:
Are you able to provide the required script, please?
0
ste5anSenior DeveloperCommented:
Just a comment:

Get rid of TEXT. It is deprecated. Also some string functions done work on TEXT, which requires an additional CAST(column TO [N]VARCHAR(MAX)). This is should not be done without very good reasons. And not knowing why it is used is definitly not a good reason.
1
jspcAuthor Commented:
I can only work with what I have. I cant change the data type of the database.
Are you suggesting adding more detail to the script, converting it? I don't understand what you mean by CAST.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>I was wondering if l can include in the below trigger script a workaround, whereby it removes the left space and carriage return first before the update?

probably using substring function to get the value from 3rd chars onwards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.