创建新表时，在插入之前创建触发器生成ID列值 [英] Creating a trigger generating ID column value before insert when new tables is created

问题描述

当在模式中创建表(MYSCHEMA)时，我需要创建一个触发器，该触发器会在插入每个已创建的表中之前生成ID列(根据序列). 我怎么能意识到这一点?

When a table create in schema (MYSCHEMA), I need to create a trigger that generate a ID column (from sequence) before insert in each created table.. How can I realize this?

我知道，如何通过触发器和序列来实现ID列的生成，如下所示:

I know, how I can realize generation of ID column through trigger and sequence, something like this:

CREATE OR REPLACE TRIGGER TR1
  BEFORE INSERT ON TB1
  FOR EACH ROW
BEGIN
  SELECT SQ1.nextval
    INTO :new.primary_key_column
    FROM dual;
END;

但是我不知道如何使用BEFORE INSERT ...在我的架构中使用AFTER CREATE ON SCHEMA触发器在CREATE TABLE之后创建触发器.

But I don't know, how I can use AFTER CREATE ON SCHEMA trigger to create trigger after CREATE TABLE in my schema with BEFORE INSERT...

我已经编写了这段代码:

I've written this code:

CREATE OR REPLACE TRIGGER /*APPROOT*/after_create_table_trigger
AFTER CREATE ON APPROOT.SCHEMA
DECLARE 
TABLE_NAME VARCHAR2(100);
BEGIN
IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
SELECT ORA_DICT_OBJ_NAME INTO TABLE_NAME FROM DUAL;
EXECUTE IMMEDIATE 
('CREATE OR REPLACE TRIGGER id_table_gen
  BEFORE INSERT ON ' || TABLE_NAME ||
  ' FOR EACH ROW
   BEGIN
    SELECT APPROOT.AE_IDSEQ.NEXTVAL
     INTO :new.ID
     FROM dual;
   END;');
END IF;
END;
/

然后，我用一个字段创建了测试表-ID，但是我的触发器不起作用... 我认为使用事件属性函数ora_dict_obj_name的原因是错误的.

Then I've created test table with one field - ID, but my trigger doesn't work... I think the reason is wrong using of event attribute function ora_dict_obj_name.

有人可以给我一些建议吗?

Could somebody give me advice about this?

谢谢.

推荐答案

如果将架构​​名称放在DDL中，则工作正常.

works ok if i put the schema name in the DDL.

SQL> connect sys/test as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER after_create_table_trigger
  2  AFTER CREATE ON TEST.SCHEMA
  3  DECLARE
  4  TABLE_NAME VARCHAR2(100);
  5  BEGIN
  6  IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
  7  SELECT ORA_DICT_OBJ_NAME INTO TABLE_NAME FROM DUAL;
  8  EXECUTE IMMEDIATE
  9  ('CREATE OR REPLACE TRIGGER ID_TABLE_GEN
 10    BEFORE INSERT ON TEST.' || TABLE_NAME ||
 11    ' FOR EACH ROW
 12     BEGIN
 13      SELECT TEST.AE_IDSEQ.NEXTVAL
 14       INTO :new.ID
 15       FROM dual;
 16     END;');
 17  END IF;
 18  END;
 19  /

Trigger created.

SQL> connect test/test
Connected.
SQL> create table mytab(id number primary key, a varchar2(1));

Table created.

SQL> insert into mytab (a) values ('a');

1 row created.

SQL> select * From mytab;

        ID A
---------- -
         1 a


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

p.s.不需要

SELECT ORA_DICT_OBJ_NAME INTO TABLE_NAME FROM DUAL;

只需将其粘贴到命令中即可.

just paste it into the command.

CREATE OR REPLACE TRIGGER ID_TABLE_GEN
  BEFORE INSERT ON APPROOT.' || ORA_DICT_OBJ_NAME ||

这篇关于创建新表时，在插入之前创建触发器生成ID列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！