Á½¸ö¾­µäµÄOracle´¥·¢Æ÷ʾÀý

¡¾°¸ÀýÒ»¡¿
ÌâÄ¿£º
--´¥·¢Æ÷£º
--Ìí¼ÓÔ±¹¤ÐÅÏ¢,Á÷Ë®ºÅ×÷Ϊ×Ô¶¯±àºÅ(ͨ¹ýÐòÁÐÉú³É),
--²¢ÇÒÅжÏÈç¹û¹¤×ÊСÓÚ0,Ôò¸ÄΪ0;Èç¹û´óÓÚ10000,Ôò¸ÄΪ10000¡£
CREATE TABLE emp2(
e_id NUMBER,
e_no NUMBER,
e_name VARCHAR2(20),
e_sal NUMBER
)
SELECT * from emp2;
CREATE SEQUENCE seq_trg_id;
INSERT INTO emp2(e_id,e_no,e_name,e_sal) VALUES(seq_trg_id.nextval,7788,'ÕÂ×Ó',
 1000000000000)
INSERT INTO emp2(e_id,e_no,e_name,e_sal) VALUES(seq_trg_id.nextval,7788,'ÕÂ×Óâù',-10)
CREATE OR REPLACE TRIGGER trg_add_emp_info
  BEFORE INSERT
  ON emp2
  FOR EACH ROW
  DECLARE
    -- local variables here
  BEGIN
    SELECT seq_trg_id.NEXTVAL INTO :NEW.e_id from dual;
    IF  :NEW.e_sal < 0 THEN
       :NEW.e_sal := 0;
    ELSIF  :NEW.e_sal > 10000 THEN
       :NEW.e_sal := 10000;
    END IF;
  END;
¡¾ °¸Àý¶þ¡¿
ÌâÄ¿£º
--À©³äÁ·Ï°£º
--Ϊemp½¨Á¢´¥·¢Æ÷,½«É¾³ýµÄ¼Ç¼·Åµ½emp3±íÖÐ(autoid,deptno,empno,ename,del_rq-ɾ³ýÈÕÆÚ)
--²âÊÔ´úÂë
CREATE TABLE emp3(
autoid NUMBER PRIMARY KEY,
deptno NUMBER,
empno NUMBER,
ename VARCHAR2(20),
del_rq DATE
)
CREATE SEQUENCE seq_trg_del_autoid;
INSERT INTO emp
  (empno, ename, deptno)
VALUES
  (114, '°¢½¿', 10);
 COMMIT;
 
 SELECT * from emp;
 
 DELETE emp WHERE empno = 114;
 SELECT * from emp3;
 
 ´ð°¸£º
CREATE OR REPLACE TRIGGER trg_del_emp_info
  BEFORE DELETE
  ON emp
  FOR EACH ROW
  DECLARE
    -- local variables here
  BEGIN
    INSERT INTO emp3(autoid,deptno,empno,ename,del_rq)
          VALUES(seq_trg_del_autoid.NEXTVAL,:OLD.deptno,:OLD.empno,:OLD.ename,sysdate);
  END;
Ô­Îijö´¦£ºhttp://blog.csdn.net/justdo2008/archive/2009/04/29/4137779.aspx