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 9, 2008 at 2:02 pm
You might use a CASE in here for your logic.
If charindex() returns > 0, then there is a semi colon in there (if you are checking). Then you can use the charindex value to get the position and substring to pull out the string.
If charindex returns zero, I'd probably return a 1 to that part of the WHERE clause.
December 10, 2008 at 8:14 am
--You haven't given much information to work with, but based on your question, the following gives you an idea of what your trigger may look like:
CREATE TRIGGER tr_Title_LastDocumentChangeDate ON Title_LastDocumentChangeDate
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE TitleDataReplicationDateTime
SET TitleDataReceiveDateTime = GETDATE()
FROM TitleDataReplicationDateTime D
INNER JOIN Inserted I
ON D.TitleReferenceNumber = I.TitleReferenceNumber
INNER JOIN DB_Title T
ON I.TitleReferenceNumber = T.TitleReferenceNumber
WHERE
T.DTI_SHORT_LEGAL_DISPLY_TEXT LIKE '%;%'
AND T.DTI_SHORT_LEGAL_DISPLY_TEXT NOT LIKE '%;%;%'
AND D.TitleReferenceNumber = SUBSTRING(T.DTI_SHORT_LEGAL_DISPLY_TEXT, 1,
CHARINDEX(';',
T.DTI_SHORT_LEGAL_DISPLY_TEXT)
- 1) + 'CS'
END
December 10, 2008 at 8:22 am
Thanks for the help. That looks kind of like what I am trying to get at...but I tested it and it's not updating TitleReplicationDateTime.
The results are as follows
TitleReferenceNumber LastDocumentRegistrationDate
-------------------- ----------------------------
0020033CS1 2008-12-10 08:19:38.243
(1 row(s) affected)
TitleReferenceNumber TitleDataReceiveDateTime
-------------------- ------------------------
0020033CS1 2004-08-09 10:56:32.390
(1 row(s) affected)
The first is from Title_DocumentLastChangeDate and the second is from TitleReplicationDateTime.
Any thoughts?
December 10, 2008 at 8:30 am
You would have to give me more information, including sample data from the related tables. You did not give the value of DTI_SHORT_LEGAL_DISPLY_TEXT.
December 10, 2008 at 8:42 am
Here is some data from DB_Title..DTI_SHORT_LEGAL_TEXT_DISPLY:
0120014;72
0020213;20C
0120070;1;1
9921326;12
0420009;48
7521106;3;3
9920020;1;3
7521308;1;3
7921543;1;2
Basically...if an update happens to any record in Title_LastDocumentChangeDate we want to check this field for corresponding records in DB_Title. If we find a record that is like 9921326;12 we want to capture the part before the semi colon and find all records that match that + CS in TitleDataReplicationDateTime and change the DateTime field for that record to getdate().
I hope that helps.
December 10, 2008 at 8:48 am
I think I got it. I just changed yours slightly to be the following:
BEGIN
SET NOCOUNT ON
UPDATE TitleDataReplicationDateTime
SET TitleDataReceiveDateTime = GETDATE()
FROM TitleDataReplicationDateTime D
INNER JOIN Inserted I
ON D.TitleReferenceNumber = I.TitleReferenceNumber
INNER JOIN DB_Title T
ON I.TitleReferenceNumber = T.DTI_TITLE_REFRNC_NBR
WHERE
T.DTI_SHORT_LEGAL_DISPLY_TEXT LIKE '%;%'
AND T.DTI_SHORT_LEGAL_DISPLY_TEXT NOT LIKE '%;%;%'
AND D.TitleReferenceNumber like SUBSTRING(T.DTI_SHORT_LEGAL_DISPLY_TEXT, 1,
CHARINDEX(';',
T.DTI_SHORT_LEGAL_DISPLY_TEXT)
- 1) + 'CS' + '%'
END
This works because there are often values after the CS...which I forgot about when I posted by original question. I'm moving on to do some more testing but so far things look good. Thanks a lot for the help.
December 10, 2008 at 11:00 am
Sorry...another question on this one...
How would I add a case statment to the Where so that the clause I have specified is only evaluated in the case where there is a ; in DTI_SHORT_LEGAL_DISPLY_TEXT. In the case where ; is not in this field, the where clause should be ignored. Please let me know.
December 10, 2008 at 12:32 pm
You don't need a CASE.
Change your WHERE Clause from:
WHERE
T.DTI_SHORT_LEGAL_DISPLY_TEXT LIKE '%;%'
AND T.DTI_SHORT_LEGAL_DISPLY_TEXT NOT LIKE '%;%;%'
AND D.TitleReferenceNumber like SUBSTRING(T.DTI_SHORT_LEGAL_DISPLY_TEXT, 1,
CHARINDEX(';',
T.DTI_SHORT_LEGAL_DISPLY_TEXT)
- 1) + 'CS' + '%'
To this:
WHERE
LEN(T.DTI_SHORT_LEGAL_DISPLY_TEXT) - 1 = LEN( REPLACE( T.DTI_SHORT_LEGAL_DISPLY_TEXT, ';', '' ) )
AND D.TitleReferenceNumber like SUBSTRING(T.DTI_SHORT_LEGAL_DISPLY_TEXT, 1,
CHARINDEX(';',
T.DTI_SHORT_LEGAL_DISPLY_TEXT)
- 1) + 'CS' + '%'
This way if you only have one semi-colon ";" then the LEN()-1 will equal the LEN() after replacing the ";" with nothing. If you have two, or more, semi-colons there will be an inequality and the record will not be selected for update.
December 10, 2008 at 1:36 pm
I don't think that is quite right. Now the TitleReferenceNumber = 872168536C does not get it's time updated when a change is made to the corresponding record on Title_LastDocumentChangeDate.
Basically, I need the trigger to find records if the TitleReferenceNumber has no semi colon or one semi colon but not two. Does that make sense?
December 10, 2008 at 2:06 pm
I think it was right based on the information you had given up to that point. Nowhere do you mention that you want to update records where there is no semi-colon. You don't mention that as a possibility. Even in your example data you don't show a value with no semi-colon.
WHERE
( CHARINDEX(';', T.DTI_SHORT_LEGAL_DISPLY_TEXT) = 0
OR
LEN(T.DTI_SHORT_LEGAL_DISPLY_TEXT) - 1 = LEN( REPLACE( T.DTI_SHORT_LEGAL_DISPLY_TEXT, ';', '' ) )
)
AND
(D.TitleReferenceNumber like SUBSTRING(T.DTI_SHORT_LEGAL_DISPLY_TEXT, 1,
CHARINDEX(';',
T.DTI_SHORT_LEGAL_DISPLY_TEXT)
- 1) + 'CS' + '%'
OR
D.TitleReferenceNumber like T.DTI_SHORT_LEGAL_DISPLY_TEXT + 'CS' + '%'
)
December 10, 2008 at 2:24 pm
Thank you for the help. You are right. I didn't quite phrase it correctly when I first brought up what I was trying to do. Thanks for baring with me.
I tried out the Where clause you provided. Now when I run:
Update Title_LastDocumentChangeDate
set LastDocumentRegistrationDate= getdate()
where titlereferencenumber = '872168536C'
I get the following error in regards to the trigger:
Msg 536, Level 16, State 5, Procedure trTitle_LastDocumentChangeDate_IU_LastDocumentChangeDate, Line 20
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.
December 10, 2008 at 2:47 pm
The problem is in not handling the possibility that there may not be a semi-colon in the part of the clause within the SUBSTRING() function.
WHERE
( CHARINDEX(';', T.DTI_SHORT_LEGAL_DISPLY_TEXT) = 0
OR
LEN(T.DTI_SHORT_LEGAL_DISPLY_TEXT) - 1 = LEN( REPLACE( T.DTI_SHORT_LEGAL_DISPLY_TEXT, ';', '' ) )
)
AND
(D.TitleReferenceNumber LIKE
CASE WHEN CHARINDEX(';',T.DTI_SHORT_LEGAL_DISPLY_TEXT) = 0 THEN T.DTI_SHORT_LEGAL_DISPLY_TEXT + 'CS' + '%'
ELSE SUBSTRING(T.DTI_SHORT_LEGAL_DISPLY_TEXT, 1, CHARINDEX(';',T.DTI_SHORT_LEGAL_DISPLY_TEXT)- 1) + 'CS' + '%'
END
)
I hope this helps.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply