Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Posts
    40

    Unanswered: Before Update Trigger

    My Professor sux that gave me half point on one question. Following is my code , i written on exam.

    Code:
    Create or replace trigger update_price_of_parts
    AFTER update
    ON parts
    FOR EACH ROW
    BEGIN
    INSERT INTO parts_log VALUES(:old.pno,USER,SYSDATE,:old.price,:new.price);
    end;
    Following is what professor believe only way to solve this problem. And said mine won't work.
    Code:
    Create or replace trigger update_price_of_parts
    Before update of price
    ON parts
    FOR EACH ROW
    BEGIN
    INSERT INTO parts_log VALUES(:old.pno,USER,SYSDATE,:old.price,:new.price);
    end;
    Problem is mine working i mean mine has created without any trouble.

    And my question is am i right. I mean i can write before update trigger without "of price" right ?

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    > Problem is mine working i mean mine has created without any trouble.

    And did it "work" (fire only when PRICE changed) properly?

    > I mean i can write before update trigger without "of price" right ?

    Yes, you can, but it will fire every time you UPDATE a row regardless of the PRICE change. Is it expected to be a correct behaviour?
    Code:
    SQL> create table t2( c1 integer, c2 integer );
    
    Table created.
    
    SQL> create or replace trigger t2_tg before update on t2 for each row
      2  begin
      3    dbms_output.put_line( 'updated c1: '||to_char(:old.c1)
      4                                ||' to '||to_char(:new.c1) );
      5  end;
      6  /
    
    Trigger created.
    
    SQL> insert into t2( c1, c2 ) values( 1, 1 );
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> set serveroutput on
    SQL> update t2 set c2 = 5 where c1 = 1;
    updated c1: 1 to 1
    
    1 row updated.
    
    SQL> update t2 set c1 = 1 where c1 = 1;
    updated c1: 1 to 1
    
    1 row updated.
    
    SQL>

  3. #3
    Join Date
    Mar 2008
    Posts
    40
    Thx FlyBoy.

    Now i'm gonna punish my instructor being such a mean to me

    Well he didn't define the when to fire. So I'm Cool !

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    For extra points, tell your instructor that he isn't fully correct. what happens if he set the price to the same value. How about

    Code:
    Create or replace trigger update_price_of_parts
    Before update of price
    ON parts
    FOR EACH ROW
    BEGIN
    if (:old.price is null and :new.price is not null)
       or (:old.price is not null and :new.price is null)
       or (nvl(:old.price,0) <> nvl(:new.price,0)) then
          INSERT INTO parts_log   VALUES(:old.pno,USER,SYSDATE,:old.price,:new.price);
    end if;
    end;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •