Topic
  • 4 replies
  • Latest Post - ‏2008-01-22T19:48:03Z by Stan
SystemAdmin
SystemAdmin
1525 Posts

Pinned topic Is it possible to use conditions in triggers?

‏2006-09-04T09:30:28Z |
Is it possible to use conditions in triggers like clause "WHEN" on DB2?
I'd like to run trigger action only on some conditions.
F.e. to check field update and log message if value changed:
code
CREATE TRIGGER APP.TRK_FORMATS_STARTFMT AFTER UPDATE
OF STARTFMT
ON STMT.VIPFORMAT
REFERENCING OLD AS OLDT NEW AS NEWT
FOR EACH ROW MODE DB2SQL
INSERT INTO STMT.VIPBASELOG (MTYPE,SENDER,MESSAGE)
values(0,'Changes',OLDT.TYPENAME||'.STARTFMT changed by '||CURRENT_USER||' from to ');
[/code]
But this trigger will insert messages on every update of field STARTFMT even if value of this field was not changed.
DB2 allow to use clause WHEN in Trigger defenition as:
code
... FOR EACH ROW MODE DB2SQL WHEN (OLDT.STARTFMTNEWT.STARTFMT) INSERT ...
[/code]
But this clause is not supported by Derby, as I understand.
If I modify trigger action as:
code
INSERT INTO STMT.VIPBASELOG (MTYPE,SENDER,MESSAGE)
SELECT * FROM (
values(0,'Changes',OLDT.TYPENAME||'.STARTFMT changed by '||CURRENT_USER||' from to ')
) AS T WHERE OLDT.STARTFMTNEWT.STARTFMT;
[/code]

I receive a such exception:

Target exception: org.apache.derby.client.am.SqlException: Column 'OLDT.TYPENAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'OLDT.TYPENAME' is not a column in the target table.
Updated on 2008-01-22T19:48:03Z at 2008-01-22T19:48:03Z by Stan
  • Jean_Anderson
    Jean_Anderson
    179 Posts

    Re: Is it possible to use conditions in triggers?

    ‏2006-09-05T20:22:25Z  
    Derby doesn't support the WHEN clause for triggers. DERBY-534 (http://issues.apache.org/jira/browse/DERBY-534) requests support for WHEN, but it hasn't been implemented yet.

    Incidently, documentation for the "create trigger" statement is here:
    http://publib.boulder.ibm.com/infocenter/cscv/v10r1/topic/com.ibm.cloudscape.doc/rrefsqlj43125.html?resultof=%22%63%72%65%61%74%65%22%20%22%63%72%65%61%74%22%20%22%74%72%69%67%67%65%72%22%20

    The upcoming Derby 10.2 release might let you achieve what you want because triggers will be able to invoke procedures.

    So, backing up a little, a 10.1 trigger can invoke a function (but not a procedure); however, a function can't perform an insert, update, or delete.

    The upcoming 10.2 release supports invoking procedures in a trigger, and procedures can perform insert, update, and delete. (See http://issues.apache.org/jira/browse/DERBY-551 .)

    More information about functions and procedures is at http://wiki.apache.org/db-derby/DerbySQLroutines .
    Information about the 10.2 release, including a snapshot for testing, is at http://wiki.apache.org/db-derby/TenTwoRelease .

    I hope this helps.

    regards,

    -jean
  • SystemAdmin
    SystemAdmin
    1525 Posts

    Re: Is it possible to use conditions in triggers?

    ‏2007-04-24T06:35:19Z  
    Hi,

    It is possible to use conditions in triggers if you use stored procedures.

    in the java method that is called in the stored procedure. you can actually include logic that controls the insertion of data.

    Some thing like this.

    CREATE PROCEDURE UPDATEDIOTABLE(DP1 INTEGER)
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    MODIFIES SQL DATA
    EXTERNAL NAME 'dekoh.portal.fwk.persistence.search.Procedures.updateDIOTable';
    CREATE TRIGGER INSERTDIOONDELETE
    AFTER DELETE ON DEKOH_PORTAL_COLLECTION_DIO_TABLE
    REFERENCING OLD AS OLD
    FOR EACH ROW MODE DB2SQL
    CALL UPDATEDIOTABLE(OLD.DIO_ID);

    And in java class

    See that jdbc:default:collection is used to get the the connection instance to avoid deadlock.

    public class Procedures
    {

    public static void updateDIOTable(int dioId)
    {

    Connection conn = null;
    try {
    conn = DriverManager.getConnection("jdbc:default:connection");
    int notInAnyCollectionInstanceId = getNotInAnyCollectionInstanceId(dioId, conn);

    if (notInAnyCollectionInstanceId == 0) {
    return;
    }
    String discriminatorType = getDiscriminatorType(dioId, conn);
    if (discriminatorType == null || discriminatorType.equals("DioCollection")) {
    return;
    }
    if (getRelationshipCountForDio(dioId, conn) != 0) {
    return;
    }
    insertRecordInRelationshipTable(notInAnyCollectionInstanceId, dioId, conn);
    }
    catch (SQLException e) {
    e.printStackTrace();
    }
    }

  • SystemAdmin
    SystemAdmin
    1525 Posts

    Re: Is it possible to use conditions in triggers?

    ‏2008-01-18T14:19:34Z  
    Hi,

    It is possible to use conditions in triggers if you use stored procedures.

    in the java method that is called in the stored procedure. you can actually include logic that controls the insertion of data.

    Some thing like this.

    CREATE PROCEDURE UPDATEDIOTABLE(DP1 INTEGER)
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    MODIFIES SQL DATA
    EXTERNAL NAME 'dekoh.portal.fwk.persistence.search.Procedures.updateDIOTable';
    CREATE TRIGGER INSERTDIOONDELETE
    AFTER DELETE ON DEKOH_PORTAL_COLLECTION_DIO_TABLE
    REFERENCING OLD AS OLD
    FOR EACH ROW MODE DB2SQL
    CALL UPDATEDIOTABLE(OLD.DIO_ID);

    And in java class

    See that jdbc:default:collection is used to get the the connection instance to avoid deadlock.

    public class Procedures
    {

    public static void updateDIOTable(int dioId)
    {

    Connection conn = null;
    try {
    conn = DriverManager.getConnection("jdbc:default:connection");
    int notInAnyCollectionInstanceId = getNotInAnyCollectionInstanceId(dioId, conn);

    if (notInAnyCollectionInstanceId == 0) {
    return;
    }
    String discriminatorType = getDiscriminatorType(dioId, conn);
    if (discriminatorType == null || discriminatorType.equals("DioCollection")) {
    return;
    }
    if (getRelationshipCountForDio(dioId, conn) != 0) {
    return;
    }
    insertRecordInRelationshipTable(notInAnyCollectionInstanceId, dioId, conn);
    }
    catch (SQLException e) {
    e.printStackTrace();
    }
    }

    I tried to define procedure which modifies SQL data but still could not make it work for following reasons -
    1. One can not use "CALL" in triggers, that means one has to use FUNCTION not PROCEDURE to be called from trigger using "VALUES (functionName(param1, param2))" in statement
    2. In FUNCTION one can not modify SQL data.
    So ultimately I could not modify SQL data using trigger.
    Is there any other way I can achieve this?
  • Stan
    Stan
    267 Posts

    Re: Is it possible to use conditions in triggers?

    ‏2008-01-22T19:48:03Z  
    I tried to define procedure which modifies SQL data but still could not make it work for following reasons -
    1. One can not use "CALL" in triggers, that means one has to use FUNCTION not PROCEDURE to be called from trigger using "VALUES (functionName(param1, param2))" in statement
    2. In FUNCTION one can not modify SQL data.
    So ultimately I could not modify SQL data using trigger.
    Is there any other way I can achieve this?
    I assume you are using an older version of Derby? Version 10.2 and 10.3 support CALL in triggers. See:

    http://issues.apache.org/jira/browse/DERBY-551