日期:2014-05-17  浏览次数:21147 次

觸發器問題。
假如新建一个成绩表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 ROW
BEGIN
  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 ON
SQL> SET TIMING ON
SQL> SELECT * FROM T_CHENGJI;

       ID1     SCORE1     SCORE2 SCORE1LINE SCORE2LINE     ISGOOD
---------- ---------- ---------- ---------- ---------- ----------
         1         80         90         95         95          0

Executed in 0.016 seconds

SQL> UPDATE T_CHENGJI
  2     SET SCORE1 = 98,
  3         SCORE2 = 96
  4   WHERE ID1 = 1;

1 row updated

Executed in 0.109 seconds

SQL> COMMIT;

Commit complete

Executed in 0 seconds

SQL> SELECT * FROM T_CHENGJI;

       ID1     SCORE1     SCORE2 SCORE1LINE SCORE2LINE     ISGOOD
---------- ---------- ---------- ---------- ---------- ----------
         1         98         96         95         95          1

Executed in 0 seconds

SQL>