Using "Contains" in a trigger

I have a trigger that audits changes by posting to an audit table when changes to other tables are made.  It works great; however, now I must isolate this to happen only to those records where the travelrequest_no field contains "-U-" while ignoring the other records.
I put a
WHERE CONTAINS(travelrequest_no, '"*-U-*"') after the FROM line below.  The trigger is successfully saved, but does not execute when I insert new records.  What am I doing wrong?  Perhaps another way of doing this?
--------------------------------------------------code---------------------------------
CREATE TRIGGER AuditVisit ON dbo.tbl_travelrequests
FOR INSERT
AS
/* * INITIALLY SETS THE TRANSFER FLAG OFF AND INSERTS OVERALL LAST MODIFICATION DATE IN 'tbl_auditForTransfer' */

BEGIN
   INSERT INTO [dbo].[tbl_auditForTransfer] (travelrequest_no, LastModifiedDate, TransferFlag)
   SELECT travelrequest_no, getDate(), 'OFF'
   FROM inserted
 
END




Thank you,
Jayme
jayme9Asked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
speaking of which why don't you use LIKE?

CREATE TRIGGER AuditVisit ON dbo.tbl_travelrequests
FOR INSERT
AS
/* * INITIALLY SETS THE TRANSFER FLAG OFF AND INSERTS OVERALL LAST MODIFICATION DATE IN 'tbl_auditForTransfer' */

BEGIN
   INSERT INTO [dbo].[tbl_auditForTransfer] (travelrequest_no, LastModifiedDate, TransferFlag)
   SELECT travelrequest_no, getDate(), 'OFF'
   FROM inserted
   WHERE travelrequest_no LIKE '%-U-%'  
END
0
 
Brian CroweDatabase AdministratorCommented:
The wildcard character in SQL is % not *
0
 
Brian CroweDatabase AdministratorCommented:
oops...i was thinking of LIKE not CONTAINS
0
 
Anthony PerkinsCommented:
The syntax for CONTAINS only supports prefix searches as in:
WHERE CONTAINS(travelrequest_no, '"-U-*"')
0
 
Anthony PerkinsCommented:
BriCrowe,

>>speaking of which why don't you use LIKE?<<
CONTAINS is indexed and should be a lot faster than a sequential search using LIKE.  However, in this case the questioner may not have much choice.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.