#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    4
    Rep Power
    0

    Trigger insert, update, and delete


    Hi,

    Can someone please help me. I am a newbie in Pl/sql.

    I like to write a trigger to insert, update, and delete a row.
    When row is inserted/updated/deleted in table1, the same action is also applied for table2.

    Thanks
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    11
    According to Oracle dicumentation "A trigger associates with A table".
    Say with me again "A trigger associates with A table"

    Regards,
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    4
    Rep Power
    0

    Trigger to insert/update/delete


    shafique,

    Yes, a trigger associates with table.

    Here is the suituation:

    If I insert data into table1, same data will be inserted into table2.
    If I update data in table1, row with same primary key in table2 is also updated. Same for deleting.

    Thanks
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    Why a view is not enough?
  8. #5
  9. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    11
    Here is an example of world popular table 'EMP', I make another table 'EMP_TEMP' identical to EMP and now i want to update, insert and delete record from EMP_TEMP whenever an update, insert, and delete occured in emp. For this purpse i am creating a trigger called 'Emp_Ins_Upd_Del_Trig', complete list as follows.
    CREATE or REPLACE TRIGGER Emp_Ins_Upd_Del_Trig
    BEFORE delete or insert or update on EMP
    FOR EACH ROW
    BEGIN
    if UPDATING then
    UPDATE emp_temp
    SET ename = :new.ename
    , job = :new.job
    , mgr = :new.mgr
    , hiredate = :new.hiredate
    , sal = :new.sal
    , comm = :new.comm
    , deptno = :new.deptno;
    end if;

    if INSERTING then
    INSERT INTO emp_temp
    VALUES( :new.empno
    , :new.ename
    , :new.job
    , :new.mgr
    , :new.hiredate
    , :new.sal
    , :new.comm
    , :new.deptno);
    end if;
    if DELETING then
    DELETE FROM emp_temp
    WHERE empno = ld.empno;
    end if;
    END;
    /

    You first must test it by inserting, deleting and updating rows on emp table, Once you satisfied then apply it in your program.

    Another option that you have is CREATE MATERIALIZED VIEW for any base table. It is a different from normal view, in normal view oracle only saves the query of that view in dictionary so that whenever you run any select statement on the normal view oracle reads the query from data dictionary and extract the data from tables, views those are using in creation of view's query, But it is rverse in the Materialized view, oracle stores the data instead of storing query.
    Oracle implicitly keeps Materialized view identical to the base table.

    Regards,
  10. #6
  11. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    Personally I don't think that a materialized view has advantages over a traditional view unless there are aggregations or complex calculations or many joins in the object's definition, so my advice is to use a traditional view if you just need a subset of a table or to join two/three tables.
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    4
    Rep Power
    0
    Thank you all.

    Especially, thank to shafique. I got it now. It works as what I expected.

IMN logo majestic logo threadwatch logo seochat tools logo