?
Solved

Using "Contains" in a trigger

Posted on 2005-04-11
5
Medium Priority
?
245 Views
Last Modified: 2008-07-03
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
0
Comment
Question by:jayme9
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13753055
The wildcard character in SQL is % not *
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13753070
oops...i was thinking of LIKE not CONTAINS
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 200 total points
ID: 13753081
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13753174
The syntax for CONTAINS only supports prefix searches as in:
WHERE CONTAINS(travelrequest_no, '"-U-*"')
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13753197
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question