Solved

MSSQL TRIGGER WITH CASE

Posted on 2014-09-09
18
142 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 26

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 26

Accepted Solution

by:
Shaun Kline earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 26

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 26

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 69

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 48

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 69

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 69

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 48

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 69

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to count the days a record spends in a step 21 51
AJAX pass along a variable 3 47
What is this datetime? 1 20
SQL Recursion schedule 13 19
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Backup & Restore 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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

821 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