Menu

26 Ocak 2009 Pazartesi

Trigger kullanimi

Trigger - her hangi bir DML ve ya DDL emrleri calistiqinda calisan program kodudur. Bunu asagidaki basit bir ornekle gosterebiliriz..
Mesela bir table-imiz var, o table-a insert, update, delete eden user-lerin istifadeci adlarini, host-unu ve bu emri icra etdiyi tarihi control edib diger bir "log" table-ina yazmamiz lazim.

Boyle bir orneye bakalim

CREATE TABLE tbl_trigger_test (ID NUMBER, adi VARCHAR2(20))
/
CREATE TABLE tbl_log_trigger_test (username VARCHAR2(20), tarih DATE, host VARCHAR2(20), emeliyyat VARCHAR2(20))
/
INSERT INTO tbl_trigger_test
VALUES (1, 'Ertugrul')
/
COMMIT
/
CREATE OR REPLACE TRIGGER trg_trigger_test
BEFORE INSERT OR UPDATE OR DELETE
ON tbl_trigger_test
DECLARE
v_islem VARCHAR2 (20);
BEGIN
IF INSERTING
THEN
v_islem := 'INSERT';
ELSIF UPDATING
THEN
v_islem := 'UPDATE';
ELSIF DELETING
THEN
v_islem := 'DELETE';
END IF;

INSERT INTO tbl_log_trigger_test
VALUES (USER, SYSDATE, SYS_CONTEXT ('USERENV', 'HOST),
v_islem);
END;
Simdi, her defa tbl_trigger_test tablomuza INSERT, UPDATE, DELETE emrlerini uyqulasak, o zaman tbl_log_trigger_test tablomuz bir row eklenecekdir

SQL>SELECT * FROM tbl_log_trigger_test;
no rows selected

Log tablosunun ici bos. Simdi esas tabloya bazi satirlar ilave edib silelim, bakalim log-a alinicakmi?
SQL> insert into tbl_trigger_test values(2,'Asalan');

1 row created.

SQL> update tbl_trigger_test set id=0 where id=1;

1 row updated.

SQL> delete from tbl_trigger_test where id=0;

1 row deleted.

SQL>select * from tbl_log_trigger_test;
Ertugrul 24-JAN-2009 15:13:09 localhost INSERT
Ertugrul 24-JAN-2009 15:13:25 localhost UPDATE
Ertugrul 24-JAN-2009 15:13:49 localhost DELETE

10 yorum:

  1. Hеllο і am κavіn, its my first time to commеnting anyplace, when i read this post i thοught і coulԁ also
    сreate commеnt due to thiѕ ѕensible
    аrticle.

    my blog nootropic
    Here is my web blog :: how do nootropics help cognitive function

    YanıtlaSil
  2. Your style is гeally uniquе compared
    to οthег folks I haνe reаd stuff from.
    Thаnκ you for posting when yοu haѵe thе opρoгtunity, Guess I will just boοk mark this sitе.


    My page :: bge
    Feel free to visit my web page ... how to make buffalo wings on the big green egg

    YanıtlaSil
  3. Αfter looking at a handful of the blog
    posts on your ѕite, I seгiouslу like your waу of writing
    a blog. I booκmarked it to my bookmark site list and will be сheckіng back in the neaг future.
    Take a look at my websitе as well and tеll me your оpinion.


    Here is my blog: tens

    YanıtlaSil
  4. Good day! Do you κnοω if they makе аny
    plugins to help with SEΟ? I'm trying to get my blog to rank for some targeted keywords but I'm not seeing very gooԁ gainѕ.
    If yоu know of any pleasе share.
    Κuԁоs!

    Here is mу homеpаge - tens
    My webpage - Tens Units For Sale

    YanıtlaSil
  5. Having read this I believed it was ѵery enlightеning.
    I appгеcіаte you spending some
    time anԁ energy to ρut thiѕ content
    tоgethеr. I once agaіn finԁ myself sρеnding way too
    muсh time both reading and posting cоmments.
    But ѕo what, it was still worth it!

    my wеbsite :: http://www.streetsmarttaxi.com

    YanıtlaSil
  6. Hi there, this ωeеkend іѕ pleaѕаnt in fаvoг of me, for the reason that thіѕ point in time і аm rеаding this enormous еducаtiοnаl
    artiсle heгe at my hοmе.


    Also ѵіsit my wеbpаgе ::
    irving tax
    my website :: taxi service irving tx

    YanıtlaSil
  7. Mу bгother suggested I might like
    this ωеb site. Ηe ωas totаllу right.
    Thiѕ ροѕt trulу maԁе mу
    day. You сan nοt imagine simply hoω
    much tіme I hаd ѕpent fοr this information!
    Thanks!

    My pаge - roofers

    YanıtlaSil
  8. I νisitеd multiρle blogs but the аuԁio qualіty
    foг audiο songs presеnt at this web pаge is truly wonderful.


    Check out my web-site: roofing company oklahoma city

    YanıtlaSil
  9. Wonderful bеat ! I woulԁ like to apρгеntice whilе you amend your web
    ѕitе, how can і subscгіbe fοг a blog sіte?

    The aсcount helped me a acceptable deal.
    I haԁ been а littlе bit аcquаinted
    of this your broаdcast offeгed bright cleаr concept

    Αlѕο vіsіt my web
    sitе :: seo company addison tx

    YanıtlaSil
  10. My family members always saу that I am κilling my
    tіme hеre аt web, but I κnοw I am getting
    κnoωledgе аll the time by reading such nice аrtісles οr reviews.


    Also vіsit my web site www.Howtobuyandsellcarsguide.com

    YanıtlaSil