如何创建具有唯一名称表，然后最好使用单个触发器将另一个表中的数据插入到该表中

Question

我希望有一个空表，当有人向其中插入数据时，我希望该表立即被复制到唯一的表名，然后原始表被截断，以防同样的事情再次发生。

我试过用触发器来做这件事，然后用触发器来执行过程，我试着只用触发器来做这件事，但我发现两者都有问题。

CREATE OR REPLACE TRIGGER REPLICATE_PDU_TABLE
AFTER INSERT ON PDU 
DECLARE V_TABLENAME VARCHAR2(30) := 'PDU_IN_PROGRESS_ || TO_CHAR(SYSDATE, 'YYYYMMDDHHMISS');
CURSOR C1 IS
SELECT * FROM PDU;
V_PDU C1%ROWTYPE;
BEGIN 
EXECUTE IMMEDIATE 'CREATE TABLE ' || V_TABLENAME || ' AS SELECT * FROM PDU';
OPEN C1;
FETCH C1 INTO PDU ;
CLOSE C1;
INSERT INTO V_TABLENAME
VALUES (
V_PDU.TARGETSCHEMA,
V_PDU.PRODUCTIONSCHEMA,
V_PDU.PRODUCTIONDATABASE,
V_PDU.TABLE_NAME,
V_PDU.DRIVER_TABLE,
V_PDU.MANDATORY_JOIN,
V_PDU.ADDITIONAL_JOINS,
V_PDU.TABLE_COMPRESSION);
END TRIGGER REPLICATE_PDU_TABLE;

这个“可能”实际上是有效的，但是我不能触发编译，因为"INSERT INTO V_TABLENAME“不起作用，因为这个表还不存在。这是我代码的第一部分，创建一个唯一的表，在插入时将原始表中的所有数据复制到其中。

Answer 1

这真的不是我们在触发器中应该做的事情。有一些复杂的处理过程，很难管理。例如，在不能创建触发器时应该发生什么？或者如果截断失败？

禁止在触发器中发出commits更是雪上加霜。所有DDL语句都会提交(每个语句两个)，因此从触发器触发DDL语句的唯一方法是使用autonomous_transaction杂注。这是一个嵌套事务，这意味着它看不到表的当前内容。所以整个事情有点混乱。

下面是一个思维实验，它说明了你需要做的事情：

create or replace trigger replicate_pdu_table
    after insert on pdu 
declare 
    -- note use the twenty-hour clock in mask
    v_tablename varchar2(30) := 'PDU_IN_PROGRESS_' || to_char(sysdate, 'YYYYMMDDHH24MISS');
    procedure create_table is
        pragma autonomous_transaction;
    begin
        execute immediate 'create table ' || v_tablename || ' as select * from pdu where 1=2';
    end create_table;
    procedure truncate_pdu is
        pragma autonomous_transaction;
    begin
        execute immediate 'truncate table pdu';
    end truncate_pdu;
begin 
    create_table;
    execute immediate 'insert into ' || v_tablename || ' select * from pdu';
    truncate_pdu;
end;
/

问题在于截断:它抛出

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

原因是当我们在pdu中插入行时触发器会触发。也就是说，在我们可以发出提交之前。因此，主会话在pdu上有一个锁，因此自治事务不能进行截断。这是触发器使用的绝对拦截器:您需要将其作为过程运行，可能是从轮询作业或通过队列调用。

更广泛的问题是，你为什么要这样做？动态创建表的事务性方法总是一种气味。除了大量杂乱的模式之外，还有我在简介中提到的失败的风险。另外，使用创建的表的进程如何知道它的名称？您的整个流程将由动态SQL构建而成。这是一场维护和支持的噩梦。

如果不知道你正在尝试实现的细节，就不可能提出更好的方法。但请放心，你肯定有更好的方法可用。

Answer 2

Execute insert语句也使用execute immediate命令，如下所示：

CREATE OR REPLACE TRIGGER REPLICATE_PDU_TABLE
AFTER INSERT ON PDU 
DECLARE V_TABLENAME VARCHAR2(30) := 'PDU_IN_PROGRESS_ || TO_CHAR(SYSDATE, 'YYYYMMDDHHMISS');
CURSOR C1 IS
SELECT * FROM PDU;
V_PDU C1%ROWTYPE;
BEGIN 
EXECUTE IMMEDIATE 'CREATE TABLE ' || V_TABLENAME || ' AS SELECT * FROM PDU';
OPEN C1;
FETCH C1 INTO v_PDU ;
CLOSE C1;
EXECUTE IMMEDIATE '
INSERT INTO '|| V_TABLENAME || '
VALUES ( '||
V_PDU.TARGETSCHEMA ||','||
V_PDU.PRODUCTIONSCHEMA ||','||
V_PDU.PRODUCTIONDATABASE ||','||
V_PDU.TABLE_NAME ||','||
V_PDU.DRIVER_TABLE ||','||
V_PDU.MANDATORY_JOIN||','||
V_PDU.ADDITIONAL_JOINS ||','||
V_PDU.TABLE_COMPRESSION ||')';
END TRIGGER REPLICATE_PDU_TABLE;

您还可以查看以下链接，了解如何使用execute immediate语句。Execute Immediate Example