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/