触发器，用于将同一行复制到另一个表中

Question

如何获取表1到表2中的值；将根据表1的列(订单(复制行

表1:

ID       TICKETID USERNAME FIRSTNAME LASTNAME ORDERS STATUS
SEL00007            Hema1     Hema    Sri       3      New
SEL00008            Romi1     Romi     T        2      New

表2:

ID       TICKETID USERNAME FIRSTNAME LASTNAME ORDERS STATUS
SEL00007            Hema1     Hema    Sri       3      New
SEL00007            Hema1     Hema    Sri       3      New
SEL00007            Hema1     Hema    Sri       3      New
SEL00008            Romi1     Romi     T        2      New
SEL00008            Romi1     Romi     T        2      New

这是我在参考了的许多其他帖子后想到的

CREATE OR REPLACE TRIGGER Duplicate_Rows
AFTER INSERT OR UPDATE ON Table1
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE N_Ord NUMBER;
BEGIN
N_Ord := (SELECT (Orders) from Table2);
INSERT INTO UserTable2 (TICKETID,username,firstName,lastName,Status)
SELECT :New.TICKETID,:New.username,:New.firstName,:New.lastName,:New.Status
FROM dual
CONNECT BY LEVEL <= N_Ord; 
END Duplicate_Rows;
/

请帮忙！！

Answer 1

关闭；不需要将CCD_ 1单独选择为变量；它无论如何都会失败，因为表正在发生变化，所以触发器看不到它。

触发器：

SQL> CREATE OR REPLACE TRIGGER trg_ai_t1 AFTER
2    INSERT ON table1
3    FOR EACH ROW
4  BEGIN
5    INSERT INTO table2(
6      id,
7      ticketid,
8      username,
9      firstname,
10      lastname,
11      orders,
12      status
13    )
14      SELECT :new.id,
15             :new.ticketid,
16             :new.username,
17             :new.firstname,
18             :new.lastname,
19             :new.orders,
20             :new.status
21      FROM dual CONNECT BY
22        level <= :new.orders;
23
24  END;
25  /
Trigger created.

插入table1:

SQL> INSERT INTO table1(
2    id,
3    ticketid,
4    username,
5    firstname,
6    lastname,
7    orders,
8    status
9  )VALUES(
10    'SEL00007',
11    NULL,
12    'Hema1',
13    'Hema',
14    'Sri',
15    3,
16    'New'
17  );
1 row created.

结果：

SQL> SELECT *
2  FROM table1;
ID           TICKETID USERNAME   FIRST LASTN     ORDERS STATU
---------- ---------- ---------- ----- ----- ---------- -----
SEL00007              Hema1      Hema  Sri            3 New
SQL> SELECT *
2  FROM table2;
ID           TICKETID USERNAME   FIRST LASTN     ORDERS STATU
---------- ---------- ---------- ----- ----- ---------- -----
SEL00007              Hema1      Hema  Sri            3 New
SEL00007              Hema1      Hema  Sri            3 New
SEL00007              Hema1      Hema  Sri            3 New
SQL>