假如新建一个成绩表CREATE TABLE "T_CHENGJI"
( "ID1" NUMBER NOT NULL ENABLE,
"SCORE1" NUMBER,
"SCORE2" NUMBER,
"SCORE1LINE" NUMBER,
"SCORE2LINE" NUMBER,
"ISGOOD" NUMBER NOT NULL ENABLE
)
要当更新score1line,score2line时要能自动触发isgood，当score1>=score1line与score2>=score2line时isgood 设为1，否则设为0.
如果ISGOOD字段可以為空的話，我能夠實現，但是現在這個字段不能為空，請問這個觸發器能實現嗎？
謝謝！
------解决方案--------------------
- SQL code
SQL> CREATE TABLE T_CHENGJI(
2 ID1 NUMBER NOT NULL ENABLE,
3 SCORE1 NUMBER,
4 SCORE2 NUMBER,
5 SCORE1LINE NUMBER,
6 SCORE2LINE NUMBER,
7 ISGOOD NUMBER NOT NULL ENABLE
8 );
Table created
SQL> CREATE OR REPLACE TRIGGER tri_T_CHENGJI
2 BEFORE INSERT OR UPDATE ON T_CHENGJI
3 FOR EACH ROW
4 BEGIN
5 IF ((:NEW.SCORE1>:NEW.score1line) AND (:NEW.SCORE2>:NEW.score2line)) THEN
6 :NEW.ISGOOD := 1;
7 ELSE
8 :NEW.ISGOOD := 0;
9 END IF;
10 END;
11 /
Trigger created
SQL> INSERT INTO T_CHENGJI(ID1,SCORE1,SCORE2,SCORE1LINE,SCORE2LINE) VALUES(1,10,10,5,5);
1 row inserted
SQL> select * from T_CHENGJI;
ID1 SCORE1 SCORE2 SCORE1LINE SCORE2LINE ISGOOD
---------- ---------- ---------- ---------- ---------- ----------
1 10 10 5 5 1
SQL> update T_CHENGJI set score1=2;
1 row updated
SQL> select * from T_CHENGJI;
ID1 SCORE1 SCORE2 SCORE1LINE SCORE2LINE ISGOOD
---------- ---------- ---------- ---------- ---------- ----------
1 2 10 5 5 0
SQL>
------解决方案--------------------
TRY IT..
- SQL code
-- TRIGGER CODE:CREATE OR REPLACE TRIGGER TRG_UPDATE_ISGOOD BEFORE UPDATE ON T_CHENGJI FOR EACH ROWBEGIN IF :NEW.SCORE1 >= :NEW.SCORE1LINE AND :NEW.SCORE2 >= :NEW.SCORE2LINE THEN :NEW.ISGOOD := 1; ELSE :NEW.ISGOOD := 0; END IF;END TRG_UPDATE_ISGOOD;-- TEST RESULT:SQL> SET SERVEROUTPUT ONSQL> SET TIMING ONSQL> SELECT * FROM T_CHENGJI; ID1 SCORE1 SCORE2 SCORE1LINE SCORE2LINE ISGOOD---------- ---------- ---------- ---------- ---------- ---------- 1 80 90 95 95 0Executed in 0.016 secondsSQL> UPDATE T_CHENGJI 2 SET SCORE1 = 98, 3 SCORE2 = 96 4 WHERE ID1 = 1;1 row updatedExecuted in 0.109 secondsSQL> COMMIT;Commit completeExecuted in 0 secondsSQL> SELECT * FROM T_CHENGJI; ID1 SCORE1 SCORE2 SCORE1LINE SCORE2LINE ISGOOD---------- ---------- ---------- ---------- ---------- ---------- 1 98 96 95 95 1Executed in 0 secondsSQL>