Prev: call sp from another sp
Next: Return 0 (zero) when null
From: fniles on 12 Apr 2010 12:25
CREATE TABLE tblA (
Symbol varchar(50) NOT NULL,
APIFormat varchar(50) NULL,
DataFormat varchar(50) NULL
)

I would like to do the following:
If a new row is added or APIFormat is edited, and DataFormat is not being
updated, I
would like to set DataFormat like this:
If RIGHT(APIFORMAT,1) = '#', then SET DataFormat =
LEFT(APIFORMAT,LEN(APIFORMAT)-1) + '0'
If right(apiformat,1) <> '#', then SET DataFormat = APIFORMAT

If I do the below trigger, if I don't update DataFormat, it sets it
correctly, but if I do an update on DataFormat, it will be overriden by the
trigger.
For ex, if I do this:
UPDATE tblA SET APIFORMAT = '#.##',DATAFORMAT='#.###00' WHERE SYMBOL = 'FFA'
APIFORMAT will be #.##
and DATAFORMAT will be #.#0, instead of #.###00

Thanks

CREATE TRIGGER trgtblA
ON tblA FOR INSERT, UPDATE AS
IF @@rowcount = 0 RETURN;
IF TRIGGER_NESTLEVEL(object_ID(tblA)) > 1 RETURN;
SET NOCOUNT ON;

UPDATE tblA
SET DataFormat = CASE WHEN RIGHT(INSERTED.APIFormat,1) = '#'
THEN LEFT(INSERTED.APIFormat,LEN(INSERTED.APIFormat)-1) + '0'
ELSE INSERTED.APIFormat
END
FROM INSERTED INSERTED
JOIN tblAON INSERTED.Symbol = tblA.Symbol
RETURN;
GO




From: Stefan Hoffmann on 13 Apr 2010 04:37
hi,

On 12.04.2010 18:25, fniles wrote:
> If I do the below trigger, if I don't update DataFormat, it sets it
> correctly, but if I do an update on DataFormat, it will be overriden by the
> trigger.

Take a look at the UPDATE() function:

http://msdn.microsoft.com/en-us/library/ms187326.aspx

> CREATE TRIGGER trgtblA
> ON tblA FOR INSERT, UPDATE AS
As long as it doesn't work I would use two explicit triggers and merge
them eventually in the end.


mfG
--> stefan <--
 | 
Pages: 1
Prev: call sp from another sp
Next: Return 0 (zero) when null