Triggers

Updated on 2024-12-06 GMT+08:00

View PDF

**Table 1** Trigger types
No.	Oracle Database	GaussDB	Difference
1	DML TRIGGER	Supported, with differences.	GaussDB: Compound DML triggers are not supported.
2	SYSTEM TRIGGER	Not supported.	-

**Table 2** CREATE triggers
No.	Oracle Database	GaussDB	Difference
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.	-

**Table 3** ALTER trigger
No.	Oracle Database	GaussDB	Difference
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.

**Table 4** drop trigger
No.	Oracle Database	GaussDB	Difference
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.

The *_TRIGGERS views in Oracle Database collect information about triggers. The views in GaussDB are different from those in Oracle Database. For details, see "DB_TRIGGERS", "ADM_TRIGGERS", "MY_TRIGGERSDB_TRIGGERS", "ADM_TRIGGERS", and "MY_TRIGGERS" in "System Catalogs and System Views > System Views > Other System Views" in Developer Guide.

**Table 5** Compatibilities of nested, package, and standalone subprograms
No.	Oracle Database	GaussDB	Difference
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.	-

**Table 6** RETURN statements
No.	Oracle Database	GaussDB
1	Functions	Supported.
2	Procedures	Supported.
3	Anonymous blocks	Supported.

**Table 7** Function-related parameters
No.	Oracle Database	GaussDB	Difference
1	DETERMINISTIC	Supported, with differences.	In GaussDB, it is IMMUTABLE.
2	PARALLEL_ENABLE	Not supported.	-
3	PIPELINED	Not supported.	-
4	RESULT_CACHE	Not supported.	-

**Table 8** Parameter formats
No.	Oracle Database	GaussDB
1	IN	Supported.
2	OUT	Supported.
3	IN OUT	Supported.

**Table 9** CREATE statements
No.	Oracle Database	GaussDB	Difference
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.	-

**Table 10** ALTER statements
No.	Oracle Database	GaussDB	Difference
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.

**Table 11** DROP statements
No.	Oracle Database	GaussDB	Difference
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.	-

**Table 12** Keywords related to functions, procedures, and anonymous blocks
No.	Oracle Database	GaussDB	Difference
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.	-

**Table 13** Keywords related to exception handling
No.	Oracle Database	GaussDB	Difference
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.	-