Trigger usage

alain pary

Trigger usage

Hello, 

I would like to use a trigger for insert and update of row in tab1 . The purpose is to save in COL2  a normize for of COL1  like 

COL2 = UPPER(TRIM(COL1)) 

and be sure that this col2 is accurate in any change if col1. 

I try the following statement 

CREATE TRIGGER NORMALIZE NO CASCADE
AFTER
UPDATE OF COL1 ON TAB1 REFERENCING NEW AS NROW
FOR EACH ROW
BEGIN ATOMIC
SET NROW.COL2 = UPPER(NROW.COL1)
END

 

Do you have already used this kind of trigger or is it a bad idea. 

 

regards 

 

Alain PAry

Eddy Coppens

RE: Trigger usage
(in response to alain pary)

Alain,

In Db2 LUW I would choose for a generated column instead of a trigger, don't know if this can be the case on z/OS.

 

--

Eddy Coppens

BiteStrike BVBA

Michael Hannan

RE: Trigger usage
(in response to alain pary)

Alain,

You did not mention the purpose. If the purpose is for search on this column in an index, you can use Index On Expression, instead of populating a denormalised Calculated column. A lot easier.

If you do go with a trigger, this is a case for a BEFORE type of trigger, which can perform better, since does not need to update the column, especially if the new column is indexed. AFTER trigger would cause extra update to row and index entries to be moved, leaving behind pseudo deleted RIDs.

You would need a BEFORE trigger on both INSERT and on UPDATE.

My comments are for zOS. Adjust accordingly for other DB2. I have used both Index On Expression and also Triggers very successfully for "denormalised columns". 
 
In Reply to alain pary:

I would like to use a trigger for insert and update of row in tab1 . The purpose is to save in COL2  a normize for of COL1  like 

COL2 = UPPER(TRIM(COL1)) 

and be sure that this col2 is accurate in any change if col1. 

I try the following statement 

CREATE TRIGGER NORMALIZE NO CASCADE
AFTER
UPDATE OF COL1 ON TAB1 REFERENCING NEW AS NROW
FOR EACH ROW
BEGIN ATOMIC
SET NROW.COL2 = UPPER(NROW.COL1)
END


Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 03, 2018 - 08:54 AM (Europe/Berlin)

alain pary

RE: Trigger usage
(in response to Michael Hannan)

Hello Michael ,

We have already use index on expression but the normalization expression is a little bit more complicated

REPLACE(UPPER (TRANSLATE (COL1, ''eeeeeeaaaaauuuuiiooccy'',''éèêëÈÉàâäÀÂùûüÜîïôöçÇÿ'')), '' '','''')

and  all users must know the normalization process to be sure to do select in the same way.

we can also create a view with the normalize form , but we try to implement a trigger to explore  this feature and find an elegant solution

The Eddy's solution is certainly nice , but i don't know it it implementation in Z/os is in the IBM project .

 

regards 

Alain

Michael Hannan

RE: Trigger usage
(in response to alain pary)

Alain,

Yes I understand. Index on Expression is largely used to tune existing queries and hence match them exactly.

A BEFORE type of trigger should be a good solution.
 
In Reply to alain pary:

Hello Michael ,

We have already use index on expression but the normalization expression is a little bit more complicated

REPLACE(UPPER (TRANSLATE (COL1, ''eeeeeeaaaaauuuuiiooccy'',''éèêëÈÉàâäÀÂùûüÜîïôöçÇÿ'')), '' '','''')

and  all users must know the normalization process to be sure to do select in the same way.

we can also create a view with the normalize form , but we try to implement a trigger to explore  this feature and find an elegant solution

The Eddy's solution is certainly nice , but i don't know it it implementation in Z/os is in the IBM project .

 

regards 

Alain



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Peter Vanroose

Re: Trigger usage
(in response to alain pary)

Alain,

We're doing exactly this, since more than 10 years, on our "persons" table (mainly for search reasons):
convert the name into a "normalized" one (stripping non-letters, to uppercase, and to non-accented)
and placing that normalized value in an extra column by using two AFTER triggers.

(The design is pre-v9, that's why we don't use index on expression instead.)

We never changed this design, and it's still working like a charm. So I believe it's indeed a good idea.

Here are the two triggers:

CREATE TRIGGER TPGPIN1 AFTER INSERT ON TPTPERSONS
REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
UPDATE TPTPERSONS SET PNAMEX = NORMALISED_NAME(PNAME) WHERE PNO = N.PNO
CREATE TRIGGER TPGPUP1 AFTER UPDATE OF PNAME ON TPTPERSONS
REFERENCING NEW AS N OLD AS O FOR EACH ROW MODE DB2SQL
WHEN (O.PNAME <> N.PNAME) UPDATE TPTPERSONS SET PNAMEX = NORMALISED_NAME(PNAME) WHERE PNO = N.PNO

As you can see, the conversion is not hard-coded into the triggers:
instead, an SQL UDF is called which does the transformation.

This way, it's much easier for us to make sure every application (e.g. implementing name comparison) is doing the same conversion. (We also have a COBOL routine doing exactly the same conversion, which is used in places where the host variable is first converted before passing it to Db2.)

For those interested, here is the implementation of the UDF:

CREATE FUNCTION NORMALISED_NAME(NAME VARCHAR(1024)) RETURNS VARCHAR(1024)
  BEGIN
    DECLARE N VARCHAR(1024);
    IF NAME = '' THEN RETURN ''; END IF;
    SET NAME = UPPER(TRANSLATE(NAME,
        'AAAAAAAEEEEIIIIOOOOOOUUUUYYCNAAAAAAAEEEEIIIIOOOOOOUUUUYCN',
        'áàâäãåæéèêëíìîïóòôöõøúùûüÿýçñÁÀÂÄÃÅÆÉÈÊËÍÌÎÏÓÒÔÖÕØÚÙÛÜÝÇÑ'));
    SET N = REPLACE(TRANSLATE(NAME,
        ' ','ABCDEFGHIJKLMNOPQRSTUVWXYZ&@0123456789'),' ','');
    IF N <> '' THEN SET NAME = TRANSLATE(NAME,' ',N); END IF;
    RETURN REPLACE(REPLACE(NAME,'IJ','Y'),' ','');
  END


In Reply to Alain Pary:

I would like to use a trigger for insert and update of row in tab1 . The purpose is to save in COL2  a normalized form of COL1  like  COL2 = UPPER(TRIM(COL1))  and be sure that this col2 is accurate in any change if col1.

Do you have already used this kind of trigger or is it a bad idea.

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

alain pary

Re: Trigger usage
(in response to Peter Vanroose)

Tank You Peter, 

A second update to do , It is a little bit heavy , but as you say ," it's still working like a charm."

we are implementing this.

Regards 

Alain