Creating a trigger to only run when a new table is being created
17,370
CREATE OR REPLACE TRIGGER
create_table_trigger
AFTER CREATE ON SCHEMA
BEGIN
IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN
....
END;
For a list of EVENT attributes, refer to this page
http://ist.marshall.edu/ist480adbp/plsql_triggers.html (link is down)
Wayback machine link to the contents of the dead link above: https://web.archive.org/web/20110809071133/http://ist.marshall.edu/ist480adbp/plsql_triggers.html
As far as I know, dictionary_obj_type is one of TABLE|SEQUENCE|PROCEDURE|INDEX|FUNCTION|TYPE|PACKAGE
And dictionary_obj_name is just the name of the table/sequence/proc/etc.
- dictionary_obj_type Returns the type of the dictionary object on which the DDL operation that fired the trigger occurred.
- dictionary_obj_name Returns the name of the dictionary object on which the DDL operation that fired the trigger occurred.
Author by
Tolga E
Updated on June 12, 2022Comments
-
Tolga E almost 2 years
I know that I can use this to create DDL create trigger;
CREATE OR REPLACE TRIGGER create_table_trigger AFTER CREATE ON SCHEMA DECLARE BEGIN END;
Problem is this trigger would run on DDLs like 'Create sequence'; how can I only execute this for 'Create Table' DDLs?
-
Tolga E over 13 yearsOh also, how can I get the name of the Table that's being created?
-
Veeresh P over 5 yearsI am getting following error.. (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIGGER create_table_trigger AFTER ALTER ON SCHEMA BEGIN IF DICTIONARY_OBJ_T' at line 1")
-
saritonin almost 5 years@VeereshP this is an oracle solution, not MySQL.