Trigger help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger help

Author

Topic

Jessica
Starting Member

23 Posts

Posted - 2008-12-09 : 12:26:31

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!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 12:34:07

something like


CREATE TRIGGER YourTrigger
ON ALTA_Staging..Title_LastDocumentChangeDate
AFTER INSERT,UPDATE 
AS
BEGIN
Update t
set
t.TitleDataReceiveDateTime=getdate()
from TitleDataReplicationDateTime t
JOIN Inserted I 
ON I.TitleReferenceNumber=t.plannumber + t.CS
inner join DB_Title tl
on I.TitleReferenceNumber=tl.TitleReferenceNumber
where LEN(tl.DTI_SHORT_LEGAL_DISPLY_TEXT )-LEN(REPLACE(tl.DTI_SHORT_LEGAL_DISPLY_TEXT,';',''))=1
END

Jessica
Starting Member

23 Posts

Posted - 2008-12-09 : 12:39:48

That's helpful. Thanks.

One clarification though. There is no t.plannumber. I have to strip that off from DTI_SHORT_LEGAL_DISPLY_TEXT where the part before the ; is the plan number. From there, I need to use that plannumber + 'CS' to find the record in TitleDataReplicationDateTime to update. Can you help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 12:44:42

[code]
CREATE TRIGGER YourTrigger
ON ALTA_Staging..Title_LastDocumentChangeDate
AFTER INSERT,UPDATE
AS
BEGIN
Update t
set
t.TitleDataReceiveDateTime=getdate()
from TitleDataReplicationDateTime t
JOIN Inserted I
ON I.TitleReferenceNumber=LEFT(tl.DTI_SHORT_LEGAL_DISPLY_TEXT,CHARINDEX(';',tl.DTI_SHORT_LEGAL_DISPLY_TEXT)-1) + 'CS'
inner join DB_Title tl
on I.TitleReferenceNumber=tl.TitleReferenceNumber
where LEN(tl.DTI_SHORT_LEGAL_DISPLY_TEXT )-LEN(REPLACE(tl.DTI_SHORT_LEGAL_DISPLY_TEXT,';',''))=1
END[/code]

Jessica
Starting Member

23 Posts

Posted - 2008-12-09 : 14:45:28

I get these errors:
Msg 4104, Level 16, State 1, Procedure trTitle_LastDocumentChangeDate_IU_LastDocumentChangeDate, Line 17
The multi-part identifier "tl.DTI_SHORT_LEGAL_DISPLY_TEXT" could not be bound.
Msg 4104, Level 16, State 1, Procedure trTitle_LastDocumentChangeDate_IU_LastDocumentChangeDate, Line 17
The multi-part identifier "tl.DTI_SHORT_LEGAL_DISPLY_TEXT" could not be bound.
Msg 207, Level 16, State 1, Procedure trTitle_LastDocumentChangeDate_IU_LastDocumentChangeDate, Line 24
Invalid column name 'TitleReferenceNumber'.

Any thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 02:33:34

Does the above fields exist in DB_Title table? else replace alias tl with approprite alias

Jessica
Starting Member

23 Posts

Posted - 2008-12-10 : 09:48:36

I replaced tl.TitleReferenceNumber with the actual column name in db_title. That fixed that problem. I'm still getting the error on:
Msg 4104, Level 16, State 1, Procedure trTitle_LastDocumentChangeDate_IU_LastDocumentChangeDate, Line 17
The multi-part identifier "tl.DTI_SHORT_LEGAL_DISPLY_TEXT" could not be bound.

That field is the right name. Is there anything else that could be causing this error?

Jessica
Starting Member

23 Posts

Posted - 2008-12-10 : 10:10:43

I had to change it to this to get it to compile:
Update t
set
t.TitleDataReceiveDateTime=getdate()
from TitleDataReplicationDateTime t, DB_Title TL
INNER JOIN Inserted I
ON I.TitleReferenceNumber=LEFT(TL.DTI_SHORT_LEGAL_DISPLY_TEXT,CHARINDEX(';',TL.DTI_SHORT_LEGAL_DISPLY_TEXT)-1) + 'CS'
inner join TL
on I.TitleReferenceNumber=TL.DTI_TITLE_REFRNC_NBR
where LEN(TL.DTI_SHORT_LEGAL_DISPLY_TEXT )-LEN(REPLACE(TL.DTI_SHORT_LEGAL_DISPLY_TEXT,';',''))=1

But now when the trigger fires I get the following error:
Msg 208, Level 16, State 1, Procedure trTitle_LastDocumentChangeDate_IU_LastDocumentChangeDate, Line 17
Invalid object name 'TL'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 10:20:39

quote:
Originally posted by Jessica

I had to change it to this to get it to compile:
Update t
set
t.TitleDataReceiveDateTime=getdate()
from TitleDataReplicationDateTime t, DB_Title TL
INNER JOIN Inserted I
ON I.TitleReferenceNumber=LEFT(TL.DTI_SHORT_LEGAL_DISPLY_TEXT,CHARINDEX(';',TL.DTI_SHORT_LEGAL_DISPLY_TEXT)-1) + 'CS'
inner join DB_Title TL
on I.TitleReferenceNumber=TL.DTI_TITLE_REFRNC_NBR
where LEN(TL.DTI_SHORT_LEGAL_DISPLY_TEXT )-LEN(REPLACE(TL.DTI_SHORT_LEGAL_DISPLY_TEXT,';',''))=1

But now when the trigger fires I get the following error:
Msg 208, Level 16, State 1, Procedure trTitle_LastDocumentChangeDate_IU_LastDocumentChangeDate, Line 17
Invalid object name 'TL'.

you missed table name

Jessica
Starting Member

23 Posts

Posted - 2008-12-10 : 10:29:03

I changed it as you suggested and get the following:
Msg 1011, Level 16, State 1, Procedure trTitle_LastDocumentChangeDate_IU_LastDocumentChangeDate, Line 20
The correlation name 'TL' is specified multiple times in a FROM clause.

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-10 : 10:31:01

Try normalizing your data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 10:34:05

dont duplicate aliases


Update t
set
t.TitleDataReceiveDateTime=getdate()
from TitleDataReplicationDateTime t, DB_Title TL
INNER JOIN Inserted I 
ON I.TitleReferenceNumber=LEFT(TL.DTI_SHORT_LEGAL_DISPLY_TEXT,CHARINDEX(';',TL.DTI_SHORT_LEGAL_DISPLY_TEXT)-1) + 'CS'
inner join DB_Title TL1
on I.TitleReferenceNumber=TL1.DTI_TITLE_REFRNC_NBR
where LEN(TL.DTI_SHORT_LEGAL_DISPLY_TEXT )-LEN(REPLACE(TL.DTI_SHORT_LEGAL_DISPLY_TEXT,';',''))=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 10:39:38

b/w how is TitleDataReplicationDateTime related to other tables? also why are you using DB_Title twice?

Jessica
Starting Member

23 Posts

Posted - 2008-12-10 : 10:54:23

I ended up changing it as follows and now it seems to work:
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

Jessica
Starting Member

23 Posts

Posted - 2008-12-10 : 12:59:44

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 21:15:44

[code]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' + '%'
OR CHARINDEX(';',
T.DTI_SHORT_LEGAL_DISPLY_TEXT)=0[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources