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_D
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[DTC_COPY_ATTRS_TO_D
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
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_D
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[DTC_COPY_ATTRS_TO_D
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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?
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?
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?
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?
ASKER
....when I first try to create the Trigger...
Please provide the updated trigger code. Are you running the CREATE TRIGGER SQL in SQL Server Management Studio?
ASKER
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_D
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 ''.
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_D
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 ''.
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
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
ASKER
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
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
>>"... 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.
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.
ASKER
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
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
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.
ASKER
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
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
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.
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.
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.
Maybe prefix all of your own columns with "custom__" or something similar that they are extremely unlikely to duplicate in their names :-) .
ASKER
OK...acknowledged and understood. All of the points you've all made are quite valid...Thanks!...Mark
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.