Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1285
  • Last Modified:

If statement in Trigger

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
LATurk
Asked:
LATurk
  • 5
  • 4
  • 3
  • +2
3 Solutions
 
Thandava VallepalliCommented:
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
 
paeloCommented:
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
 
paeloCommented:
Or, rather, if you're going the way of the trigger use itsvtk's suggestion because my syntax is flawed.

-Paul.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
LATurkAuthor Commented:
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
 
paeloCommented:
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
 
LowfatspreadCommented:
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
 
LATurkAuthor Commented:
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
 
LATurkAuthor Commented:
one more thing,

MVRResults has the datatype of text.
0
 
LowfatspreadCommented:
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
 
stewartwbCommented:
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
 
stewartwbCommented:
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
 
LATurkAuthor Commented:
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
 
stewartwbCommented:
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
 
stewartwbCommented:
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
 
LATurkAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now