各位大哥,今天小弟寫了一個觸發器,實現兩個表的聯級更新,一個表為student(id,name,address,sal),另一個表為class(id,name,classroom)。兩表的id和name的數據類型一樣,但兩表之間并沒有添加約束。觸發器的源代碼如下:
create or replace trigger modify_together
before update of name
on student
for each row
begin
dbms_output.put_line('old name is :' || :old.name);
dbms_output.put_line('new name is :' || :new.name);
update class set name = :new.name where name = :old.name;
end;
/
當執行完這段代碼之前,系統提示觸發器已成功創建。于是小弟再執行一條更新語句:
update student set name ='vinda' where id =7;
但此時系統卻提示錯誤,錯誤信息如下:
第1行出現錯誤:
ORA-04098:觸發器'SYSTEM.MODIFY_STUDENT'無效且未通過重新驗證
create or replace trigger modify_together
before update of name
on student
for each row
begin
dbms_output.put_line('old name is :' || :old.name);
dbms_output.put_line('new name is :' || :new.name);
update class set name = :new.name where name = :old.name;
end;
/
當執行完這段代碼之前,系統提示觸發器已成功創建。于是小弟再執行一條更新語句:
update student set name ='vinda' where id =7;
但此時系統卻提示錯誤,錯誤信息如下:
第1行出現錯誤:
ORA-04098:觸發器'SYSTEM.MODIFY_STUDENT'無效且未通過重新驗證
2,class里的name不一定就等于old.name
1,after update才有new.name
-- 胡扯
2,class里的name不一定就等于old.name
-- 没有也不会报错,最多是删除0条数据
before update of name
on student
for each row
begin
dbms_output.put_line('old name is :' || :old.name);
dbms_output.put_line('new name is :' || :new.name);
update class set name = :new.name where name = :old.name;
--end;
end modify_together;
是不是end后面需要+ modify_together;
SQL> conn sql/hello
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as sqlSQL>
SQL> create or replace trigger trigger_2 before insert on test_2 for each row
2 DECLARE
3 PRAGMA AUTONOMOUS_TRANSACTION;
4
5 begin
6 insert into test_1 values(:new.id,:new.name);
7 :new.name :=to_char(sysdate,'YYYY-MM-DD HH24:MI:SS');
8 commit;
9 end;
10
11 /Trigger created然后再在这个用户下使用就没问题了