Solved

MSSQL TRIGGER WITH CASE

Posted on 2014-09-09
18
135 Views
Last Modified: 2014-09-09
Hello!  I'm trying to write a simple trigger in MSSQL...and I'm a 'newbie' when it comes to trigger writing, so please don't laugh at me too hard :-)...

The following script produces an error.  I'm sure it's got something to do with the Trigger's syntax...can someone please help me?  I needed to do the 'CASE' statement (I believe???), because any one of the (3) fields in question "may" be a NULL value...so I had to account for that.  Anyhoo, here goes...please feel free to comment and correct...I'd truly appreciate it!...Thanks!...Mark


/****** Object:  Trigger [dbo].[DTC_COPY_ATTRS_TO_DESCR1]    Script Date: 05/03/2011 11:52:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[DTC_COPY_ATTRS_TO_DESCR1]  ON [dbo].[IM_ITEM]
AFTER INSERT, UPDATE
AS

SET NOCOUNT ON

DECLARE @ATTR_1      VARCHAR(10)
DECLARE @ATTR_2      VARCHAR(10)
DECLARE @PROF_1 VARCHAR(30)

SELECT @ATTR_1 = ATTR_COD_1 FROM INSERTED
SELECT @ATTR_2 = ATTR_COD_2 FROM INSERTED
SELECT @PROF_1 = PROF_ALPHA_1 FROM INSERTED


UPDATE dbo.IM_ITEM SET                    

ADDL_DESCR_1 = CASE WHEN (@ATTR_1 IS NULL AND @ATTR_2 IS NULL AND @PROF_1 IS NULL) THEN ''
                    WHEN (@ATTR_1 IS NULL AND @ATTR_2 IS NULL AND @PROF_1 IS NOT NULL) THEN @PROF_1
                    WHEN (@ATTR_1 IS NULL AND @ATTR_2 IS NOT NULL AND @PROF_1 IS NULL) THEN @ATTR_2  
                    WHEN (@ATTR_1 IS NULL AND @ATTR_2 IS NOT NULL AND @PROF_1 IS NOT NULL) THEN @ATTR_2+@PROF_1
                    WHEN (@ATTR_2 IS NULL AND @ATTR_1 IS NOT NULL AND @PROF_1 IS NULL) THEN @ATTR_1  
                    WHEN (@ATTR_2 IS NULL AND @ATTR_1 IS NOT NULL AND @PROF_1 IS NOT NULL) THEN @ATTR_1+@PROF_1
                    WHEN (@ATTR_1 IS NOT NULL AND @ATTR_2 IS NOT NULL AND @PROF_1 IS NULL) THEN @ATTR_1+@ATTR_2
                    ELSE @ATTR_1+@ATTR_2+@PROF_1 END
0
Comment
Question by:datatechcorp
  • 8
  • 4
  • 4
  • +1
18 Comments
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
Instead of the case, try this:
ADDL_DESCR_1 = ISNULL(@ATTR_1, '') + ISNULL(@ATTR_2, '') + ISNULL(@PROF_1, '') 

Open in new window


Also, if your code/query can update multiple rows in the table at once, your trigger will fail, because you cannot assign multiple row values to a variable.
0
 
LVL 25

Accepted Solution

by:
Shaun Kline earned 500 total points
Comment Utility
If your IM_ITEM table has a primary key you may be better off writing the entire trigger query like this:
CREATE TRIGGER [dbo].[DTC_COPY_ATTRS_TO_DESCR1]  ON [dbo].[IM_ITEM]
 AFTER INSERT, UPDATE
 AS
UPDATE dbo.IM_ITEM
SET ADDL_DESCR_1 = ISNULL(ATTR_COD_1, '') + ISNULL(ATTR_COD_2, '') + ISNULL(PROF_ALPHA_1, '')
FROM Inserted
  INNER JOIN dbo.IM_ITEM IM_ITEM ON Inserted.<PK Column> = IM_ITEM.<PK Column>

Open in new window

0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Shaun...

I'm just a 'tad' confused.  In the trigger, we're only updating (1) field in the IM_ITEM table...that being the 'ADDL_DESCR_1' field.  BUT...it's quite possible, that any one of the (3) fields (i.e. my 3 variable)...CAN, indeed, be 'NULL'...so I needed to account for that.

So...based on this information, what would the 'UPDATE' statement look like, knowing that any one of ATTR_COD_1, ATTR_COD_2, or PROF_ALPHA_1...could, possibly, be a 'NULL' value?  Please help...I'd very much appreciate it!...Thanks!...Mark
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Shaun...

With either method that you suggested...I keep getting an error in MSSQL:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.

Any thoughts on this?
0
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
To your first comment:
The ISNULL function will use the first non-NULL value in the parameter list. So, if ATTR_COD_1 is NULL, it will use the second parameter, which is an empty string. This also applies to your other fields. When you concatenate an empty string to a field that has text, you only get back the text. If all values evaluate to empty strings, the result will be an empty string.

To your second comment:
Are you getting that error when you attempt to create the trigger, or afterwards, when you perform an update on your IM_Item table?
0
 

Author Comment

by:datatechcorp
Comment Utility
....when I first try to create the Trigger...
0
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
Please provide the updated trigger code. Are you running the CREATE TRIGGER SQL in SQL Server Management Studio?
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Shaun...

Yes, I'm definitely doing this inside of MSSQL Management Studio.  Here is the Trigger Create Statement:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[DTC_COPY_ATTRS_TO_DESCR1]  ON [dbo].[IM_ITEM]
 AFTER INSERT, UPDATE
 AS
 SET NOCOUNT ON
 
UPDATE dbo.IM_ITEM
SET ADDL_DESCR_1 = ISNULL(ATTR_COD_1, '') + ISNULL(ATTR_COD_2, '') + ISNULL(PROF_ALPHA_1, '')
FROM INSERTED

...which yields the following error in Management Studio...

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You definitely should use a computed column for this.  There's no reason to duplicate data in the table and have a trigger do additional work in this situation:

ALTER TABLE [dbo].[IM_ITEM]
ADD ADDL_DESCR_1 AS CAST(
    ISNULL(ATTR_COD_1, '') + ISNULL(ATTR_COD_2, '') + ISNULL(PROF_ALPHA_1, '')
    AS varchar(200)) --<<-- chg to whatever data type and length you want to use for this column
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:datatechcorp
Comment Utility
Hi Shaun...

I would *LOVE* to use a Computed Column...but we are NOT the authors of the schema...NCR is...and, as such, they can (and quite often do) OVERWRITE their schema...particularly for File/Column definitions...during updates, upgrades, and/or service packs.  They DON'T touch anyone's Customized Triggers...and that is the reason I had to opt for that route.

Just so you're aware, I realize the mistake I made in my Trigger code above, and now the error when opt for the "Create" script is gone.  So, thank you SOOOOOOO much for all your help...it is now working...correctly.  And I truly appreciate all your advice and commentary as well.  Again, THANK YOU!!!...Mark
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"... They DON'T touch anyone's Customized Triggers. ... "

That is because they don't know about them, and you will probably learn that triggers against the vendors data model are not supported either.  You put the product at risk by introducing unsupported triggers.

Just thought I should caution you on this. In particular you should consider dropping custom triggers prior to any upgrades.
0
 

Author Comment

by:datatechcorp
Comment Utility
Hi Paul...

Thank you for pointing that out...and yes, we know...and we do...that is...drop & recreate all custom triggers...during any upgrade/update/etc.  This is for NCR CounterPoint, just so you're aware.  Do you happen to do any work with/for CounterPoint?  Just curios...Thanks!...Mark
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Wouldn't it make far more sense to drop and recreate the computed columns if necessary?  Triggers are a much, much bigger risk than computed columns.
0
 

Author Comment

by:datatechcorp
Comment Utility
Hmmm...perhaps...but then, what if (yes, a longshot, but a possibility nonetheless)...they just happen to match up the SAME fieldnames...as those used in the custom computed columns...and have Views, reports, and other things tied to it.

Having implemented this system for 15+ years, it's always just been cleaner (again, with CounterPoint, specifically)...to simply drop & recreate the trigger.  But I appreciate your wisdom and insight here, it's very well received :-)

Thanks!...Mark

P.S....BTW Scott...could you please revisit that other Open Question that I've got open?  If not, I'll have to close it out, and recreate it as a new question...because since you've responded, we've had no activity.  Please let me know...Thanks!...Mark
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You're creating a new column with that name anyway, right?  I don't see the difference in that, only in how that column gets populated.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I do not work for NCR. But I know vendors and their support policies.

I have to agree with Scott here. If you have already added a column to the table populating it via a trigger won't make it any safer.

You would need to remove it before an upgrade. If you choose a naming convention for custom items - and be consistent with this - that helps.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Maybe prefix all of your own columns with "custom__" or something similar that they are extremely unlikely to duplicate in their names :-) .
0
 

Author Comment

by:datatechcorp
Comment Utility
OK...acknowledged and understood.  All of the points you've all made are quite valid...Thanks!...Mark
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 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

9 Experts available now in Live!

Get 1:1 Help Now