Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSSQL TRIGGER WITH CASE

Posted on 2014-09-09
18
Medium Priority
?
160 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 27

Expert Comment

by:Shaun Kline
ID: 40312936
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 27

Accepted Solution

by:
Shaun Kline earned 2000 total points
ID: 40312973
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
ID: 40312977
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
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.

 

Author Comment

by:datatechcorp
ID: 40313001
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 27

Expert Comment

by:Shaun Kline
ID: 40313109
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
ID: 40313120
....when I first try to create the Trigger...
0
 
LVL 27

Expert Comment

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

Author Comment

by:datatechcorp
ID: 40313153
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 70

Expert Comment

by:Scott Pletcher
ID: 40313193
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
 

Author Comment

by:datatechcorp
ID: 40313202
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 49

Expert Comment

by:PortletPaul
ID: 40313304
>>"... 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
ID: 40313341
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 70

Expert Comment

by:Scott Pletcher
ID: 40313345
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
ID: 40313357
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 70

Expert Comment

by:Scott Pletcher
ID: 40313366
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 49

Expert Comment

by:PortletPaul
ID: 40313385
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 70

Expert Comment

by:Scott Pletcher
ID: 40313411
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
ID: 40313431
OK...acknowledged and understood.  All of the points you've all made are quite valid...Thanks!...Mark
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

916 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