Triggers

1

DML TRIGGER

Supported, with differences.

GaussDB: Compound DML triggers are not supported.

2

SYSTEM TRIGGER

Not supported.

-

1

CREATE syntaxes:

CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] TRIGGER plsql_trigger_source

Supported, with differences.

GaussDB does not support EDITIONABLE | NONEDITIONABLE, but supports some behaviors of plsql_trigger_source.

2

plsql_trigger_source ::= syntax:

[schema.] trigger_name

[ sharing_clause ] [ default_collation_clause ]

{ simple_dml_trigger

| instead_of_dml_trigger

| compound_dml_trigger

| system_trigger

}

Supported, with differences.

GaussDB: The schema, sharing_clause, and default_collation_clause are not supported.

3

simple_dml_trigger ::= syntax:

{ BEFORE | AFTER } dml_event_clause [ referencing_clause ] [ FOR EACH ROW ]

[ trigger_edition_clause ] [ trigger_ordering_clause ]

[ ENABLE | DISABLE ] [ WHEN ( condition ) ] trigger_body

Supported, with differences.

GaussDB does not support referencing_clause, referencing_clause (instead, from referencing_table is used), trigger_edition_clause, trigger_ordering_clause, and ENABLE | DISABLE. It supports some behaviors of trigger_body.

No error is reported in GaussDB when a statement-level BEFORE/AFTER TRIGGER is created in a view without INSTEAD OF TRIGGER. An error is reported when DML is executed.

4

dml_event_clause ::= syntax:

{ DELETE | INSERT | UPDATE [ OF column [, column ]... ] }

[ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] }...

ON [ schema.] { table | view }

Not supported.

-

5

trigger_body ::= syntax:

{ plsql_block | CALL routine_clause }

Supported, with differences.

In GaussDB, plsql_block cannot be declared as PRAGMA AUTONOMOUS_TRANSACTION. For the second branch, similar syntax is supported. That is, a function is executed in EXECUTE PROCEDURE function_name ( arguments ); mode. The function needs to be defined by users. The function must be declared without parameters and the return type must be trigger. The function is executed when the trigger is enabled.

6

instead_of_dml_trigger ::= syntax:

INSTEAD OF { DELETE | INSERT | UPDATE } [ OR { DELETE | INSERT | UPDATE } ]...

ON [ NESTED TABLE nested_table_column OF ] [ schema. ] noneditioning_view

[ referencing_clause ] [ FOR EACH ROW ]

[ trigger_edition_clause ] [ trigger_ordering_clause ]

[ ENABLE | DISABLE ] trigger_body

Supported, with differences.

GaussDB: The NESTED TABLE nested_table_column OF, referencing_clause, trigger_edition_clause, trigger_ordering_clause, and ENABLE | DISABLE are not supported.

7

compound_dml_trigger ::= syntax:

CREATE trigger FOR dml_event_clause ON view

COMPOUND TRIGGER

INSTEAD OF EACH ROW IS BEGIN

statement;

END INSTEAD OF EACH ROW;

Not supported.

-

8

system_trigger ::= syntax:

{ BEFORE | AFTER | INSTEAD OF }

{ ddl_event [OR ddl_event]... | database_event [OR database_event ]... }

ON { [schema.] SCHEMA | [ PLUGGABLE ] DATABASE }

[ trigger_ordering_clause ] [ ENABLE | DISABLE ] trigger_body

Not supported.

-

1

ALTER TRIGGER [ schema. ] trigger_name

{ trigger_compile_clause

| { ENABLE | DISABLE }

| RENAME TO new_name

| { EDITIONABLE | NONEDITIONABLE }

} ;

Supported, with differences.

GaussDB: The schema, trigger_compile_clause, { ENABLE | DISABLE }, and { EDITIONABLE | NONEDITIONABLE } are not supported.

1

DROP TRIGGER [ schema. ] trigger ;

Supported, with differences.

GaussDB does not support schemas. You need to add ON table_name to the end of trigger_name.

1

Nested subprograms (subblocks)

Supported, with differences.

Overloading, definition of autonomous transactions, and SETOF are not supported. Only one qualifier can reference nested subprograms or variables of nested subprograms.

2

Package subprograms

Supported.

-

3

Standalone subprograms (including functions and procedures)

Supported.

-

4

Anonymous blocks

Supported.

-

1

Functions

Supported.

2

Procedures

Supported.

3

Anonymous blocks

Supported.

1

DETERMINISTIC

Supported, with differences.

In GaussDB, it is IMMUTABLE.

2

PARALLEL_ENABLE

Not supported.

-

3

PIPELINED

Not supported.

-

4

RESULT_CACHE

Not supported.

-

1

IN

Supported.

2

OUT

Supported.

3

IN OUT

Supported.

1

CREATE FUNCTION

Supported, with differences.

GaussDB does not support the IF NOT EXISTS syntax.

The sharing_clause is not supported. Only the invoker_rights_clause with the function attribute is supported. The keyword [ EDITIONABLE | NONEDITIONABLE ] is not supported.

For details about the syntax, see "SQL Reference > SQL Syntax > C > CREATE FUNCTION" in Developer Guide.

2

CREATE LIBRARY

Not supported.

-

3

CREATE PACKAGE

Supported, with differences.

GaussDB does not support the IF NOT EXISTS syntax.

The sharing_clause is not supported. Only the invoker_rights_clause with the package attribute is supported. The keyword [ EDITIONABLE | NONEDITIONABLE ] is not supported.

For details about the syntax, see "SQL Reference > SQL Syntax > C > CREATE PACKAGE" in Developer Guide.

4

CREATE PACKAGE BODY

Supported, with differences.

GaussDB does not support the IF NOT EXISTS syntax.

The sharing_clause and keyword [ EDITIONABLE | NONEDITIONABLE ] are not supported.

For details about the syntax, see "SQL Reference > SQL Syntax > C > CREATE PACKAGE" in Developer Guide.

5

CREATE PROCEDURE

Supported, with differences.

GaussDB does not support the IF NOT EXISTS syntax.

The sharing_clause and its following clauses are not supported. The keyword [ EDITIONABLE | NONEDITIONABLE ] is not supported.

For details about the syntax, see "SQL Reference > SQL Syntax > C > CREATE PROCEDURE" in Developer Guide.

6

CREATE TRIGGER

Supported, with differences.

For details about GaussDB syntax, see "SQL Reference > SQL Syntax > C > CREATE TRIGGER" in Developer Guide.

7

CREATE TYPE

Supported, with differences.

GaussDB does not support the varray, object type, and UNDER syntax.

For details about the syntax, see "SQL Reference > SQL Syntax > C > CREATE TYPE" in Developer Guide.

8

CREATE TYPE BODY

Not supported.

-

1

ALTER FUNCTION

Supported, with differences.

GaussDB does not support keywords [ EDITIONABLE | NONEDITIONABLE ], REUSE, SETTINGS, and DEBUG.

For details about the syntax, see "SQL Reference > SQL Syntax > A > ALTER FUNCTION" in Developer Guide.

2

ALTER LIBRARY

Not supported.

-

3

ALTER PACKAGE

Supported, with differences.

GaussDB does not support keywords [ EDITIONABLE | NONEDITIONABLE ], REUSE, SETTINGS, and DEBUG.

For details about the syntax, see "SQL Reference > SQL Syntax > A > ALTER PACKAGE" in Developer Guide.

4

ALTER PROCEDURE

Supported, with differences.

GaussDB does not support keywords [ EDITIONABLE | NONEDITIONABLE ], REUSE, SETTINGS, and DEBUG.

For details about the syntax, see "SQL Reference > SQL Syntax > A > ALTER PROCEDURE" in Developer Guide.

5

ALTER TRIGGER

Supported, with differences.

In GaussDB, only the trigger name can be modified.

For details about the syntax, see "SQL Reference > SQL Syntax > A > ALTER TRIGGER" in Developer Guide.

6

ALTER TYPE

Supported, with differences.

GaussDB supports only some statements.

For details about the syntax, see "SQL Reference > SQL Syntax > A > ALTER TYPE" in Developer Guide.

1

DROP FUNCTION

Supported.

-

2

DROP LIBRARY

Not supported.

-

3

DROP PACKAGE

Supported.

-

4

DROP PROCEDURE

Supported.

-

5

DROP TRIGGER

Supported, with differences.

The syntax in GaussDB is different.

For details about the syntax, see "SQL Reference > SQL Syntax > D > DROP TRIGGER" in Developer Guide.

6

DROP TYPE

Supported, with differences.

GaussDB does not support keywords FORCE and VALIDATE.

For details about the syntax, see "SQL Reference > SQL Syntax > D > DROP TYPE" in Developer Guide.

7

DROP TYPE BODY

Not supported.

-

1

ACCESSIBLE BY

Not supported.

-

2

AGGREGATE

Supported, with differences.

• GaussDB does not support Oracle Database's aggregate using [schema.] implementation_type.
• For details about GaussDB syntax, see "SQL Reference > SQL Syntax > C > CREATE AGGREGATE" in Developer Guide.

The syntax is different, but the implementation functions are the same.

3

DETERMINISTIC

Supported, with differences.

GaussDB supports the keyword DETERMINISTIC only in syntax, but does not support this function.

4

PIPE ROW

Not supported.

-

5

PIPELINED

Not supported.

-

6

SQL_MACRO

Not supported.

-

7

RESTRICT_REFERENCES

Not supported.

-

8

INLINE

Not supported.

-

1

EXCEPTION_INIT

Supported, with differences.

Binding with system error codes is not supported in GaussDB.

2

Exception

Supported.

-

3

Exception Handler

Supported.

-

4

SQLCODE

Supported.

-

5

SQLERRM

Supported.

-