怎么在C#中启用和禁用Oracle触发器?
怎样在C#中启用和禁用Oracle触发器???
问题1:一个C#与Oracle数据库的应用程序,如何在C#中启用或禁用该数据库的触发器?
问题2:有两个在不同机器上的Oracle数据库,其中一个要与另一个进行同步,如何在C#启动该同步过程?
------解决方案--------------------
要在代码中实现吗?
你看能不能在事务中先定义好
然后用C#直接调用oracle中的事务
------解决方案--------------------
2.那就用触发起来实现,对某些标志性表进行操作创建触发器,将新数据更新到另一个数据库中,这是个触发器的例子:(不同数据库之间要先建立链接)
CREATE OR REPLACE TRIGGER OnlyPositive
BEFORE INSERT OR UPDATE OF num_col
ON temp_table
FOR EACH ROW
declare
com_num NUMBER;
tran_container t_decl_container%rowtype;
BEGIN
IF :new.num_col < 0 THEN
RAISE_APPLICATION_ERROR(-20100, 'Please insert a positive value');
END IF;
END OnlyPositive;
/
------解决方案--------------------
--禁用脚本
SET SERVEROUTPUT ON SIZE 10000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;
/
--启用脚本
SET SERVEROUTPUT ON SIZE 10000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' ENABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;
/
------解决方案--------------------
问题1:一个C#与Oracle数据库的应用程序,如何在C#中启用或禁用该数据库的触发器?
问题2:有两个在不同机器上的Oracle数据库,其中一个要与另一个进行同步,如何在C#启动该同步过程?
------解决方案--------------------
要在代码中实现吗?
你看能不能在事务中先定义好
然后用C#直接调用oracle中的事务
------解决方案--------------------
2.那就用触发起来实现,对某些标志性表进行操作创建触发器,将新数据更新到另一个数据库中,这是个触发器的例子:(不同数据库之间要先建立链接)
CREATE OR REPLACE TRIGGER OnlyPositive
BEFORE INSERT OR UPDATE OF num_col
ON temp_table
FOR EACH ROW
declare
com_num NUMBER;
tran_container t_decl_container%rowtype;
BEGIN
IF :new.num_col < 0 THEN
RAISE_APPLICATION_ERROR(-20100, 'Please insert a positive value');
END IF;
END OnlyPositive;
/
------解决方案--------------------
--禁用脚本
SET SERVEROUTPUT ON SIZE 10000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' DISABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' DISABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;
/
--启用脚本
SET SERVEROUTPUT ON SIZE 10000
BEGIN
for c in (select 'ALTER TABLE '||TABLE_NAME||' ENABLE CONSTRAINT '||constraint_name||' ' as v_sql from user_constraints where CONSTRAINT_TYPE='R') loop
DBMS_OUTPUT.PUT_LINE(C.V_SQL);
begin
EXECUTE IMMEDIATE c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
for c in (select 'ALTER TABLE '||TNAME||' ENABLE ALL TRIGGERS ' AS v_sql from tab where tabtype='TABLE') loop
dbms_output.put_line(c.v_sql);
begin
execute immediate c.v_sql;
exception when others then
dbms_output.put_line(sqlerrm);
end;
end loop;
end;
/
------解决方案--------------------
- SQL code
可以写个存储过程 create or replace procedure EnableTriggers(b number) is cursor c is select TABLE_NAME from user_all_tables; tablename varchar2(100); sqlstr varchar2(1000); begin open c; loop fetch c into tablename; exit when c%notfound; if b=0 then --0禁用 1启用 sqlstr:='alter table '||tablename||' disable all triggers'; else sqlstr:='alter table '||tablename||' enable all triggers'; end if; execute immediate sqlstr; dbms_output.put_line(sqlstr); end loop; close c; end;