trim Function in Sql Trigger
Posted on 2006-07-20
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]
if ((select trigonoff from trigconfig where trigtype = 'Solomon') = 1)
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
set XACT_ABORT on
exec Sol_Ins_Company @orgnbr, @entnbr
set XACT_ABORT off