ORACLE´¥·¢Æ÷
´¥·¢Æ÷
q ´¥·¢Æ÷Êǵ±Ìض¨Ê¼þ³öÏÖʱ×Ô¶¯Ö´ÐеĴ洢¹ý³Ì
q ÌØ¶¨Ê¼þ¿ÉÒÔÊÇÖ´ÐиüеÄDMLÓï¾äºÍDDLÓï¾ä
q ´¥·¢Æ÷²»Äܱ»ÏÔʽµ÷ÓÃ
q ´¥·¢Æ÷µÄ¹¦ÄÜ£º
q ×Ô¶¯Éú³ÉÊý¾Ý
q ×Ô¶¨Ò帴ÔӵݲȫȨÏÞ
q ÌṩÉó¼ÆºÍÈÕÖ¾¼Ç¼
q ÆôÓø´ÔÓµÄÒµÎñÂß¼
´´½¨´¥·¢Æ÷µÄÓï·¨£º
CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]]
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
pl/sql_block;
´¥·¢Æ÷ÓÉÈý²¿·Ö×é³É£º
q ´¥·¢Æ÷Óï¾ä£¨Ê¼þ£©
q ¶¨Ò弤»î´¥·¢Æ÷µÄ DML ʼþºÍ DDL ʼþ
q ´¥·¢Æ÷ÏÞÖÆ
q Ö´Ðд¥·¢Æ÷µÄÌõ¼þ£¬¸ÃÌõ¼þ±ØÐëÎªÕæ²ÅÄܼ¤»î´¥·¢Æ÷
q ´¥·¢Æ÷²Ù×÷£¨Ö÷Ì壩
q °üº¬Ò»Ð© SQL Óï¾äºÍ´úÂ룬ËüÃÇÔÚ·¢³öÁË´¥·¢Æ÷Óï¾äÇÒ´¥·¢ÏÞÖÆµÄÖµÎªÕæÊ±ÔËÐÐ
´´½¨´¥·¢Æ÷
CREATE OR REPLACE TRIGGER first_emp_trg
AFTER INSERT
ON emp
FOR EACH ROW
BEGIN
IF (:NEW.sal < 10000) THEN
DBMS_OUTPUT.PUT_LINE('less 10000');
ELSE
DBMS_OUTPUT.PUT_LINE('ge 10000');
END IF;
END;
SQL> insert into emp(empno,ename,sal) values(8102,'HuangPei',10000);
ge 10000
1 row inserted
SQL> insert into emp(empno,ename,sal) values(8103,'HuangPei',100);
less 10000
1 row inserted
²éѯ´¥·¢Æ÷
SQL> select trigger_name,trigger_type,triggering_event,status from user_triggers;
TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT STATUS &n
Ïà¹ØÎĵµ£º
Ò²Ðí¶ÔSQL ServerÀ´Ëµ,ȡǰNλµÄÊý¾ÝÊǺܼòµ¥µÄÊÂÇé,Ò»ÌõTOPÓï¾ä¾Í¸ã¶¨ÁË.
ÔÚOracleÖÐÎÒÃÇÍùÍù»á¸Ð¾õµ½Í·ÌÛ,ƽÈÕÀïÒ²³£»áÓõ½,Ö÷ÒªÊÇʹÓÃpartition by.
Óï·¨ÈçÏÂ:
select emp_card_no,work_date,read_card_date,count(rownum) as cnt
from hra_read_car ......
1.´´½¨Ñ§Éú±í£º
CREATE TABLE student (
xh number primary key,
Name varchar2(10) not null);
2. ´´½¨ÐòÁУº
CREATE SEQUENCE student_seq
START WITH 1
INCREMENT BY 1
NOCACHE;
°´»Ø³ ......
Create directoryÈÃÎÒÃÇ¿ÉÒÔÔÚOracleÊý¾Ý¿âÖÐÁé»îµÄ¶ÔÎļþ½øÐжÁд²Ù×÷£¬¼«´óµÄÌá¸ßÁËOracleµÄÒ×ÓÃÐԺͿÉÀ©Õ¹ÐÔ¡£
ÆäÓ﷨Ϊ:
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
±¾°¸Àý¾ßÌå´´½¨ÈçÏÂ:
create or replace directory exp_dir as '/tmp';
Ŀ¼´´½¨ÒԺ󣬾ͿÉÒ԰ѶÁдȨÏÞÊÚÓèÌØ¶¨Óû§ ......
µ¼³öºÍµ¼ÈëʵÓóÌÐò
q µ¼³öºÍµ¼ÈëʵÓóÌÐòÓÃÓÚʵʩÊý¾Ý¿âµÄÂß¼±¸·ÝºÍ»Ö¸´
q µ¼³öʵÓóÌÐò½«Êý¾Ý¿âÖеĶÔÏó¶¨ÒåºÍÊý¾Ý±¸·Ýµ½Ò»¸ö²Ù×÷ϵͳ¶þ½øÖÆÎļþÖÐ
q µ¼ÈëʵÓóÌÐò¶ÁÈ¡¶þ½øÖƵ¼³öÎļþ²¢½«¶ÔÏóºÍÊý¾ÝÔØÈëÊý¾Ý¿âÖÐ ......