Results 1 to 6 of 6

Thread: Oracle Trigger

  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: Oracle Trigger

    Hi,
    I am facing very unusual problem. I have update trigger defined on supplier_master.

    CREATE OR REPLACE TRIGGER DATAP.TU_SUPPLIER_MASTER
    BEFORE
    UPDATE ON DATAP.SUPPLIER_MASTER FOR EACH ROW begin
    :new.upd_date := sysdate;
    end;

    Problem is this trigger is not updating column upd_date with sysdate when supplier_master is updated.
    Following are the statistics for the trigger

    1* SELECT TABLE_NAME,TRIGGER_NAME,STATUS FROM DBA_TRIGGERS WHERE TABLE_NAME='SUPPLIER_MASTER'
    test@prod>/

    TABLE_NAME TRIGGER_NAME STATUS
    ------------------------------ ------------------------------ --------
    SUPPLIER_MASTER TU_SUPPLIER_MASTER ENABLED

    test@prod>SELECT OBJECT_NAME,STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME='SUPPLIER_MASTER' AND OBJECT_
    TYPE='TRIGGER';

    no rows selected

    test@prod>SELECT OBJECT_NAME,STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME='TU_SUPPLIER_MASTER';

    OBJECT_NAME STATUS
    ----------- -------
    TU_SUPPLIER_MASTER VALID

    I have compile the trigger. I don't have privilege to Recreate the same trigger.

    Can anyone please help me in figuring the problem. There is only one trigger on supplier master.

    Thanks
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Apr 2002
    Posts
    56
    You need the FOR EACH ROW in your definition.
    This is a row-trigger, not a statement trigger

    CREATE OR REPLACE TRIGGER dbtbriu_pai
    BEFORE INSERT OR UPDATE
    ON PALM_INDEX
    FOR EACH ROW
    DECLARE
    BEGIN etc...

  3. #3
    Join Date
    Apr 2002
    Posts
    56
    Sorry, my mistake, you have that already!

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    How do you know that it isn't updating.issue the following commands. Put the correct column names and values in the selects and inserts.


    select upd_date,one_of_the_columns from DATAP.SUPPLIER_MASTER
    where whateverkey = uniquekey;

    update DATAP.SUPPLIER_MASTER
    set one_of_the_columns = anything
    where whateverkey = uniquekey;

    select upd_date from DATAP.SUPPLIER_MASTER
    where whateverkey = uniquekey;

    rollback;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2004
    Posts
    1

    Wink

    [FONT=Arial][QUOTE=paginit]Hi,
    I am facing very unusual problem. I have update trigger defined on supplier_master.
    ....

    The trigger looks OK and should work, but I can think of two possibilities. One is what is the datatype of the upd_date attribute (will it support sysdate as a value?). The second possibility depends on the update table code. For example, "update table1 t1 set t1.col1 = t2.col1, t1.upd_date = t2.upd_date from table2 t2 where t1.key = t2.key"; would update col1, the trigger would update upd_date to sysdate, then the sysdate would update to the value of t2.upd_date.

    Good luck!

  6. #6
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Opps Sorry!!
    It weas my mistake. We have promotion tool which promotes oracle obects
    from Development to Production. The trigger was not promoted properly.
    The description for the trigger was different in both the database.
    Any ways thank you very much.


    Bye and good day

    Thanks
    Pagnint
    (No need to search web before posting new question)

Posting Permissions

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