MSSQL TRIGGER WITH CASE

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
datatechcorpAsked:
Who is Participating?
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
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
 
Shaun KlineLead Software EngineerCommented:
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
 
datatechcorpAuthor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
datatechcorpAuthor Commented:
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
 
Shaun KlineLead Software EngineerCommented:
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
 
datatechcorpAuthor Commented:
....when I first try to create the Trigger...
0
 
Shaun KlineLead Software EngineerCommented:
Please provide the updated trigger code. Are you running the CREATE TRIGGER SQL in SQL Server Management Studio?
0
 
datatechcorpAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
datatechcorpAuthor Commented:
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
 
PortletPaulfreelancerCommented:
>>"... 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
 
datatechcorpAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
datatechcorpAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
PortletPaulfreelancerCommented:
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
 
Scott PletcherSenior DBACommented:
Maybe prefix all of your own columns with "custom__" or something similar that they are extremely unlikely to duplicate in their names :-) .
0
 
datatechcorpAuthor Commented:
OK...acknowledged and understood.  All of the points you've all made are quite valid...Thanks!...Mark
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.

All Courses

From novice to tech pro — start learning today.