SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CASE WHEN and Update of Table


CASE WHEN and Update of Table

Author
Message
SteveEClarke
SteveEClarke
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1345 Visits: 457
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.
Mark Eckeard
Mark Eckeard
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 505
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
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63057 Visits: 17959
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SteveEClarke
SteveEClarke
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1345 Visits: 457
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
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63057 Visits: 17959
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?

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SteveEClarke
SteveEClarke
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1345 Visits: 457
Thanks for your help and thoughts - it is always the trouble when you change jobs and inherit systems that "The Users think best!"....

Its a real shame that we have to put triggers into the system to pick up changes and/or problems that the GUI should be doing !

Regards
Steve

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

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search