当前位置: 代码迷 >> Oracle管理 >> 觸發器問題。解决办法
  详细解决方案

觸發器問題。解决办法

热度:94   发布时间:2016-04-24 05:53:11.0
觸發器問題。
假如新建一个成绩表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>
  相关解决方案