Trigger of Oracle |
尚未結案
|
tom18mychan
一般會員 ![]() ![]() 發表:3 回覆:13 積分:3 註冊:2005-04-17 發送簡訊給我 |
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.
|
tom18mychan
一般會員 ![]() ![]() 發表:3 回覆:13 積分:3 註冊:2005-04-17 發送簡訊給我 |
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
|
Mickey
版主 ![]() ![]() ![]() ![]() ![]() 發表:77 回覆:1882 積分:1390 註冊:2002-12-11 發送簡訊給我 |
引言: 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, 這樣可能比較容易找到問題. |
tom18mychan
一般會員 ![]() ![]() 發表:3 回覆:13 積分:3 註冊:2005-04-17 發送簡訊給我 |
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
|
yu_blake
一般會員 ![]() ![]() 發表:0 回覆:23 積分:14 註冊:2003-01-16 發送簡訊給我 |
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 illegalMhope helpful |
tom18mychan
一般會員 ![]() ![]() 發表:3 回覆:13 積分:3 註冊:2005-04-17 發送簡訊給我 |
|
pgdennis
資深會員 ![]() ![]() ![]() ![]() ![]() 發表:41 回覆:526 積分:443 註冊:2002-05-23 發送簡訊給我 |
你的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 ;
------
星期一,二...無窮迴圈@@ |
tom18mychan
一般會員 ![]() ![]() 發表:3 回覆:13 積分:3 註冊:2005-04-17 發送簡訊給我 |
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 illegalMhope helpful |
pgdennis
資深會員 ![]() ![]() ![]() ![]() ![]() 發表:41 回覆:526 積分:443 註冊:2002-05-23 發送簡訊給我 |
|
tom18mychan
一般會員 ![]() ![]() 發表:3 回覆:13 積分:3 註冊:2005-04-17 發送簡訊給我 |
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 =>
..
引言: 用select * from user_errors把你的錯誤貼上來吧 我貼給你的code我有測試過,應該不會有問題 so,看看是不是其他地方。 星期一,星期二...星期日..星期一..無窮迴圈@@ |
pgdennis
資深會員 ![]() ![]() ![]() ![]() ![]() 發表:41 回覆:526 積分:443 註冊:2002-05-23 發送簡訊給我 |
|
tom18mychan
一般會員 ![]() ![]() 發表:3 回覆:13 積分:3 註冊:2005-04-17 發送簡訊給我 |
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....
|
pgdennis
資深會員 ![]() ![]() ![]() ![]() ![]() 發表:41 回覆:526 積分:443 註冊:2002-05-23 發送簡訊給我 |
|
tom18mychan
一般會員 ![]() ![]() 發表:3 回覆:13 積分:3 註冊:2005-04-17 發送簡訊給我 |
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....
引言: 最後一次回你了,再不行我投降了 |
yu_blake
一般會員 ![]() ![]() 發表:0 回覆:23 積分:14 註冊:2003-01-16 發送簡訊給我 |
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. |
tom18mychan
一般會員 ![]() ![]() 發表:3 回覆:13 積分:3 註冊:2005-04-17 發送簡訊給我 |
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 |
yu_blake
一般會員 ![]() ![]() 發表:0 回覆:23 積分:14 註冊:2003-01-16 發送簡訊給我 |
hi 您好 1.select Count(0) Into i from sequel Where sequel_name=:new.mname;
-- 找出sequel_name等於:new.mname的資料筆數放入i 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... |
tom18mychan
一般會員 ![]() ![]() 發表:3 回覆:13 積分:3 註冊:2005-04-17 發送簡訊給我 |
多謝你呀...
我改了
but當我入
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的資料筆數放入i 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... |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |