December 9, 2008 at 7:56 am
Good Morning,
I need to develop a trigger to do the following:
An alternative could be a trigger on ALTA_Staging..Title_LastDocumentChangeDate
Every time a record is added or changed, check the DTI_SHORT_LEGAL_DISPLY_TEXT field in the matching DB_TITLE record.
If it contains a single semi-colon (i.e. LIKE '%;%' AND NOT LIKE '%;%;%' then get the plan number which is the part before the ‘;’
Then, update titledatareplicationdatetime..titledatadatetime with getdate() where the titlereferencenumber = plan number + CS
Here is what I have so far...but I don't know if this is right:
Update TitleDataReplicationDateTime
set
TitleDataReceiveDateTime=getdate()
from
Inserted I inner join DB_Title T
on I.TitleReferenceNumber=T.TitleReferenceNumber
where T.DTI_SHORT_LEGAL_DISPLY_TEXT like '%;%' AND NOT LIKE '%;%;%'
and I.TitleReferenceNumber in (Not sure what to put in here)
Please help!!!
December 10, 2008 at 2:13 am
I've made a few assumptions and I'm not sure what "plan number + CS" means but you could replace
and I.TitleReferenceNumber in (Not sure what to put in here)
with the rather horrendous looking
and I.TitleReferenceNumber = SUBSTRING(T.DTI_SHORT_LEGAL_DISPLY_TEXT,1,
CASE WHEN CHARINDEX(';',T.DTI_SHORT_LEGAL_DISPLY_TEXT) = 0 THEN 0 ELSE CHARINDEX(';',T.DTI_SHORT_LEGAL_DISPLY_TEXT) - 1 END)
This assumes DTI_SHORT_LEGAL_DISPLY_TEXT might not contain a semi-colon. If that assumption is wrong then SUBSTRING statement can be simplified but I'll leave that to you.
There are other ways of solving your problem but see how this goes.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply