[
https://issues.apache.org/jira/browse/PHOENIX-932?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
alex kamil updated PHOENIX-932:
-------------------------------
Description:
add a trigger feature, calling a SQL statement or stored procedure in Java on
DDL/DML changes
Syntax:
CREATE
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name
WHEN trigger_condition
FOR EACH ROW
CALL trigger_body
trigger_time: { BEFORE | AFTER }
trigger_condition: { condition AND|OR|NOT|>|<|= condition }
trigger_event: { UPSERT|CREATE|ALTER|DROP|DELETE}
trigger_body: { TRIGGERED_SQL_STATEMENT| TRIGGERED_JAVA_PROCEDURE}
TRIGGERED_SQL_STATEMENT*:{UPSERT|CREATE|ALTER|DROP|DELETE}
TRIGGER_JAVA_PROCEDURE: '<Jar location|default=$HBASE_HOME/lib>', '<Class
Name>', '<Method Name>(args)'
*TRIGGERED_SQL_STATEMENT has the following limitations (similar to Derby):
It must not contain any dynamic parameters ( ? ).
It must not create, alter, or drop the table upon which the trigger is defined.
It must not add an index to or remove an index from the table on which the
trigger is defined.
It must not add a trigger to or drop a trigger from the table upon which the
trigger is defined.
It must not commit or roll back the current transaction or change the isolation
level.
It must not execute a CALL statement.
Before triggers cannot have INSERT, UPDATE or DELETE statements as their action.
Example:
CREATE TRIGGER trigger1
AFTER ALTER ON table1
WHEN (table1.col1 > table1.col2)
CALL UPSERT INTO test.targetTable(col1, col2) SELECT col3, col4 FROM
test.sourceTable WHERE col5 < 100
or
CREATE TRIGGER trigger1
AFTER UPSERT ON table1
FOR EACH ROW
WHEN (table1.col1 > table1.col2)
CALL 'myTriggerClass' , 'myMethod(table1.col1, table1.col2)';
References:
http://docs.oracle.com/cd/F49540_01/DOC/java.815/a64686/04_call2.htm ,
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
http://db.apache.org/derby/docs/10.1/ref/rrefsqlj43125.html
for now we implement a trigger in the application, e.g. intercept DDL/DML calls
with a wrapper method in our phoenix client and either synchronously call
corresponding trigger method, or put a record into a queue and handle it
asynchronously by the part of the application responsible for receiving a
trigger.
was:
add a trigger feature, calling a SQL statement or stored procedure in Java on
DDL/DML changes
Syntax:
CREATE
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name
WHEN trigger_condition
FOR EACH ROW
CALL trigger_body
trigger_time: { BEFORE | AFTER }
trigger_condition: { condition AND|OR|NOT|>|<|= condition }
trigger_event: { UPSERT|CREATE|ALTER|DROP|DELETE}
trigger_body: { TRIGGERED_SQL_STATEMENT| TRIGGERED_JAVA_PROCEDURE}
TRIGGERED_SQL_STATEMENT:{UPSERT|CREATE|ALTER|DROP|DELETE}*
TRIGGER_JAVA_PROCEDURE: '<Jar location|default=$HBASE_HOME/lib>', '<Class
Name>', '<Method Name>(args)'
*TRIGGERED_SQL_STATEMENT has the following limitations (similar to Derby):
It must not contain any dynamic parameters (?).
It must not create, alter, or drop the table upon which the trigger is defined.
It must not add an index to or remove an index from the table on which the
trigger is defined.
It must not add a trigger to or drop a trigger from the table upon which the
trigger is defined.
It must not commit or roll back the current transaction or change the isolation
level.
It must not execute a CALL statement.
Before triggers cannot have INSERT, UPDATE or DELETE statements as their action.
Example:
CREATE TRIGGER trigger1
AFTER ALTER ON table1
WHEN (table1.col1 > table1.col2)
CALL UPSERT INTO test.targetTable(col1, col2) SELECT col3, col4 FROM
test.sourceTable WHERE col5 < 100
or
CREATE TRIGGER trigger1
AFTER UPSERT ON table1
FOR EACH ROW
WHEN (table1.col1 > table1.col2)
CALL 'myTriggerClass' , 'myMethod(table1.col1, table1.col2)';
References:
http://docs.oracle.com/cd/F49540_01/DOC/java.815/a64686/04_call2.htm ,
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
http://db.apache.org/derby/docs/10.1/ref/rrefsqlj43125.html
for now we implement a trigger in the application, e.g. intercept DDL/DML calls
with a wrapper method in our phoenix client and either synchronously call
corresponding trigger method, or put a record into a queue and handle it
asynchronously by the part of the application responsible for receiving a
trigger.
> db triggers
> -----------
>
> Key: PHOENIX-932
> URL: https://issues.apache.org/jira/browse/PHOENIX-932
> Project: Phoenix
> Issue Type: New Feature
> Reporter: alex kamil
>
> add a trigger feature, calling a SQL statement or stored procedure in Java on
> DDL/DML changes
> Syntax:
> CREATE
> TRIGGER trigger_name
> trigger_time trigger_event
> ON tbl_name
> WHEN trigger_condition
> FOR EACH ROW
> CALL trigger_body
> trigger_time: { BEFORE | AFTER }
> trigger_condition: { condition AND|OR|NOT|>|<|= condition }
> trigger_event: { UPSERT|CREATE|ALTER|DROP|DELETE}
> trigger_body: { TRIGGERED_SQL_STATEMENT| TRIGGERED_JAVA_PROCEDURE}
> TRIGGERED_SQL_STATEMENT*:{UPSERT|CREATE|ALTER|DROP|DELETE}
> TRIGGER_JAVA_PROCEDURE: '<Jar location|default=$HBASE_HOME/lib>', '<Class
> Name>', '<Method Name>(args)'
> *TRIGGERED_SQL_STATEMENT has the following limitations (similar to Derby):
> It must not contain any dynamic parameters ( ? ).
> It must not create, alter, or drop the table upon which the trigger is
> defined.
> It must not add an index to or remove an index from the table on which the
> trigger is defined.
> It must not add a trigger to or drop a trigger from the table upon which the
> trigger is defined.
> It must not commit or roll back the current transaction or change the
> isolation level.
> It must not execute a CALL statement.
> Before triggers cannot have INSERT, UPDATE or DELETE statements as their
> action.
> Example:
> CREATE TRIGGER trigger1
> AFTER ALTER ON table1
> WHEN (table1.col1 > table1.col2)
> CALL UPSERT INTO test.targetTable(col1, col2) SELECT col3, col4 FROM
> test.sourceTable WHERE col5 < 100
> or
> CREATE TRIGGER trigger1
> AFTER UPSERT ON table1
> FOR EACH ROW
> WHEN (table1.col1 > table1.col2)
> CALL 'myTriggerClass' , 'myMethod(table1.col1, table1.col2)';
> References:
> http://docs.oracle.com/cd/F49540_01/DOC/java.815/a64686/04_call2.htm ,
> http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
> http://db.apache.org/derby/docs/10.1/ref/rrefsqlj43125.html
> for now we implement a trigger in the application, e.g. intercept DDL/DML
> calls with a wrapper method in our phoenix client and either synchronously
> call corresponding trigger method, or put a record into a queue and handle it
> asynchronously by the part of the application responsible for receiving a
> trigger.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
