创建一个和Suppliers内容相同的表t_suppliers,写一个trigger,实现当suppliers 表增删,修改时t_suppliers进行相同的变化,保障两个表的数据一模一样。帮我看看怎么写
解决方案 »
- 求解:PL/SQL包創建的錯誤
- 将查询出来的某个字段插入到临时表里,查完临时表要drop掉,sql语句怎么写?
- oralce 中如何查看表和表之间的关联 ?
- 还是那个分组后分组求和比较的问题!
- oracle启动问题
- [请教]Failed to commit the transaction: … caused by: ORA-00018: maximum number of
- 执行下面的procedure时提示我“ORA-01031:权限不足”,应该如何改写下面的procedure呢?
- oracle的导入问题(初学者)
- 我在WINDOW2000下装了 ORACLE9i,如何用PROC*C程序连接ORACLE ?
- vs2012出现调试web出现 Oracle的问题
- 请教各位高手触发器的写法
- oracle动态sql
drop table t_suppliersCREATE MATERIALIZED VIEW LOG ON Suppliers;CREATE MATERIALIZED VIEW mv_emp_pk
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/24/60
WITH PRIMARY KEY
AS SELECT * FROM t_suppliers;
CREATE MATERIALIZED VIEW t_suppliers
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/24/60
WITH PRIMARY KEY
AS SELECT * FROM Suppliers;
create table T_Suppliers as select * from Suppliers where 1 <> 1
創建三個觸發器在Suppliers表上
before insert,update, delete
2.创建insert,update,delete触发器
before insert or update or delete on students
for each row
begin
if inserting then
insert into t_students values (:new.cname,:new.course,:new.score);
end if;
if updating then
update t_students set cname=:new.cname,course=:new.course,score=:new.score where cname=:old.cname
and course=:old.course and score=:old.score;
end if;
if deleting then
delete t_students where cname=:old.cname and course=:old.course and score=:old.score;;
end if;
end;
/
before insert or update or delete on Suppliers for each row
as
i number(8) default 0;
begin
select 1 into i from user_tables where Table_name = 't_suppliers';
if i = 0 then
create table t_suppliers as select * from suppliers;
end if ;
if inserting then
insert into t_suppliers values (:new.c1,:new.c2,:new.c3);
end if;
if updating then
update t_suppliers set c1=:new.c1,c2=:new.c2,c3=:new.c3 where c1=:old.c1;
end if;
if deleting then
delete t_suppliers where c1=:old.c1;
end if;
end;
/