Trigger
in [Programming SQL]
|
Prev: ActiveMQ uplock preventing transaction log backup and truncation.
Next: Logical Error Very simple I'm sure!
From: fniles on 12 Apr 2010 16:38 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 12 Apr 2010 20:24
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 > > > > > |