Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2007
    Posts
    11

    Unanswered: oracle PL/SQL Trigger

    Hi All

    I have an oracle PL/SQL trigger that i need help on

    Currently i have set the trigger to be triggered off when data is inserted or updated in a table.... however i realised that the "update" function doesn't work as it did not get triggered off when there is an update in the table . (insert works fine!) someone kindy help! thanks.

    These are 2 sample trigger tt i've tried to written on my own

    Code:
    CREATE OR REPLACE TRIGGER "ITRADESYS"."PARTIAL_FILLED_TRIGGER" 
        AFTER
    INSERT OR UPDATE ON "ITRADESYS"."ORD_MST" FOR EACH ROW DECLARE
         dup_count CHAR(4);
    
    BEGIN
          if updating and (:NEW.ORD_STATUS = 4 and :NEW.OUTSTD_QTY != 0) then
    
         UPDATE PARTIAL_FILLED_TRIGGER set 
    			TRADE_DT=:NEW.TRADE_DT,                
                REF_NO=:NEW.REF_NO,                    
                ORD_NO=:NEW.ORD_NO
    		where REF_NO = :NEW.REF_NO and ALERTFLG = '0';
    ELSE
    
        SELECT Count(*) INTO dup_count from PARTIAL_FILLED_TRIGGER where REF_NO=:NEW.REF_NO;
        IF (dup_count <= 0) THEN
    	 INSERT INTO PARTIAL_FILLED_TRIGGER
    			(TRADE_DT,       
                REF_NO,         
                ORD_NO)
    		VALUES
    			(:NEW.TRADE_DT,       
                 :NEW.REF_NO,         
                 :NEW.ORD_NO)
        	END IF;
      
        END IF;
    END;
    and

    Code:
    CREATE OR REPLACE TRIGGER "ITRADESYS"."PARTIAL_FILLED_TRIGGER" 
        AFTER
    INSERT
    OR UPDATE ON "ITRADESYS"."ORD_MST" FOR EACH ROW DECLARE
         dup_count CHAR(4);
    
    BEGIN
        IF updating and (:NEW.ORD_STATUS = 4 and :NEW.OUTSTD_QTY != 0) then
        SELECT Count(*) INTO dup_count from PARTIAL_FILLED_TRIGGER where REF_NO=:NEW.REF_NO;
        IF (dup_count <= 0) THEN
    	 INSERT INTO PARTIAL_FILLED_TRIGGER
    			(TRADE_DT,       
                REF_NO,         
                ORD_NO)
    	VALUES
    			(:NEW.TRADE_DT,       
                 :NEW.REF_NO,         
                 :NEW.ORD_NO)
           ELSE
            UPDATE PARTIAL_FILLED_TRIGGER set 
    			TRADE_DT=:NEW.TRADE_DT,                
                REF_NO=:NEW.REF_NO,                    
                ORD_NO=:NEW.ORD_NO
    		where REF_NO = :NEW.REF_NO and ALERTFLG = '0';
    	END IF;
        END IF;
    END;
    thanks once again!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    We see triggers, but nothing else.

    The way you've written it, it will fire (upon updating) ONLY IF (:NEW.ORD_STATUS = 4 and :NEW.OUTSTD_QTY != 0). As you didn't provide sample data or, at least, UPDATE statement you used to test the trigger, I can only guess that you didn't update both 'ord_status' to 4 and 'outstd_qty' to be different from 0.

    BTW, your second trigger does nothing when inserting records into a table as IF block prevents it to.

  3. #3
    Join Date
    Jun 2007
    Posts
    11
    Hi there

    I just did a testing.... the 1st code was actually worse than the 2nd one because the 1st code could not even do the insertion part

    ok some sample data copied from my sql*plus

    From: Partial_Filled_Trigger Table(Target Table) (Trigger and table name is the same)
    REF_NO FILLED_QTY OUTSTD_QTY
    ----------------- ---------- ----------
    20070608000000190 100 300
    20070608000000202 100 300

    From : Ord_Mst (source table)
    REF_NO FILLED_QTY OUTSTD_QTY
    ----------------- ---------- ----------
    20070608000000190 300 100
    20070608000000202 200 200

    Erm... Can see the difference? If i use the 2nd code, when i first insert the data, the filled_qty and outstd_qty attributes is the same as the target table (which mean the trigger works for the insertion) but when i do the update to increase the amount of fille_qty, hence outstd_qty decreases, the data is not updated in the target table (partial_filled_trigger table).

    Yup hope this is clear enough
    Thanks alot... i hope someone can shed light on how to rectify this because I am stuck at this problem for quite a few days
    Thanks again

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by You said
    when i do the update to increase the amount of fille_qty, hence outstd_qty decreases, the data is not updated
    How do you know that 'outstd_qty' decreases? Did you write another database trigger to do that?

    Quote Originally Posted by I said
    provide UPDATE statement you used to test the trigger

  5. #5
    Join Date
    Jun 2007
    Posts
    11
    oh... the update and insertion is done thru a software application via a remote server
    i've confirmed with my internship supervisor that they only used the normal update sql statement and only the filled_qty and outstd_qty will be updated to the ord_mst table based on the ref_no.
    ord_status will only change if the outstd_qty is zero... so if it's still maintain as a non-zero in outstd_qty, then status will always remain as 4

    btw... the "outstd_qty' will be compute from the application , which is the total quantity minus the filled_qty.

    thanks

    PS/ btw will it be more useful and less time-consuming if i just create another trigger doing "update" function only?
    Last edited by peachtea; 06-22-07 at 00:02.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I was hoping for a test case like this one ... First, create tables and insert sample data into them:
    Code:
    SQL> CREATE TABLE ord_mst
      2  (ref_no NUMBER, ord_status NUMBER, filled_qty NUMBER, outstd_qty NUMBER);
    
    Table created.
    
    SQL>
    SQL> CREATE TABLE partial_filled_trigger
      2  (ref_no NUMBER, ord_status NUMBER, filled_qty NUMBER, outstd_qty NUMBER);
    
    Table created.
    
    SQL>
    SQL> INSERT ALL
      2    INTO ord_mst VALUES (190, 0, 300, 100)
      3             INTO ord_mst VALUES (202, 0, 200, 200)
      4             INTO partial_filled_trigger VALUES (190, 0, 100, 300)
      5             INTO partial_filled_trigger VALUES (202, 0, 100, 300)
      6             SELECT * FROM dual;
    
    4 rows created
    Create a trigger; I'm not going to code the computing - I'll simply write a message to the screen which will show whether the trigger fires correctly or not.
    Code:
    SQL> CREATE OR REPLACE TRIGGER  trg_pft
      2    AFTER INSERT OR UPDATE ON ord_mst
      3    FOR EACH ROW
      4  DECLARE
      5    dup_count NUMBER;
      6  BEGIN
      7    IF UPDATING AND :NEW.ord_status = 4 AND :NEW.outstd_qty <> 0
      8    THEN
      9       dbms_output.put_line('Updating');
     10    ELSIF INSERTING THEN
     11       dbms_output.put_line('Inserting');
     12    END IF;
     13  END;
     14  /
    
    Trigger created.
    Finally, let's update a record and insert a new one and watch the output:
    Code:
    SQL> UPDATE ord_mst SET ord_status = 4, outstd_qty = 20
      2  WHERE ref_no = 190;
    Updating                            --> OK
    
    1 row updated.
    
    SQL> INSERT INTO ord_mst VALUES (1, 2, 3, 4);
    Inserting                           --> OK
    
    1 row created.
    
    SQL>
    So the trigger will actually fire; I'd suggest you to first make it simple and working - forget about "if qty <> 0 and status = 4 and ..." stuff - go step by step and MAKE SURE those conditions are true. Simulate your application's computing by simple INSERT or UPDATE statements (like I did) and test.

    Try to write a single trigger; two versions are somehow confusing and Forum members (including me) do not know which one to watch and, perhaps, test.

    Although I've created a simple test case, I don't know whether it is correct ro not. You should do that and post it here. Don't forget to include your Oracle version.

    [EDIT]

    Why, oh why are you storing the result of the COUNT function into the CHAR variable?!? NUMBERS are here for such a purpose, not characters.
    Last edited by Littlefoot; 06-24-07 at 13:16.

  7. #7
    Join Date
    Jun 2007
    Posts
    11
    oh no
    i tried what u did (create dummy tables n data).

    i realise that the trigger is not firing. the 'partial_filled_trigger' data does not contain the updated data which was update/inserted at the ord_mst.

    is there something wrong with my oracle since urs can work??

    oh btw my oracle version is 10G
    Last edited by peachtea; 06-25-07 at 01:27.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't have a crystal ball and I have no idea what happened when you tried to follow my steps. Saying that "the trigger is not firing" is ... well, useless. Did you get any error message? If so, which one? Would it cost too much if you did what I did - use the good, old copy-paste technique so that we can SEE what you are doing?

  9. #9
    Join Date
    Jun 2007
    Posts
    11
    oh ok.. sorry about it... no there's no error msg

    output:

    Code:
    SQL> select * from test_partial;
    
        REF_NO ORD_STATUS FILLED_QTY OUTSTD_QTY
    ---------- ---------- ---------- ----------
           190          0        100        300
           202          0        100        300
    
    SQL> select * from test_mst;
    
        REF_NO ORD_STATUS FILLED_QTY OUTSTD_QTY
    ---------- ---------- ---------- ----------
           190          4        300         20
           202          4        300         20
             1          2          3          4
             4          3          2          1
    Trying to insert/update :

    Code:
    SQL> update test_mst set outstd_qty = 30 where ref_no = 190;
    
    1 row updated.
    
    SQL> insert into test_mst values (3,3,3,3);
    
    1 row created.
    i've copied paste ur trigger code... it is suppose to print the word 'updating' and 'inserting' right?

    yup.. sorry to bother u again..... thanks

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >it is suppose to print the word 'updating' and 'inserting' right?
    NO.
    The trigger run deep inside the database & doesn't your terminal exists.
    The DBMS_OUTOUT statements are useless within the trigger itself.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Peachtea,

    did you, perhaps, forget to enable output? As the first SQL statement, issue
    Code:
    SQL> SET SERVEROUTPUT ON
    Doing so, you should see 'updating' and 'inserting' messages.

  12. #12
    Join Date
    Jun 2007
    Posts
    11
    yup i see it now... thanks

    Progress so far....
    I've tried to make basic simple trigger for insert and update each (can work) and i slowly add in the conditions bit by bit with the dummy tables/data (works too).

    However, when i tried it with the actual project scope... that's where the problem is.

    Ok, i got an application called IDealer that is supposed to insert the data to the table, ord_mst, and this in turn will fired the trigger to update/insert data into the partial_filled_trigger table.
    The trigger can fired if i manually insert/update data in ord_mst table but will not get fired if i use Idealer to insert/update data in ord_mst. And strangly, even though it doesn't get fired, there is data been inserted/updated in ord_mst.

    Some outputs to illustrate my points :

    This is when i manually insert/update data into ord_mst, u can see that the data got insert/update in the secondary table, partial_filled_trigger.

    Code:
    SQL> insert into ord_mst (trade_dt,ref_no,ord_no, ord_status, outstd_qty) values (current_date, '222
    ', '20', 4,22);
    
    1 row created.
    
    SQL> select trade_dt,ref_no,ord_no, ord_status, outstd_qty from partial_filled_trigger where ref_no 
    = '222';
    
    TRADE_DT  REF_NO            ORD_NO    ORD_STATUS OUTSTD_QTY
    --------- ----------------- --------- ---------- ----------
    27-JUN-07 222               20                 4         22 
    
    SQL> select trade_dt,ref_no,ord_no, ord_status, outstd_qty from ord_mst where ref_no 
    = '222';
    
    TRADE_DT  REF_NO            ORD_NO    ORD_STATUS OUTSTD_QTY
    --------- ----------------- --------- ---------- ----------
    27-JUN-07 222               20                 4         22 
    
    SQL> update ord_mst set outstd_qty = 200 where ref_no = '222';
    
    1 row updated.
    
    SQL> select trade_dt,ref_no,ord_no, ord_status, outstd_qty from partial_filled_trigger where ref_no 
    = '222';
    
    TRADE_DT  REF_NO            ORD_NO    ORD_STATUS OUTSTD_QTY
    --------- ----------------- --------- ---------- ----------
    27-JUN-07 222               20                 4        200
    
    SQL>  select trade_dt,ref_no,ord_no, ord_status, outstd_qty from ord_mst where ref_no = '222';
    
    TRADE_DT  REF_NO            ORD_NO    ORD_STATUS OUTSTD_QTY
    --------- ----------------- --------- ---------- ----------
    27-JUN-07 222               20                 4        200

    This is when the Idealer application insert/update data into ord_mst (can insert!) but u realise that the data is not been updated/inserted into the secondary table, partial_filled_trigger

    Code:
    SQL> select trade_dt,ref_no,ord_no, ord_status, outstd_qty from ord_mst where ref_no = 2007060800000
    0312;
    
    TRADE_DT  REF_NO            ORD_NO    ORD_STATUS OUTSTD_QTY
    --------- ----------------- --------- ---------- ----------
    08-JUN-07 20070608000000312 05393378           4        200
    
    SQL> select trade_dt,ref_no,ord_no, ord_status, outstd_qty from partial_filled_trigger where ref_no 
    = 20070608000000312;
    
    no rows selected
    Does any of you knows what is going on? If i manually insert/update and it get fires away means that my trigger shld be ok right? why is it that when the IDealer application insert/update the primary table and the trigger is not working at all?

    Thanks for reading this long post and your patience.

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, INSERT is INSERT, no matter how it is done or who/what does it.

    Did you, perhaps, forget to recreate the trigger in order not to fire on your dummy table, but real one? Are you sure that both IDealer and your manually written INSERT look and do the same?

  14. #14
    Join Date
    Jun 2007
    Posts
    11
    yes, the trigger is already recreated to fire from the actual ord_mst table and not from the dummy table.

    I don't have access to the Idealer way of insertion/update but i've ask my supervisor and he said it is like normal sql line of command of insert and update. Like "insert into ord_mst values (attribute 1, attribute 2, etc);"

    Is there another type of insert n update sql statement??

  15. #15
    Join Date
    Jun 2007
    Posts
    11
    yup the trigger problem is solved. it works perfectly fine now.

    thank you for all your help, Littlefoot.

Posting Permissions

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