From: fniles on
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: Sylvain Lafontaine on
Inside a trigger, you can use the functions Update() and Columns_Updated()
to determine which columns were affected; ie, which columns have been set
explicitely in the Update or Insert statement.

Affected doesn't mean changed: if you update the value to the same value it
was before, it will be marked as affected even if its value has remained the
same. I suppose that this is what you need.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"fniles" <fniles(a)pfmail.com> wrote in message
news:%236qgGAo2KHA.3844(a)TK2MSFTNGP05.phx.gbl...
> 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
>
>
>
>
>