Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CASE WHEN and Update of Table Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 4:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:44 AM
Points: 170, Visits: 404
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.
Post #1513799
Posted Wednesday, November 13, 2013 6:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:38 AM
Points: 128, Visits: 486
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



Post #1513827
Posted Wednesday, November 13, 2013 8:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 11,949, Visits: 10,982
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 Moden's 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)
Post #1513908
Posted Thursday, November 14, 2013 9:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:44 AM
Points: 170, Visits: 404
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.
Post #1514340
Posted Thursday, November 14, 2013 9:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 11,949, Visits: 10,982
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 Moden's 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)
Post #1514350
Posted Monday, November 18, 2013 5:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:44 AM
Points: 170, Visits: 404
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.
Post #1515181
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse