我建了一个触发器代码如下create or replace trigger test_trigger
after insert or update or delete on test_old for each row
begin
if inserting then
insert into test_new(id,a1,a2,a3,a4,a5,date_time,tstatus) values(:new.id,:new.a1,:new.a2,:new.a3,:new.a4,:new.a5,SYSDATE(),'插入');
elsif updating then
insert into test_new(id,a1,a2,a3,a4,a5,date_time,tstatus) values(:new.id,:new.a1,:new.a2,:new.a3,:new.a4,:new.a5,SYSDATE(),'更新');
elsif deleting then
insert into test_new(id,a1,a2,a3,a4,a5,date_time,tstatus) values(:old.id,:old.a1,:old.a2,:old.a3,:new.a4,:new.a5,SYSDATE(),'删除');
end if;
end;就是我为一个表建个触发器,当该表发生改变(插入,更新,删除)的时候,我把改变后的记录放到另个表上去,相当于备份用的。上面的a4,a5字段分别是clob,和blob类型,触发器没有错误,但是我想达到的效果,对这两个类型貌似不起作用。该怎么做?具体实现方法怎么实现?,我才接触oracle,不太懂。
after insert or update or delete on test_old for each row
begin
if inserting then
insert into test_new(id,a1,a2,a3,a4,a5,date_time,tstatus) values(:new.id,:new.a1,:new.a2,:new.a3,:new.a4,:new.a5,SYSDATE(),'插入');
elsif updating then
insert into test_new(id,a1,a2,a3,a4,a5,date_time,tstatus) values(:new.id,:new.a1,:new.a2,:new.a3,:new.a4,:new.a5,SYSDATE(),'更新');
elsif deleting then
insert into test_new(id,a1,a2,a3,a4,a5,date_time,tstatus) values(:old.id,:old.a1,:old.a2,:old.a3,:new.a4,:new.a5,SYSDATE(),'删除');
end if;
end;就是我为一个表建个触发器,当该表发生改变(插入,更新,删除)的时候,我把改变后的记录放到另个表上去,相当于备份用的。上面的a4,a5字段分别是clob,和blob类型,触发器没有错误,但是我想达到的效果,对这两个类型貌似不起作用。该怎么做?具体实现方法怎么实现?,我才接触oracle,不太懂。
解决方案 »
- drop 表权限????????
- 如何查出这个值
- TNS:listener does not currently know of service requested in connect descriptor
- pfile
- data guard配置过程中ORA-01041: 内部错误,hostdef 扩展名不存在的问题
- 如何用sqlplus连接 ORACLE10i?
- 目录服务位于何处?
- rowid什么时候会改变?
- 小人,今天装了个Oracle 我要学Oracle 请教大虾,怎么学好Oracle 呢?
- VB连接oracle成功后执行连接查询无结果
- Oracle单表循环
- 连接oracle数据库问题?
DECLARE
stud_hist CLOB;
BEGIN
--Fetch the LOB locator
SELECT student_history INTO stud_hist
FROM student
WHERE student_id = 1003
FOR UPDATE;
--The LOB is empty to start with. Append 10 characters
--onto the end.
DBMS_LOB.WRITEAPPEND (stud_hist, 10, ‘1234567890’);
--Do the same thing ttwice more, giving
--us a total of 30 characters.
DBMS_LOB.WRITEAPPEND (stud_hist, 10, ‘1234567890’);
DBMS_LOB.WRITEAPPEND (stud_hist, 10, ‘1234567890’);
END;
另外篇帖子,明天自己测试下:
oracle已说明了,不能在触发器体来对lob类型操作 pl/sql_block
Specify the PL/SQL block that Oracle executes to fire the trigger. The PL/SQL block of a database trigger can contain one of a series of built-in functions in the SYS schema designed solely to extract system event attributes. These functions can be used only in the PL/SQL block of a database trigger. Restrictions on Trigger Implementation
The PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT) if the block is executed within the same transaction.
You can reference and use LOB columns in the trigger action inside the PL/SQL block. You can modify the :NEW values but not the :OLD values of LOB columns within the trigger action.
当insert不能引用old值,如果引用了old值,其值为空。所以上面的delete时应该是引用旧值
insert into test_new(id,a1,a2,a3,a4,a5,date_time,tstatus) values(:old.id,:old.a1,:old.a2,:old.a3,:old.a4,:old.a5,SYSDATE(),'删除');
windows xp下的oracle 10g r2