oracle 用trigger动态创建表格

[dongdong1006]

各位大哥大姐.
小弟在创建trigger 遇到一个问题.帮帮忙吧!
在表eqdb_platform 中插入记录 之前先检查 相关联另外的一个表格是不是存在. 如果不存在就创建.
CREATE OR REPLACE TRIGGER eqdb_platform_bi
BEFORE INSERT ON eqdb_platform
FOR EACH ROW
DECLARE
v_count int ;
v_platform VARCHAR2(10) ;
v_area VARCHAR2(10) ;
v_tablename VARCHAR2(20);
sql_stmt VARCHAR2(200);
BEGIN
v_platform := :NEW.platform;
v_tablename := :NEW.tablename;
v_area := :NEW.area;
SELECT count(*) INTO v_count FROM user_tables
WHERE table_name=v_tablename;
IF v_count < 1 THEN
sql_stmt := 'CREATE TABLE '|| v_tablename || ' AS SELECT * FROM EQDB_DETAILS_J971 WHERE rownum < 1';
EXECUTE IMMEDIATE sql_stmt;
END IF;
END;

trigger 创建时 没问题. 执行时出错.
SQL> /

Trigger created.

SQL> INSERT INTO eqdb_platform(platform,tablename,vendor,area) values ('J973','EQDB_DETAILS_J973','Teradyne','Probe')
2 ;
INSERT INTO eqdb_platform(platform,tablename,vendor,area) values ('J973','EQDB_DETAILS_J973','Teradyne','Probe')
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "PROBEWEB.EQDB_PLATFORM_BI", line 15
ORA-04088: error during execution of trigger 'PROBEWEB.EQDB_PLATFORM_BI'


SQL>

[tangren]

1、在触发器中一般是不能commit的，因为触发器与引发这个触发器操作（INSERT，UPDATE）为同一事务。
2、DDL操作实际上是对系统表进行操作，它会执行隐式提交（在执行DDL开始前就会commit，完成也会commit)；
3、可以使用自治事务，但自治事务看不到父事务的数据。
4、建议少用触发器来实现业务逻辑。太多的触发器可能会不好维护。

[dongdong1006]

[Quote=引用 1 楼 inthirties 的回复:]
根据错误提示，cannot COMMIT in a trigger

这里的execute immediate是一个动态的create表的DDL语句，隐性的comit数据
所以报错，

你可以用自治事务预定义一下这个trigger
PRAGMA AUTONOMOUS_TRANSACTION
[/Quote]

我在trigger 定义 里加了PRAGMA AUTONOMOUS_TRANSACTION
还是报错, 但我是有权限建表的.
INSERT INTO eqdb_platform(platform,tablename,vendor,area) values ('J973','EQDB_DETAILS_J973','Teradyne','Probe')
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "PROBEWEB.EQDB_PLATFORM_BI", line 19
ORA-04088: error during execution of trigger 'PROBEWEB.EQDB_PLATFORM_BI'

[ojuju10]

楼主可以在代码中实现，或者在存储过程里面实现

不要放在触发器中

[crazylaa]

[Quote=引用 3 楼 dongdong1006 的回复:]
引用 1 楼 inthirties 的回复:
根据错误提示，cannot COMMIT in a trigger

这里的execute immediate是一个动态的create表的DDL语句，隐性的comit数据
所以报错，

你可以用自治事务预定义一下这个trigger
PRAGMA AUTONOMOUS_TRANSACTION


我在trigger 定义 里加了PRAGMA AUTONOMOUS_TRANSACTION
还是报错, 但我是有权限建表的.
INSERT INTO eqdb_platform(platform,tablename,vendor,area) values ('J973','EQDB_DETAILS_J973','Teradyne','Probe')
            *
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "PROBEWEB.EQDB_PLATFORM_BI", line 19
ORA-04088: error during execution of trigger 'PROBEWEB.EQDB_PLATFORM_BI'
[/Quote]
那你有没有权限select那张表啊？

[inthirties]

[Quote=引用 3 楼 dongdong1006 的回复:]
引用 1 楼 inthirties 的回复:
根据错误提示，cannot COMMIT in a trigger

这里的execute immediate是一个动态的create表的DDL语句，隐性的comit数据
所以报错，

你可以用自治事务预定义一下这个trigger
PRAGMA AUTONOMOUS_TRANSACTION


我在trigger 定义 里加了PRAGMA AUTONOMOUS_TRANSACTION
还是报错, 但我是有权限建表的.
INSERT INTO eqdb_platform(platform,tablename,vendor,area) values ('J973','EQDB_DETAILS_J973','Teradyne','Probe')
            *
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "PROBEWEB.EQDB_PLATFORM_BI", line 19
ORA-04088: error during execution of trigger 'PROBEWEB.EQDB_PLATFORM_BI'
[/Quote]

现在已经是另一个错误了；

以前在procedure里用动态sql来cretate表也出现过这个问题。

你执行一下grant create table to 用户名;以后再试试。

[inthirties]

根据错误提示，cannot COMMIT in a trigger

这里的execute immediate是一个动态的create表的DDL语句，隐性的comit数据
所以报错，

你可以用自治事务预定义一下这个trigger
PRAGMA AUTONOMOUS_TRANSACTION

[dongdong1006]

[Quote=引用 6 楼 inthirties 的回复:]
引用 3 楼 dongdong1006 的回复:
引用 1 楼 inthirties 的回复:
根据错误提示，cannot COMMIT in a trigger

这里的execute immediate是一个动态的create表的DDL语句，隐性的comit数据
所以报错，

你可以用自治事务预定义一下这个trigger
PRAGMA AUTONOMOUS_TRANSACTION


我在trigger 定义 里加了PRAGMA AUTONOMOUS_TRANSACTION
还是报错, 但我是有权限建表的.
INSERT INTO eqdb_platform(platform,tablename,vendor,area) values ('J973','EQDB_DETAILS_J973','Teradyne','Probe')
            *
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "PROBEWEB.EQDB_PLATFORM_BI", line 19
ORA-04088: error during execution of trigger 'PROBEWEB.EQDB_PLATFORM_BI'


现在已经是另一个错误了；

以前在procedure里用动态sql来cretate表也出现过这个问题。

你执行一下grant create table to 用户名;以后再试试。
[/Quote]

嗯, 好了. 谢谢 加AUTHID CURRENT_USER 就行了
CREATE OR REPLACE PROCEDURE create_table
( v_tablename IN VARCHAR2
)
AUTHID CURRENT_USER
AS
sql_stmt VARCHAR2(200);
v_count int;
BEGIN
SELECT count(*) INTO v_count FROM user_tables
WHERE table_name = v_tablename;
IF v_count < 1 THEN
sql_stmt := 'CREATE TABLE '||
v_tablename||
' AS SELECT * FROM EQDB_DETAILS_J971 WHERE rownum < 1';
DBMS_OUTPUT.PUT_LINE(sql_stmt);
EXECUTE IMMEDIATE sql_stmt;
ELSE
DBMS_OUTPUT.PUT_LINE('Table:'||v_tablename||' exists!');
END IF;
END create_table;