(shardingsphere) branch master updated: Support Create Trigger with Assignment Statement (#29654)

[duanzhengqiang]

This is an automated email from the ASF dual-hosted git repository.

duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git


The following commit(s) were added to refs/heads/master by this push:
     new 45fad86141c Support Create Trigger with Assignment Statement (#29654)
45fad86141c is described below

commit 45fad86141c356e9fa8982c3ea354aa35786a73d
Author: ilyas ahsan <meilyasah...@gmail.com>
AuthorDate: Fri Jan 5 09:41:26 2024 +0700

    Support Create Trigger with Assignment Statement (#29654)
    
    add new line
---
 .../dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4    | 11 +++++------
 test/it/parser/src/main/resources/case/ddl/create-trigger.xml |  1 +
 .../src/main/resources/sql/supported/ddl/create-trigger.xml   |  7 ++++++-
 3 files changed, 12 insertions(+), 7 deletions(-)

diff --git a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4 
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
index 351b2523c46..315be7fc6b5 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
@@ -90,7 +90,6 @@ statement
         | gotoStatement
         | ifStatement
         | nullStatement
-        | executeImmediateStatement
         | openStatement
         | openForStatement
         | pipeRowStatement
@@ -119,7 +118,7 @@ assignStatementTarget
     ;
 
 placeholder
-    : COLON_ hostVariable=name (COLON_ indicatorVariable=name)?
+    : COLON_ hostVariable=name (DOT_ columnName)? (COLON_ 
indicatorVariable=name)?
     ;
 
 // TODO PL/SQL grammar more than expr
@@ -570,7 +569,7 @@ plsqlTriggerSource
     ;
 
 simpleDmlTrigger
-    : (BEFORE | AFTER) dmlEventClause
+    : (BEFORE | AFTER) dmlEventClause (FOR EACH ROW)? triggerBody
     ;
 
 dmlEventClause
@@ -582,7 +581,7 @@ dmlEventElement
     ;
 
 systemTrigger
-    : (BEFORE | AFTER | INSTEAD OF) (ddlEvent (OR ddlEvent)* | databaseEvent 
(OR databaseEvent)*) ON ((PLUGGABLE? DATABASE) | (schemaName DOT_)? SCHEMA) body
+    : (BEFORE | AFTER | INSTEAD OF) (ddlEvent (OR ddlEvent)* | databaseEvent 
(OR databaseEvent)*) ON ((PLUGGABLE? DATABASE) | (schemaName DOT_)? SCHEMA) 
triggerBody
     ;
 
 ddlEvent
@@ -625,6 +624,6 @@ databaseEvent
     | (BEFORE | AFTER) SET CONTAINER
     ;
 
-executeImmediateStatement
-    : EXECUTE IMMEDIATE stringLiterals
+triggerBody
+    : plsqlBlock
     ;
diff --git a/test/it/parser/src/main/resources/case/ddl/create-trigger.xml 
b/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
index 92919d9f9fd..5ca32c2a1c3 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
@@ -26,4 +26,5 @@
     <create-trigger sql-case-id="create_trigger_with_dml_event_clause" />
     <create-trigger sql-case-id="create_trigger_with_body" />
     <create-trigger 
sql-case-id="create_trigger_with_execute_immediate_statement" />
+    <create-trigger sql-case-id="create_trigger_with_assignment_statement" />
 </sql-parser-test-cases>
diff --git 
a/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml 
b/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
index 8335e1c2593..aafdd9e562a 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
@@ -28,7 +28,7 @@
             SELECT EVENTDATA()" db-types="SQLServer" />
 
     <sql-case id="create_trigger_with_create_view" value="CREATE TRIGGER tr1 
BEFORE INSERT ON t1 FOR EACH ROW BEGIN create view v1 as select 1; END" 
db-types="MySQL" />
-    <sql-case id="create_trigger_with_dml_event_clause" value="CREATE TRIGGER 
scott.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON scott.emp" 
db-types="Oracle" />
+    <sql-case id="create_trigger_with_dml_event_clause" value="CREATE TRIGGER 
scott.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON scott.emp BEGIN 
IF (IS_SERVERERROR (1017)) THEN NULL; ELSE NULL; END IF; END;" 
db-types="Oracle" />
     <sql-case id="create_trigger_with_body" value="CREATE TRIGGER log_errors
         AFTER SERVERERROR ON DATABASE
         BEGIN
@@ -45,4 +45,9 @@
                   execute immediate 'alter session sync with primary';
             end if;
         end;" db-types="Oracle" />
+    <sql-case id="create_trigger_with_assignment_statement" value="CREATE 
TRIGGER Contacts_BI
+            BEFORE INSERT ON Contacts FOR EACH ROW
+        BEGIN
+            :NEW.ID := Contacts_Seq.NEXTVAL;
+        END;" db-types="Oracle" />
 </sql-cases>