CASE WHEN and Update of Table

Question

Post reply

CASE WHEN and Update of Table

SteveEClarke

SSCertifiable

Points: 5161
More actions
November 13, 2013 at 4:29 am

#279140

I am looking at updating a table with a trigger and have attached the code - but am getting basic errors with the case functionality - it is not parsing.
CREATE TRIGGER trgDocumentHeaderExchangeRateDefault
ON dbo.DocumentHeaders
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Operation Char(10)
SET @Operation= 'X' -- Default Value or NO Update to be performed
IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) SET @Operation = 'UPDATE'
IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) SET @Operation = 'INSERT'
IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) SET @Operation = 'X'
BEGIN
-- Update Transactions
IF @Operation = 'UPDATE'
BEGIN
CASE WHEN IsNumeric( DocumentHeaders.CustomText13 ) = 1 THEN
WHEN Cast(DocumentHeaders.CustomText13 as decimal(14,5)) = 0
SET DocumentHeaders.CustomText13 = '1'
ELSE
SET DocumentHeaders.CustomText13 = '1'
END
END
ELSE
-- Insert Transactions
IF @Operation = 'INSERT'
BEGIN
CASE WHEN IsNumeric( DocumentHeaders.CustomText13 ) = 1 THEN
WHEN Cast(DocumentHeaders.CustomText13 as decimal(14,5)) = 0
SET DocumentHeaders.CustomText13 = '1'
ELSE
SET DocumentHeaders.CustomText13 = '1'
END
END
ELSE
END
GO
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 1

I don't think the case statement will work that way. Maybe something more like this?

update <table>

set <field> =

case when <field> = 0 then 1

else 2

end

Before you do that, I'd see if there was a better way to do this. If you are using a trigger, wouldn't it be better to make this change in the sproc that's making the original call to update the table?

Mark

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

SteveEClarke (11/13/2013)
I am looking at updating a table with a trigger and have attached the code - but am getting basic errors with the case functionality - it is not parsing.
CREATE TRIGGER trgDocumentHeaderExchangeRateDefault
ON dbo.DocumentHeaders
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Operation Char(10)
SET @Operation= 'X' -- Default Value or NO Update to be performed
IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) SET @Operation = 'UPDATE'
IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) SET @Operation = 'INSERT'
IF NOT EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) SET @Operation = 'X'
BEGIN
-- Update Transactions
IF @Operation = 'UPDATE'
BEGIN
CASE WHEN IsNumeric( DocumentHeaders.CustomText13 ) = 1 THEN
WHEN Cast(DocumentHeaders.CustomText13 as decimal(14,5)) = 0
SET DocumentHeaders.CustomText13 = '1'
ELSE
SET DocumentHeaders.CustomText13 = '1'
END
END
ELSE
-- Insert Transactions
IF @Operation = 'INSERT'
BEGIN
CASE WHEN IsNumeric( DocumentHeaders.CustomText13 ) = 1 THEN
WHEN Cast(DocumentHeaders.CustomText13 as decimal(14,5)) = 0
SET DocumentHeaders.CustomText13 = '1'
ELSE
SET DocumentHeaders.CustomText13 = '1'
END
END
ELSE
END
GO

What exactly are you trying to do here? Your trigger has tons of syntax and logic errors. Remember that a trigger is an event where you can run queries, you can't just hack together code snippets and expect them to work.

I don't think you need a trigger for this at all. But even if you do what is the point of @Operation? Also, the value will never be 'X' because this trigger only fires for an INSERT or an UPDATE. It is logically impossible for both the deleted and the inserted tables to be empty in any trigger for any operation.

Once we get around the challenges we are left with a very simple piece of logic. If there are rows in deleted, this is an update, else it is an insert.

Below I cobbled together something but I can't make heads or tails of what you are actually trying to do. I have no idea what your actual primary key is so you will have to provide that. Also, I suspect that the logic I put in here is not really what you want. One final question, why is CustomText13 defined as a character type column when you only put decimal values in it?

CREATE TRIGGER trgDocumentHeaderExchangeRateDefault

ON dbo.DocumentHeaders

AFTER INSERT,UPDATE

AS

BEGIN

SET NOCOUNT ON;

IF EXISTS(SELECT * FROM deleted) --This is an update

Update DocumentHeaders

set CustomText13 = '1'

from DocumentHeaders dh

join deleted d on dh.PrimaryKey = d.PrimaryKey

where Cast(DocumentHeaders.CustomText13 as decimal(14,5)) = 0

else --This is an insert

Update DocumentHeaders

set CustomText13 = '1'

from DocumentHeaders dh

join inserted i on dh.PrimaryKey = i.PrimaryKey

where Cast(DocumentHeaders.CustomText13 as decimal(14,5)) = 0

END

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 3

Thanks for your reply -

The problem is that there is a "Custom Database" setup which people have used Text/Char fields to store numeric in - what I am trying to achieve is;

When a record is added, a trigger will fire and check that there is a numeric entered into this field - it is exchange rate, so a default value of 1 ( ONE ) needs to be there - users are entering text and/or leaving blank which is causing problems.

The trigger that was created is already working and in production.

I agree with the "X" - but this was just a catch all at the end of the script.

Regards

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

SteveEClarke (11/14/2013)
Thanks for your reply -
The problem is that there is a "Custom Database" setup which people have used Text/Char fields to store numeric in - what I am trying to achieve is;

That stinks. Sounds like you are likely fighting an uphill battle there.

When a record is added, a trigger will fire and check that there is a numeric entered into this field - it is exchange rate, so a default value of 1 ( ONE ) needs to be there - users are entering text and/or leaving blank which is causing problems.
The trigger that was created is already working and in production.
I agree with the "X" - but this was just a catch all at the end of the script.
Regards

The code I posted should accomplish this. If you need to keep the extra craziness you will need to use some update statements like the ones I posted inside the "blocks". That make sense?