Update column before Inserting

Matt Steele

Update column before Inserting
I would like to find a way to update a column's value PRIOR to inserting the
record. I would like to replace one character with another. I thought I
could use a BEFORE INSERT trigger to do that but after reading a couple
articles, it seems like you cannot. I tried the following:

CREATE TRIGGER SCPART1
BEFORE INSERT ON THOMPARTY
REFERENCING NEW AS NNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NNN.FIRST_NM = REPLACE(NNN.FIRST_NM, '&', '+');
END


And received:


DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "BEFORE". SOME SYMBOLS
THAT MIGHT BE LEGAL ARE: AFTER
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHSM5R SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 32 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF' X'00000020' X'00000000' SQL DIAGNOSTIC
INFORMATION
BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.
RETCODE = 8



I REALLY do not want to do the insert followed by an update (AFTER INSERT
trigger). I would like to find some way to do the replace prior to actually
inserting into the table. I realize I can do it in COBOL code but this is
also inserted from online. I also realize they can do the replace also but
I would like to do it in one place (ie. like a trigger). Our schedule for
getting online changes into production is not very flexible.

Does anyone know the best way to do this?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Churn

Re: Update column before Inserting
(in response to Matt Steele)
Matt,

Try this;

CREATE TRIGGER SCPART1
*NO CASCADE* BEFORE INSERT ON THOMPARTY
REFERENCING NEW AS NNN
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
SET NNN.FIRST_NM = REPLACE(NNN.FIRST_NM, '&', '+');
END

And thank you for the opportunity to look up a new scalar function...

--
David Churn
President, Heart of America DB2 Users Group

On 1/5/07, Matt Steele <[login to unmask email]> wrote:
>
> I would like to find a way to update a column's value PRIOR to inserting
> the
> record. I would like to replace one character with another. I thought I
> could use a BEFORE INSERT trigger to do that but after reading a couple
> articles, it seems like you cannot. I tried the following:
>
> CREATE TRIGGER SCPART1
> BEFORE INSERT ON THOMPARTY
> REFERENCING NEW AS NNN
> FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> SET NNN.FIRST_NM = REPLACE(NNN.FIRST_NM, '&', '+');
> END
>
>
> And received:
>
>
> DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "BEFORE". SOME SYMBOLS
> THAT MIGHT BE LEGAL ARE: AFTER
> DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
> DSNT415I SQLERRP = DSNHSM5R SQL PROCEDURE DETECTING ERROR
> DSNT416I SQLERRD = 0 0 0 -1 32 0 SQL DIAGNOSTIC INFORMATION
> DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
> X'FFFFFFFF' X'00000020' X'00000000' SQL DIAGNOSTIC
> INFORMATION
> BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.
> RETCODE = 8
>
>
>
> I REALLY do not want to do the insert followed by an update (AFTER INSERT
> trigger). I would like to find some way to do the replace prior to
> actually
> inserting into the table. I realize I can do it in COBOL code but this is
> also inserted from online. I also realize they can do the replace also
> but
> I would like to do it in one place (ie. like a trigger). Our schedule for
> getting online changes into production is not very flexible.
>
> Does anyone know the best way to do this?
>
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm