[
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 similar to Apache Derby, calling a SQL statement 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_SQL_STATEMENT:{UPSERT|CREATE|ALTER|DROP|DELETE}
TRIGGERED_SQL_STATEMENT has the following limitations:
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 table2(col1, col2) SELECT col3, col4 FROM table3
References:
http://db.apache.org/derby/docs/10.1/ref/rrefsqlj43125.html
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 similar to Apache Derby, calling a SQL statement 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_SQL_STATEMENT:{UPSERT|CREATE|ALTER|DROP|DELETE}
> TRIGGERED_SQL_STATEMENT has the following limitations:
> 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 table2(col1, col2) SELECT col3, col4 FROM table3
> References:
> http://db.apache.org/derby/docs/10.1/ref/rrefsqlj43125.html
--
This message was sent by Atlassian JIRA
(v6.2#6252)
