Solved

If statement in Trigger

Posted on 2004-10-18
15
1,277 Views
Last Modified: 2012-06-21
This is what I want to do with a trigger.

Everytime a record is inserted in tblMVR,  I want to search the field tblMVR.MVRResults for the phrase:  Report Clear:YES

If that phrase is found, I want to set the value of the field tblMVR.MVRStatus to the value of 1 in this inserted record.

Thanks,

Lori
0
Comment
Question by:LATurk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12338447
Hi,

CREATE TRIGGER trigger_name ON tblMVR
FOR INSERT
AS
     
declare @x varchar(200)

    UPDATE tblMVR SET MVRStatus = 1
    WHERE EXISTS ( SELECT 1 FROM tblMVR WHERE MVRResults LIKE '%Report Clear:YES%' )
                  AND PKEY_tblMVR = ( SELECT PKEY_tblMVR FROM INSERTED )

GO

itsvtk
0
 
LVL 9

Expert Comment

by:paelo
ID: 12338474
Maybe you could explain a little more about the purpose, but I would think that it would be easier to replicate this functionality with a calculated field in a view.


CREATE VIEW dbo.vwMVR
AS
SELECT M.*, CASE WHEN C.MVRResults LIKE '%Report Clear:YES%' THEN 1 ELSE 0 END As [MVRStatus]
FROM dbo.tblMVR M

I'm inherently biased against triggers, though.  I won't use them except as a very last resort because of some of the drawbacks.  If you are intent on doing this within a trigger you could use:

CREATE TRIGGER tr_MVRSetStatus
ON [dbo].[tblMVR]
FOR INSERT
AS
UPDATE INSERTED
SET MVRStatus=1
WHERE MVRResults LIKE '%Report Clear:YES%'


-Paul.
0
 
LVL 9

Expert Comment

by:paelo
ID: 12338508
Or, rather, if you're going the way of the trigger use itsvtk's suggestion because my syntax is flawed.

-Paul.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:LATurk
ID: 12338532
This is why I'm using a trigger.

We order Driving Reports using an xml gateway to our vendor.  The results are automatically dumped back into our database.  If the phrase Report Clear:YES shows up in that field, the report does not need to be looked at - it can be marked as Clear in our database and that's the end of it.  If the phrase is Report Clear:NO, then a person will need to physically look at the report.

Now does it sound like a job for a trigger?  
0
 
LVL 9

Assisted Solution

by:paelo
paelo earned 166 total points
ID: 12338610
I don't think so.  You just need to create a view like I mentioned to act as a wrapper for the data that's dumped into tblMVR.  Immediately after the data is dumped, you could run the aforementioned view (SELECT * FROM vwMVR WHERE MVRStatus=1) to obtain the same functionality, and its more efficient.

-Paul.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12339788
by all means use a trigger..

but get it to do work only when it needs to
when the status isn't what you want
and using the inserted table for the tests...

CREATE TRIGGER trigger_name ON tblMVR
FOR INSERT
AS
     
    UPDATE tblMVR
          SET MVRStatus = 1
        From tblMVR as M
        Inner Join INSERTED as I
             on M.PK_MVR = I.PK_MVR
    WHERE I.MVRStatus <> 1
         and  I.MVRResults like '%Report Clear:YES%'

GO
0
 

Author Comment

by:LATurk
ID: 12340176
Am I suppose to substitute the actual primary key name for M.PK_MVR (m.mvrId).

I changed it a little but I keep getting this error:

Error 311:  Cannot use text, ntext, or image columns in the inserted and deleted tables.

  UPDATE tblMVR
          SET MVRstatidlink = 1
        From tblMVR
        Inner Join INSERTED
             on tblMVR.mvrId = Scope_Identity()
    WHERE Inserted.MVRStatIdLInk <> 1
         and  Inserted.MVRResults like '%Report Clear:YES%'



0
 

Author Comment

by:LATurk
ID: 12340253
one more thing,

MVRResults has the datatype of text.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 166 total points
ID: 12340324
use PATINDEX ( '%pattern%' , expression )
instead of Like then...

and yes substitute the correct key..

CREATE TRIGGER trigger_name ON tblMVR
FOR INSERT
AS
     
    UPDATE tblMVR
          SET MVRStatus = 1
        From tblMVR as M
        Inner Join INSERTED as I
             on M.mvrId= I.mvrId
    WHERE I.MVRStatus <> 1
         and  Patindex('%Report Clear:YES%' ,I.MVRResults) > 0

GO

you can't use scope_identity in the trigger as that wouldn't be correct for a mass insert example....
which your trigger needs to deal with
0
 
LVL 1

Expert Comment

by:stewartwb
ID: 12340341
For the error regarding text, ntext, or image columns: your MVRResults column must be a text column.  Text columns allow more than 8000 chars, but they can't be used interchangeably with varchar columns in SQL code.  If you are sure the 'Report Clear:YES' text appears early in the field, you can simply CAST the text field to varchar:
  cast(MVRResults as varchar(8000))

I agree with paelo... a trigger is unnecessary and more complex to create / debug.

Rather than creating a view, you can simply add a calculated field to the actual data table.

When you define the table, define the status column to be
MVRStatus AS CASE WHEN cast(MVRResults as varchar(8000)) LIKE '%Report Clear:YES%' THEN 1 ELSE 0 END

I hope this helps.
-- Brian
0
 
LVL 1

Expert Comment

by:stewartwb
ID: 12340385
Ahh... excellent point... patindex() accepts fields of type text, not just char/varchar.

The field definition would become:

MVRStatus AS CASE WHEN patindex('%Report Clear:YES%', MVRResults) <> 0 THEN 1 ELSE 0 END

and you wouldn't have to worry about the specified text appearing in the first 8000 chars of the field.

-- Brian
0
 

Author Comment

by:LATurk
ID: 12341105
When I entered the following, I still get the same error.

    UPDATE tblMVR
          SET MVRStatIdLink = 1
        From tblMVR as M
        Inner Join INSERTED as I
             on M.mvrId= I.mvrId
    WHERE  Patindex('%Report Clear:YES%' ,I.MVRResults) > 0
0
 
LVL 1

Expert Comment

by:stewartwb
ID: 12354157
You could make a simple stored procedure to update the MVRStatIdLink based on the MVRResults column.  You could then call the stored procedure at the start of any process that pulls reports that need to be reviewed.

Of course, a calculated field or trigger would be kept in-sync at all times, but since you're having trouble getting it to work I'd recommend you try something like this first.

Try running this update statement and see if it works:

update tblMVR
   set MVRStatIdLink = case when cast(i.MVRResults as varchar(8000)) like '%Report Clear: YES%' then 1 else 0 end

How long is your longest report?  Does the text 'Report Clear: YES' appear in the first 8000 chars?

-- Brian
0
 
LVL 1

Accepted Solution

by:
stewartwb earned 168 total points
ID: 12354325
OK... I did some of my own research using Query Analyzer.  Your error message is actually pretty clear... you can't utilize Text, NText, or Image columns when working with the INSERTED and DELETED virtual tables in a trigger.  So, it appears that a trigger can't do what you want, unless you are willing to change your schema to use VARCHAR(8000) instead of TEXT.

Consider the following code:

create table tblMVR2 (
      mvrId int identity,
      MVRResults text,
      MVRStatus as case when patindex('%Report Clear:YES%', MVRResults) <> 0 then 1 else 0 end
      )

insert into tblMVR2 (MVRResults) values (replicate('*', 8000) + replicate('*', 8000) + 'Report Clear:YES')
insert into tblMVR2 (MVRResults) values (replicate('*', 8000) + 'Report Clear:YES')
insert into tblMVR2 (MVRResults) values (replicate('*', 7500) + 'Report Clear:YES')
insert into tblMVR2 (MVRResults) values (replicate('*', 2000) + 'Report Clear:YES')
select * from tblMVR2

This example follows my recommendation of using a calculated field rather than a trigger or update statement.  Notice that in this case only rows on which the 'Report Clear:YES' string appears in the first 8000 chars of the Text field are marked with MVRStatus = 1.  

So, if you use this strategy, or if you do an UPDATE statment just before you want to check the MVRStatus column, you can use either Text or Varchar(8000), but the condition won't work if the test string comes before position 7985 in the field.  To use a trigger, you must change from Text to Varchar(8000) for your report.

I can think of different schemas in which to hold the data, akin to the way SQL Server stores its stored procedures.  Those go into a table with NVarchar(4000) fields to store that data, with a set of rows for each procedure and a sequence number field to keep the "chunks" in order.  You could store the inbound reports in two tables, one with "header" data (Report ID, date, status) and one with "detail" data (text of the report split into varchar fields.

-- Brian

0
 

Author Comment

by:LATurk
ID: 12372913
This is what we ended up doing.  

Instead of updating the database with the driving results from inside a vb.net application that runs on a server, we now call a stored procedure from the vb.net application and pass the driving report to the stored procedure along with the ID number of the record that needs to be updated.  The stored procedure then checks for the phrase Report Clear:YES and updates the database accordingly. It worked fine in the stored procedure without using Cast or changing the datatype.

Thanks for all your help.  Hope it's ok that I split the points up between everyone.

Lori
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help needed in sql query 4 28
query analyser in sql server 2016 express edition 2 25
Change this SQL to get all nodes 3 38
SQL Server code help needed 14 30
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

756 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