Triggers Extension

[MattShaw]

Hi Thomas,

We are thinking of switching from HSQLDB to H2 for our production
system. However in the HSQLDB Trigger Definition it passes through
the type of operation that the Trigger is performing i.e. UPDATE
AFTER, DELETE BEFORE, INSERT AFTER etc etc. We have logic in our
triggers that uses this information to determine what to do. Looking
at your trigger definition this isn't present. Would it be possible
to add this to the trigger definition to allow us to use your
database ?

Many Thanks

Matt

[MattShaw]

Hi,

Is there any likelyhood that this may get implemented ?

Regards

Matt Shaw

[Thomas Mueller]

Hi,

Do you mean the SQL column in INFORMATION_SCHEMA.TRIGGERS? Yes
currently only the name is listed. The plan was to list the create
trigger statement there. I will fix this. Or is there something else
missing?

create table test(id int primary key, name varchar);
create trigger test_del after delete on test call
"org.h2.test.db.TestTriggersConstraints";
create trigger test_ins before insert on test call
"org.h2.test.db.TestTriggersConstraints";
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;

TRIGGER_CATALOG TEST2
TRIGGER_SCHEMA PUBLIC
TRIGGER_NAME TEST_DEL
TRIGGER_TYPE DELETE
TABLE_CATALOG TEST2
TABLE_SCHEMA PUBLIC
TABLE_NAME TEST
BEFORE FALSE
JAVA_CLASS org.h2.test.db.TestTriggersConstraints
QUEUE_SIZE 1024
NO_WAIT FALSE
REMARKS
SQL PUBLIC.TEST_DEL
ID 23

TRIGGER_CATALOG TEST2
TRIGGER_SCHEMA PUBLIC
TRIGGER_NAME TEST_INS
TRIGGER_TYPE INSERT
TABLE_CATALOG TEST2
TABLE_SCHEMA PUBLIC
TABLE_NAME TEST
BEFORE TRUE
JAVA_CLASS org.h2.test.db.TestTriggersConstraints
QUEUE_SIZE 1024
NO_WAIT FALSE
REMARKS
SQL PUBLIC.TEST_INS
ID 25

In the next release, the column SQL will contains:
CREATE TRIGGER PUBLIC.TEST_INS BEFORE INSERT ON PUBLIC.TEST QUEUE 1024
CALL "org.h2.test.db.TestTriggersConstraints"

Regards,
Thomas

[MattShaw]

Hi Thomas,

Thanks for getting back to me. I think we may have our wires
crossed. I'm talking about the Java Trigger Interface. In HSQLDB as
part of the Trigger Interface class the type of trigger is passed
through to the derived Trigger Callback. What I would like is that
the Trigger type i.e. DELETE AFTER, INSERT BEFORE etc etc, as a
constant , would be passed through to the init() method of the Trigger
class.

Does that make sense ?

Thanks

Matt

On Dec 6, 6:56 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

Sorry. Of course, your request makes sense. What about:

public interface Trigger {

int INSERT = 1, UPDATE = 2, DELETE = 4;

/**
* This method is called by the database engine once when
initializing the trigger.
*
* @param conn a connection to the database
* @param schemaName the name of the schema
* @param triggerName the name of the trigger used in the CREATE
TRIGGER statement
* @param tableName the name of the table
* @param before whether the fire method is called before or after
the operation is performed
* @param type the operation type: INSERT, UPDATE, or DELETE
*/
void init(Connection conn, String schemaName, String triggerName,
String tableName, boolean before, int type) throws SQLException;

... rest as before ...

Is this OK?

Regards,
Thomas