Solved

trim Function in Sql Trigger

Posted on 2006-07-20
8
936 Views
Last Modified: 2008-01-16
I need to trim to tables before they are replicated to the other sql databse. I have a database called ResourceFile_FK and it has a table called Entity.

I have another database called sol_impact_db and it has a table called customer.

We have a trigger setup to move data from ResourceFile_FK to Sol_Impact_db and we are running in to a problem because the table Entitiy on ResourceFile_FK has two colum that have character lengths of 100 and the same colums on the customer table are only 30. We can not modity the colums on from 30 to 100 because it will break our solomon application that relies on that database and expects it to be 30. I need to add a line to our existing trigger that will trim anything from those two colums on Entity from 100 to 30 charaters so that it will match the customer table on sol_impact_db.  I have heard of a left / right function but have no idea how to create this.

Here is my current trigger that is setup. I am not a sql developer so sorry if I'm not explaining this in the simplest form. I'll be happy to answer any questions that will help you help me.  Thank you very much for your help.

Here is my current trigger (SOL_Entity_Upd):

CREATE TRIGGER SOL_Entity_Upd  ON [dbo].[Entity]
FOR UPDATE
AS

if ((select trigonoff from trigconfig where trigtype = 'Solomon') = 1)
BEGIN

if update(entitystatus)
begin

declare @orgnbr int
declare @entnbr int
declare @entitystatus varchar(4)
declare @entitystatus_d varchar(4)

select @orgnbr = orgnbr, @entnbr = entnbr, @entitystatus = i.entitystatus
from inserted i

select @entitystatus_d = d.entitystatus
from deleted d

if @entitystatus = 'ACTV' and @entitystatus_d <> 'ACTV' and (select count(idnumber) from alternate_id where idtype = 'imp' and orgnbr = @orgnbr and entnbr = @entnbr) = 1
begin
      set XACT_ABORT on
      exec Sol_Ins_Company @orgnbr, @entnbr
      set XACT_ABORT off
end

end      

END


0
Comment
Question by:pdiblasi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
8 Comments
 
LVL 5

Accepted Solution

by:
Dbergert earned 500 total points
ID: 17149128

LEFT (columname,30)

you should just have to put the above somewhere and repalce the column name with it.


Here is another example:

The example below for the Northwind sample SQL Server database uses the Left function to create a three character ProductCode column from the first three characters of the ProductName column:

SELECT ProductName, LEFT(ProductName, 3) AS ProductCode FROM Products




0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17158493
On a totally unrelated note, you should be aware that Triggers are not executed for every row UPDATEd (INSERTed or DELETEd), but rather executed for every UPDATE (INSERT or DELETE) statement.  How does that affect me, you ask?  If you should update more than one row at a time the following code:

select @orgnbr = orgnbr, @entnbr = entnbr, @entitystatus = i.entitystatus
from inserted i

select @entitystatus_d = d.entitystatus
from deleted d

Will only contain the values from the last row updated.  In other words the Inserted and Deleted logical tables are tables for a reason. If this could occur you will have to place the values of the Inserted/Deleted tables in a CURSOR and execute the Sol_Ins_Company stored procedure for each applicable row.
0
 
LVL 1

Expert Comment

by:artificial_dragon
ID: 17170245
substr (columnname, 0,29)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17170476
artificial_dragon,

>>substr (columnname, 0,29)<<
Do you mean the T-SQL function SUBSTRING()?  If so it is 1-based not 0-based.  So that should read:
SUBSTRING(columnname, 1, 29)

Which for varchars is funcitonaly equivalent to:
LEFT(columnname, 29)

Which was alaredy suggested ...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17762367
Award points to Dbergert
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question