• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

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'
0
jspc
Asked:
jspc
1 Solution
 
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 ChongCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now