create or replace trigger pcdrop_info after drop on dlcc.schema declare tab_name varchar2(100); g_tab_name varchar2(100); st varchar2(4000); begin tab_name:=sys.dictionary_obj_name; insert into drop_log (session_id, drop_time, ip_address, object_owner, object_name, object_type, drop_by_user) values(USERENV('SESSIONID'), sysdate, SYS_CONTEXT('USERENV','IP_ADDRESS'), sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_type, sys.login_user); delete from sde.layers where Upper(table_name)=upper(tab_name); If sql%rowcount=1 then
select G_TABLE_NAME into g_tab_name from sde.geometry_columns where upper(F_TABLE_NAME)=upper(tab_name); delete from sde.geometry_columns where Upper(F_TABLE_NAME)=upper(tab_name); begin st:='alter table '||g_tab_name||'table_name disable all triggers'; execute immediate st; st:='drop table '||g_tab_name; execute immediate st; st:='alter table '||g_tab_name||'table_name enable all triggers'; execute immediate st; exception when others then null; end; end if; end;改后如下 SQL> drop table tootherant; drop table tootherant * ERROR 位于第 1 行: ORA-04097: 在尝试删除或改变触发器时发生 DDL 冲突
首先st:='drop table '||g_tab_name;中drop 的g_tab_name是否需要记录到你的日志中?如果需要,那么二次触发应该保留,你可以通过类似的语句,保证不执行重复操作,第三次触发... delete from sde.layers where Upper(table_name)=upper(tab_name); If sql%rowcount=1 then如果不需要,建议在该触发器的开始部分,加上判断,如 select count(*) into l_count from sde.geometry_columns where upper(F_TABLE_NAME)=upper(tab_name); 来判断这是不是第二次触发,这样就不会有循环现象和误操作了。
我想最好还是了解一下geometry_columns 这个表是干什么用的。
geometry_columns 是arcgis中sde中一个表
CREATE TABLE "SDE"."GEOMETRY_COLUMNS" ("F_TABLE_CATALOG" VARCHAR2(32 byte), "F_TABLE_SCHEMA" VARCHAR2(32 byte) NOT NULL, "F_TABLE_NAME" VARCHAR2(160 byte) NOT NULL, "F_GEOMETRY_COLUMN" VARCHAR2(32 byte) NOT NULL, "G_TABLE_CATALOG" VARCHAR2(32 byte), "G_TABLE_SCHEMA" VARCHAR2(32 byte) NOT NULL, "G_TABLE_NAME" VARCHAR2(160 byte) NOT NULL, "STORAGE_TYPE" NUMBER, "GEOMETRY_TYPE" NUMBER, "COORD_DIMENSION" NUMBER, "MAX_PPR" NUMBER, "SRID" NUMBER NOT NULL, CONSTRAINT "GEOCOL_FK" FOREIGN KEY("SRID") REFERENCES "SDE"."SPATIAL_REFERENCES"("SRID"), CONSTRAINT "GEOCOL_PK" PRIMARY KEY("F_TABLE_SCHEMA", "F_TABLE_NAME", "F_GEOMETRY_COLUMN") USING INDEX TABLESPACE "SDE" STORAGE ( INITIAL 40K NEXT 16K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 1) PCTFREE 10 INITRANS 4 MAXTRANS 255) TABLESPACE "SDE" PCTFREE 10 PCTUSED 90 INITRANS 4 MAXTRANS 255 STORAGE ( INITIAL 40K NEXT 16K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 1) LOGGING
是删除表用户dlcc下表时触发
after drop on dlcc.schema 对整个schema触发的。
不过我希望贴主把具体问题提出来,比如为什么要这么做?
这样大家也许可以从别的方面来解决了。
if upper(g_tab_name)<>'SCHEMA' then
--在这儿
st:='drop table '||g_tab_name;
execute immediate st;
end if;
exception
when others then
null;
end;
--在这儿
st:='alter table '||g_tab_name||'table_name disable all triggers';//使触发器无效
execute immediate st; st:='drop table '||g_tab_name;
execute immediate st; st:='alter table '||g_tab_name||' enable all triggers';//使触发器有效
execute immediate st; end if;
drop table tootherant
*
ERROR 位于第 1 行:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-30512: 不能在事务处理超过一次修改DLCC.F157
ORA-06512: 在line 30
: bzszp(SongZip) 的方法我在试
贴主,你是在dlcc这个用户下建的表名叫schema的上面创建的drop触发器吗?
还是这个触发器对dlcc用户下所有的表drop都有效呢?
after drop on dlcc.schema
declare
tab_name varchar2(100);
g_tab_name varchar2(100);
st varchar2(4000);
begin
tab_name:=sys.dictionary_obj_name;
insert into drop_log
(session_id,
drop_time,
ip_address,
object_owner,
object_name,
object_type,
drop_by_user)
values(USERENV('SESSIONID'),
sysdate,
SYS_CONTEXT('USERENV','IP_ADDRESS'),
sys.dictionary_obj_owner,
sys.dictionary_obj_name,
sys.dictionary_obj_type,
sys.login_user);
delete from sde.layers where Upper(table_name)=upper(tab_name);
If sql%rowcount=1 then
select G_TABLE_NAME into g_tab_name from sde.geometry_columns where upper(F_TABLE_NAME)=upper(tab_name);
delete from sde.geometry_columns where Upper(F_TABLE_NAME)=upper(tab_name);
begin
st:='alter table '||g_tab_name||'table_name disable all triggers';
execute immediate st;
st:='drop table '||g_tab_name;
execute immediate st;
st:='alter table '||g_tab_name||'table_name enable all triggers';
execute immediate st;
exception
when others then
null;
end;
end if;
end;改后如下
SQL> drop table tootherant;
drop table tootherant
*
ERROR 位于第 1 行:
ORA-04097: 在尝试删除或改变触发器时发生 DDL 冲突
delete from sde.layers where Upper(table_name)=upper(tab_name);
If sql%rowcount=1 then如果不需要,建议在该触发器的开始部分,加上判断,如
select count(*) into l_count from sde.geometry_columns where upper(F_TABLE_NAME)=upper(tab_name);
来判断这是不是第二次触发,这样就不会有循环现象和误操作了。
VARCHAR2(32 byte), "F_TABLE_SCHEMA" VARCHAR2(32 byte) NOT
NULL, "F_TABLE_NAME" VARCHAR2(160 byte) NOT NULL,
"F_GEOMETRY_COLUMN" VARCHAR2(32 byte) NOT NULL,
"G_TABLE_CATALOG" VARCHAR2(32 byte), "G_TABLE_SCHEMA"
VARCHAR2(32 byte) NOT NULL, "G_TABLE_NAME" VARCHAR2(160 byte)
NOT NULL, "STORAGE_TYPE" NUMBER, "GEOMETRY_TYPE" NUMBER,
"COORD_DIMENSION" NUMBER, "MAX_PPR" NUMBER, "SRID" NUMBER NOT
NULL,
CONSTRAINT "GEOCOL_FK" FOREIGN KEY("SRID")
REFERENCES "SDE"."SPATIAL_REFERENCES"("SRID"),
CONSTRAINT "GEOCOL_PK" PRIMARY KEY("F_TABLE_SCHEMA",
"F_TABLE_NAME", "F_GEOMETRY_COLUMN")
USING INDEX
TABLESPACE "SDE"
STORAGE ( INITIAL 40K NEXT 16K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 1)
PCTFREE 10 INITRANS 4 MAXTRANS 255)
TABLESPACE "SDE" PCTFREE 10 PCTUSED 90 INITRANS 4 MAXTRANS
255
STORAGE ( INITIAL 40K NEXT 16K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 1)
LOGGING
表中的记录是怎么来的?