In oracle, assume we have the following requirements 1.For each employee, store her/his HK ID, and name. 2.Each employee is an actor or a director. For an actor, store her/his nationality, while for a director, store her/his level (of qualification). 3.For each movie, store its name, and production year. No two movies can have the same name. 4.For each movie, record its cast (i.e., all the actors involved) and the director (a movie has a single director). For each actor/director, record the amount of money s/he earned from the movie. 5.Every director must have directed some movie, but not every actor has acted in a movie. Each director/actor may participate in multiple movies. 6.If a movie is a sequel to another one (e.g., “Matrix Re-loaded” is a sequel to “Matrix”), this relationship must be recorded. Not every movie has a sequel, and a movie is a sequel to at most one other movie. We obtained the following table Employee (eid, name) Actor (aid, nationality) Director (did, level) Movie (mname, year, did, d_pay) Starring (aid, mname, a_pay) Sequel (original_name, sequel_name) How to create a triggers to make sure 1.a movie is a sequel to at most one other movie. 2.Updates on “d_pay” in Movie are not allowed if the “mname” of the tuples affected already appear in Sequel. 3.Let the “expenditure” of a movie be the total amount of money paid to its director and actors. Then, the expenditure of any sequel should not exceed that of the original movie.

I wrote create trigger sequelerror before insert on sequel for each row Begin if (:new.original_name in (select sequel_name from sequel)) Then raise_application_error (-20000, 'Each movie can only have 1 sequel') end if end sequelerror create trigger illegalM before update on movie for each row Begin if (:new.mname in (select original_name from sequel)) Then raise_application_error (-20000, 'Illegal update on director pay') else if (:new.mname in (select sequel_name from sequel)) Then raise_application_error (-20000, 'Illegal update on director pay') end if end illegalM . run; in Oracle but it doesn't work......please help me

引言: I wrote create trigger sequelerror before insert on sequel for each row Begin if (:new.original_name in (select sequel_name from sequel)) Then raise_application_error (-20000, 'Each movie can only have 1 sequel') end if end sequelerror ...

Oracle 的 Row Trigger 中 ... Table 本身是 "Muting" 的. 是否 Error Code or Message, 這樣可能比較容易找到問題.

but it said Warning: Trigger created with compilation errors. and when I type insert into sequel values('troy', 'troy returns'); Error comes out as follow insert into sequel values('troy', 'troy returns') * ERROR at line 1: ORA-04098: trigger 'C50481550.SEQUELERROR' is invalid and failed re-validation

hi.     When u wrote a row-type trigger, the table could be mutating, That mean u can't write any code to read or write the table in the Trigger. so to solve this problem.

create or replace trigger sequelerror before insert on sequel
for each row
declare
  -- add this code
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  if (:new.original_name in (select sequel_name from sequel)) Then
    raise_application_error (-20000, 'Each movie can only have 1 sequel')
  end if
end sequelerror
 
create or replace trigger illegalM before update on movie
for each row
declare
  -- add this code
  PRAGMA AUTONOMOUS_TRANSACTION;
Begin
  if (:new.mname in (select original_name from sequel)) Then
    raise_application_error (-20000, 'Illegal update on director pay')
  else if (:new.mname in (select sequel_name from sequel)) Then
    raise_application_error (-20000, 'Illegal update on director pay')
  end if
end illegalM

hope helpful

it still cannot work....when I type insert into sequel values('troy', 'troy returns'); it still doesn't allow me to insert it , instead it should be OK..... Please help me~~

你的trigger本身就有語法錯誤了，我幫你改了你跑看看

create Or Replace trigger sequelerror before insert on sequel
for each Row
Declare
 i Integer;
Begin
  select Count(0) Into i from sequel Where sequel_name=:new.original_name;/*be sure two columns's length are the same.*/
  If i>0 Then
     raise_application_error (-20000, 'Each movie can only have 1 sequel');
  end if
end ;

星期一,星期二...星期日..星期一..無窮迴圈@@

------
星期一,二...無窮迴圈＠＠

Actually, when I type your version in Oracle it first said 'Warning: Trigger created with compilation errors.' and when I type insert into sequel values('troy', 'troy returns'); it said Error comes out as follow insert into sequel values('troy', 'troy returns') * ERROR at line 1: ORA-04098: trigger 'C50481550.SEQUELERROR' is invalid and failed re-validation How can I solve this to meet the first two requirement ??

引言: hi. When u wrote a row-type trigger, the table could be mutating, That mean u can't write any code to read or write the table in the Trigger. so to solve this problem.

create or replace trigger sequelerror before insert on sequel
for each row
declare
  -- add this code
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  if (:new.original_name in (select sequel_name from sequel)) Then
    raise_application_error (-20000, 'Each movie can only have 1 sequel')
  end if
end sequelerror
 
create or replace trigger illegalM before update on movie
for each row
declare
  -- add this code
  PRAGMA AUTONOMOUS_TRANSACTION;
Begin
  if (:new.mname in (select original_name from sequel)) Then
    raise_application_error (-20000, 'Illegal update on director pay')
  else if (:new.mname in (select sequel_name from sequel)) Then
    raise_application_error (-20000, 'Illegal update on director pay')
  end if
end illegalM

hope helpful >< face="Verdana, Arial, Helvetica">

用select * from user_errors把你的錯誤貼上來吧 我貼給你的code我有測試過，應該不會有問題 so,看看是不是其他地方。 星期一,星期二...星期日..星期一..無窮迴圈@@

------
星期一,二...無窮迴圈＠＠

Thanks you very much, but I think requirement is already satisfy if when I create the table, I specify the primary key  is (original_name) in sequel, then I can know a movie is a sequel to at most one other movie.right.... so now, problem is changed to how to satisfy requirement 2,3,4 although 2 and 3 are very similar say for requirement 2, I type create or replace trigger sequelerror before insert on sequel for each row Begin if (:new.original_name in (select sequel_name from sequel)) Then raise_application_error (-20000, ''Each movie can only have 1 sequel'') end if end sequelerror . run; It said [red] Warning: Trigger created with compilation errors. SQL> select * from user_errors; NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- ILLEGALM TRIGGER 1 2 1 PLS-00103: Encountered the symbol "IF" when expecting one of the following: := . ( @ % ; The symbol ";" was substituted for "IF" to continue. ILLEGALM TRIGGER 2 4 1 PLS-00103: Encountered the symbol "ELSE" when expecting one of the following: NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- := . ( % ; The symbol ";" was substituted for "ELSE" to continue. ILLEGALM TRIGGER 3 6 1 PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; SEQUELERROR TRIGGER 1 3 36 NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- PLS-00103: Encountered the symbol "EACH" when expecting one of the following: . ( ) , * @ % & | = - < / > at in is mod not range rem => .. <> or != or ~= >= <= <> and or like between || Please help me....

引言: 用select * from user_errors把你的錯誤貼上來吧 我貼給你的code我有測試過，應該不會有問題 so,看看是不是其他地方。 星期一,星期二...星期日..星期一..無窮迴圈@@

我之前說過你的code語法有錯誤，你改用我貼的code，然後再把錯誤貼上來? ok? 星期一,星期二...星期日..星期一..無窮迴圈@@

------
星期一,二...無窮迴圈＠＠

I think requirement 1 can easily be solved by setting the primary key of sequel is original_name, right? For requirement 2 I type create or replace trigger illegalM before update of d_pay on movie referencing new as nrow for each row Begin atomic if (nrow.mname in (select original_name from sequel)) Then raise_application_error (-20000, 'Illegal update on director pay') else if (nrow.mname in (select sequel_name from sequel)) Then raise_application_error (-20000, 'Illegal update on director pay') end if end . run; It said Warning: Trigger created with compilation errors. SQL> select * from user_errors; it said NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- ILLEGALM TRIGGER 1 2 1 PLS-00103: Encountered the symbol "IF" when expecting one of the following: := . ( @ % ; The symbol ";" was substituted for "IF" to continue. ILLEGALM TRIGGER 2 4 1 PLS-00103: Encountered the symbol "ELSE" when expecting one of the following: NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- := . ( % ; The symbol ";" was substituted for "ELSE" to continue. ILLEGALM TRIGGER 3 6 1 PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; SEQUELERROR TRIGGER 1 3 36 NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- PLS-00103: Encountered the symbol "EACH" when expecting one of the following: . ( ) , * @ % & | = - < / > at in is mod not range rem => .. <> or != or ~= >= <= <> and or like between || Please help me....

最後一次回你了，再不行我投降了... 你自己要比對我給的> >

------
星期一,二...無窮迴圈＠＠

sorry, can you please help me again... I had made change to my trigger...but it still has one strange compliation error. My trigger is  create or replace trigger illegalM before update of d_pay on movie for each row Declare i Integer; j Integer; Begin select Count(0) Into i from sequel Where sequel_name=:new.mname; select Count(0) Into j from sequel Where original_name=:new.mname; if (i>0 Or j>0) Then raise_application_error (-20000, 'Illegal update on director pay'); end if end ; . run; The error message is NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- ILLEGALM TRIGGER 1 10 1 PLS-00103: Encountered the symbol "END" when expecting one of the following: ; The symbol ";" was substituted for "END" to continue. I don't know why because I had already put ';' after end Please help me....

引言: 最後一次回你了，再不行我投降了... 你自己要比對我給的> > >< face="Verdana, Arial, Helvetica">

hi 您好     看了一下您的程式碼 應該是漏寫了一個分號而已    

create or replace trigger illegalM before update of d_pay on movie
for each row
Declare
  i Integer;
  j Integer;
Begin
  select Count(0) Into i from sequel Where sequel_name=:new.mname;
  select Count(0) Into j from sequel Where original_name=:new.mname;
  if (i>0 Or j>0) Then
     raise_application_error (-20000, 'Illegal update on director pay');
  end if;  -- 這裡漏寫了分號 
end ;
.
run;    

p.s 程式碼請用[ code][ /code]括住 比較容易閱讀喔 還是建議用 PL/SQL 編輯器寫程式比較方便 容易偵錯 Hope Helpful. --

Thanks you very much....its ok now... but I have 2 more question. 1. Can you tell me what is mean by     select count (0) from .......?? 2. For the requirement 3, I wrote

    create or replace trigger overexpenditure before insert on sequel
for each row
Declare
        tempd_pay1 Integer;
        tempa_pay1 Integer;
        tempd_pay2 Integer;
        tempa_pay2 Integer;
        cost1 Integer;
        cost2 Integer;
Begin
        select d_pay into tempd_pay1 from movie where mname = :new.original_name;
        select sum(a_pay) into tempa_pay1 from starring group by mname having mname = :new.original_name;
        select d_pay into tempd_pay2 from movie where mname = :new.sequel_name;
        select sum(a_pay) into tempa_pay2 from starring group by mname having mname = :new.sequel_name;
        cost1=tempd_pay1   tempa_pay1;
        cost2=tempd_pay2   tempa_pay2;
        if (cost2>cost1) Then
                raise_application_error (-20000, 'Illegal insert becasue expenditure of sequel is exceed its original');
        end if;
end;
.
run;

But..still it has compliation error. the error is the following NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- OVEREXPENDITURE TRIGGER 1 13 7 PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; OVEREXPENDITURE TRIGGER 2 14 2 PLS-00103: Encountered the symbol "COST2" OVEREXPENDITURE TRIGGER 3 14 31 NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( * @ % & - / at mod rem return returning and or || Can you help me becasue I do not have PL/SQL

hi 您好    1.select Count(0) Into i from sequel Where sequel_name=:new.mname;    -- 找出sequel_name等於:new.mname的資料筆數放入ｉ 2.

 
  cost1=tempd_pay1   tempa_pay1;
  cost2=tempd_pay2   tempa_pay2;
  
  cost1:=tempd_pay1   tempa_pay1;
  cost2:=tempd_pay2   tempa_pay2;
  
  

hope helpful. -- work harder...

多謝你呀．．． 我改了 ｂｕｔ當我入 insert into sequel values('troy', 'troy returns'); 佢話 insert into sequel values('troy', 'troy returns') * ERROR at line 1: ORA-01403: no data found ORA-06512: at "C50481550.OVEREXPENDITURE", line 10 ORA-04088: error during execution of trigger 'C50481550.OVEREXPENDITURE' i think 因為個database本身冇data in starring... so how to ensure that the trigger start when there is data in the table.... 我的想法是如果table冇野 set a_pay1=0 or d_pay1=0..... please help me..

引言: hi 您好 1.select Count(0) Into i from sequel Where sequel_name=:new.mname; -- 找出sequel_name等於:new.mname的資料筆數放入ｉ 2.

 
  cost1=tempd_pay1   tempa_pay1;
  cost2=tempd_pay2   tempa_pay2;
  
  cost1:=tempd_pay1   tempa_pay1;
  cost2:=tempd_pay2   tempa_pay2;
  
  

hope helpful. -- work harder...