Solved

If statement in Trigger

Posted on 2004-10-18
15
1,272 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
  • 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
 

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
back end of ssas cube views or tables? 2 28
Numeric sequence in SQL 14 38
Change part of a string 2 24
SQL Query for Periods 3 0
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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 …
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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now