DB/Oracle/PLSQL/ȥꥬ

Top / DB / Oracle / PLSQL / ȥꥬ

ܼ

PL/SQLʥȥꥬΤá


ȥꥬȤ

ǡ١ȥꥬΤϤΥȥɥץ㡣

ɽӥ塼ιʤɤȯȤˡưŪ˼¹ԤΡɽбդƥǡ١˳ǼƤޤϥƥ।٥ȡDB εư߻ʤɡˤ桼٥ȡʥ󡢥դʤɡˤκݤˤⵯưǤ롣

ȤС߸ɽΤ뾦ʤλĿ 100 Ĥ 50 ĤˤʤäȤưǥȥꥬưƿͤåĿͰʲǤȯɽ˥ǡɲä롢ʤɤνǽ

ץʤäƤǤǤ뤱ɡOracle ΤȤȤεǽؤǤʤ餽ᤤȤХåȤOracle εǽбǤˡưŪʰŪˤ˲餫νԤ碌ȤѤȤ褤äݤ

Ūʹʸ

ɤϥȥɥץʤΤǹʸϻƤ롣ץ󤬤뤱ɡ

  • ȥꥬ
    create or replace
    -- ȥꥬ롣ѥ᡼Ϥʤ
    trigger trg_hoge
      -- ߥ󥰡ʸҡˤȥ٥ȡʸҡˤɽФƥå
      <timing> <event> on <tablename>
      -- סʸҡˤλ
      <type>
    -- β̵֥̾å򤯤äĤ
    declare
        ...
    begin
        ...
    exception
        ...
    end;
    /
    

ߥ󥰤λ

ΥȥꥬɤΥߥ󥰤ǵưΤꤹ롣

  • ߥ
    ߥ
    beforeɽФμ¹˥ȥꥬư
    afterɽФμ¹Ը˥ȥꥬư

٥

ɽФɤΤ褦ǵưΤꤹ롣ʲΤ줾ɬפʤ OR 󤹤롣

  • ٥Ȥλ
    ٥
    insertINSERT ˵ư
    updateUPDATE ˵ư
    update of <columnname>ؤ UPDATE ˵ư
    deleteDELETE ˵ư
  • ٥Ȼ
    create or replace trigger trg_hoge
      -- HOGE ɽФ INSERT  UPDATE  DELETE ǵư
      before insert or update or delete on hoge
      -- HOGE ɽФ INSERT  FUGA ؤ UPDATE ǵư
      before insert or update of fuga on hoge
    

ȥꥬΥסɤϰϤưΤꤹ륤᡼

  • ˡ
    ʸȥꥬꤷʤƶԤοȤϴطʤҤȤĤ SQL ʸμ¹Ի̵˵ưȥꥬ¹˵ư뤫¹Ը˵ư뤫ҤΥߥ󥰤ǻꤹ롣
    ԥȥꥬfor each rowԤФư󤺤ĵưȥꥬȤа UPDATE 5 Ԥ 5 ư롣

¹ʸε

:new :old

ȹιԤΥǡǼ줿ХѿͤȤȤǡԤιͤǤ롣

  • ͥ١ƺˤȤ
    create or replace trigger trg_hoge
      befor update on tbl_hoge for each row
    declare
        v_diff number;
    begin
        v_diff := :new.foo - :old.foo;
        dbms_output.put_line('old value : ' || :old.foo);
        dbms_output.put_line('new value : ' || :old.foo);
        dbms_output.put_line('diff      : ' || :old.foo);
    end;
    /
    

Last-modified: 2011-11-09 () 15:04:38 (2360d)