SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-24-2008, 02:52 PM
| ||||
| ||||
 Schema Level Trigger difficulties Hi all. I was wondering if any body had a solution to the following. I have a schema level trigger.... ..... AFTER CREATE ON SCHEMA ....... CreateMyTableLevelTrigger(mytable); END; Problem is when I create a table the procedure executes, when this procedute tries to CREATE the trigger for the new table Oracle tries to run the SCHEMA trigger again. Is there a way I can only run this schema trigger for tables?? Is the a way to create the table trigger without firing the schem trigger?? Thanks in advance.      |
|
02-24-2008, 02:53 PM
| |||
| |||
| Re: Schema Level Trigger difficulties scott@ORA92> CREATE OR REPLACE PROCEDURE CreateMyTableLevelTrigger 2 (p_table IN VARCHAR2) 3 AS 4 BEGIN 5 EXECUTE IMMEDIATE 6 'CREATE OR REPLACE TRIGGER tr_' || p_table 7 || ' BEFORE INSERT ON ' || p_table 8 || ' BEGIN NULL; END;'; 9 END CreateMyTableLevelTrigger; 10 / Procedure created. scott@ORA92> SHOW ERRORS No errors. scott@ORA92> CREATE OR REPLACE TRIGGER after_create_on_schema 2 AFTER CREATE ON SCHEMA 3 WHEN (ora_dict_obj_type = 'TABLE') 4 BEGIN 5 CreateMyTableLevelTrigger (ora_dict_obj_name); 6 END after_create_on_schema; 7 / Trigger created. scott@ORA92> SHOW ERRORS No errors. scott@ORA92> CREATE TABLE test_table (col1 NUMBER) 2 / Table created. scott@ORA92> SELECT object_name 2 FROM user_objects 3 WHERE object_name LIKE '%TEST_TABLE' 4 / OBJECT_NAME ---------------------------------------------------------------------------------------------------- TEST_TABLE TR_TEST_TABLE scott@ORA92> |
|
02-24-2008, 02:53 PM
| |||
| |||
| Re: Schema Level Trigger difficulties scott@ORA92> CREATE OR REPLACE PROCEDURE CreateMyTableLevelTrigger 2 (p_table IN VARCHAR2) 3 AS 4 BEGIN 5 EXECUTE IMMEDIATE 6 'CREATE OR REPLACE TRIGGER tr_' || p_table 7 || ' BEFORE INSERT ON ' || p_table 8 || ' BEGIN NULL; END;'; 9 END CreateMyTableLevelTrigger; 10 / Procedure created. scott@ORA92> SHOW ERRORS No errors. scott@ORA92> CREATE OR REPLACE TRIGGER after_create_on_schema 2 AFTER CREATE ON SCHEMA 3 WHEN (ora_dict_obj_type = 'TABLE') 4 BEGIN 5 CreateMyTableLevelTrigger (ora_dict_obj_name); 6 END after_create_on_schema; 7 / Trigger created. scott@ORA92> SHOW ERRORS No errors. scott@ORA92> CREATE TABLE test_table (col1 NUMBER) 2 / Table created. scott@ORA92> SELECT object_name 2 FROM user_objects 3 WHERE object_name LIKE '%TEST_TABLE' 4 / OBJECT_NAME ---------------------------------------------------------------------------------------------------- TEST_TABLE TR_TEST_TABLE scott@ORA92> |
|
02-24-2008, 02:54 PM
| |||
| |||
| Re: Schema Level Trigger difficulties Hi Barbara Thanks very much for your help. You sort of answered my question albeit indirectly. I was doing just as your example except that in the createtriggerprocedure I was initiating PRAGMA AUTOMOUS_TRANSACTION. This was my problem - I've been banging my head off my desk trying to fix this - can't even remember why i put the pragma there in the first place! If you get chance Ihave a couple of smaller sub-questions i need answers for. 1) AFTER ALTER ON SCHEMA .... how can I grab the new column i've just added? 2) AFTER RENAME ON SCHEMA....how can I find the new table name. ie RENAME a TO b - ora_dict_obj_name returns a....but i need b?? Thanks again Chris |
|
02-24-2008, 02:54 PM
| |||
| |||
| Re: Schema Level Trigger difficulties Have answered my own question now. ora_sql_txt (sql_text out ora_name_list_t) BINARY_INTEGER Returns the SQL text of the triggering statement in the OUT parameter. If the statement is long, it is broken up into multiple PL/SQL table elements. The function return value specifies how many elements are in the PL/SQL table. sql_text ora_name_list_t; stmt VARCHAR2(2000); .... n := ora_sql_txt(sql_text); FOR i IN 1..n LOOP stmt := stmt || sql_text(i); END LOOP; INSERT INTO event_table ('text of triggering statement: ' || stmt); thanks. chris. |
|
02-24-2008, 02:58 PM
| ||||
| ||||
| Re: Schema Level Trigger difficulties ChrisH wrote: > Have answered my own question now. > > ora_sql_txt (sql_text out > ora_name_list_t) > > BINARY_INTEGER > > Returns the SQL text of the triggering statement in the OUT parameter. > If the statement is long, it is broken up into multiple PL/SQL table > elements. The function return value specifies how many elements are in > the PL/SQL table. > sql_text ora_name_list_t; > stmt VARCHAR2(2000); > ... > n := ora_sql_txt(sql_text); > FOR i IN 1..n LOOP > stmt := stmt || sql_text(i); > END LOOP; > INSERT INTO event_table ('text > of triggering statement: ' || > stmt); > > thanks. > > chris. Another possible solution using SYSTEM EVENTS. www.psoug.org click on Morgan's Library click on System Events scroll down to ORA_DICT_OBJ_TYPE -- Daniel A. Morgan http://www.psoug.org damorgan@x.washington.edu (replace x with u to respond) |
Linear Mode
