Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Unanswered: Triggers on inserts

    Hi
    I wanted to write a script, a trigger, which would get triggered on a insert.
    Then using the primarykey of the insert (docid), I want to know if the insert was full or partial, by checking another field of the inserted record (status) from the table.

    Can someone help me with the same.
    Thanx and Regards
    Aruneesh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Triggers on inserts

    Originally posted by aruneeshsalhotr
    Hi
    I wanted to write a script, a trigger, which would get triggered on a insert.
    Then using the primarykey of the insert (docid), I want to know if the insert was full or partial, by checking another field of the inserted record (status) from the table.

    Can someone help me with the same.
    Thanx and Regards
    Aruneesh
    CREATE TRIGGER trg AFTER INSERT ON tbl
    FOR EACH ROW
    BEGIN
    IF :NEW.status = 'FULL' THEN
    -- Do whatever
    ELSIF :NEW.status = 'PARTIAL' THEN
    -- Do whatever
    END IF;
    END;

    You would put BEFORE instead of AFTER, if the intention of the trigger is to validate the insert and possibly reject it.

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Trigger compilation errors

    Hi Andrew,
    I am sure I must be missing some piece as I wrote a trigger and it gives me compilation error.
    I have a table wm_order, with an order status of orderstatus.
    The following is the trigger i created.

    create or replace trigger trg1
    after insert on wm_order
    for each row
    begin
    if :new.orderstatus=1 then
    dbms_output.put_line('order ' | :new.orderid | ' has an orderstatus of 1');
    elsif :new.orderstatus=0 then
    dbms_output.put_line('order ' | :new.orderid | ' has an orderstatus of 0');
    end if;
    end;
    /

    I would appreciate your response.
    Thanx and Regards
    Aruneesh

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    to concatenate strings do || rather than |

    Alan

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Trigger compilation errors

    Originally posted by aruneeshsalhotr
    Hi Andrew,
    I am sure I must be missing some piece as I wrote a trigger and it gives me compilation error.
    I have a table wm_order, with an order status of orderstatus.
    The following is the trigger i created.

    create or replace trigger trg1
    after insert on wm_order
    for each row
    begin
    if :new.orderstatus=1 then
    dbms_output.put_line('order ' | :new.orderid | ' has an orderstatus of 1');
    elsif :new.orderstatus=0 then
    dbms_output.put_line('order ' | :new.orderid | ' has an orderstatus of 0');
    end if;
    end;
    /

    I would appreciate your response.
    Thanx and Regards
    Aruneesh
    Tip: instead of saying "it gives me a compilation error" it would be helpful to post the actual error message!

    However, in this case I can see that you have used "|" for concatenate, when you should use "||".

  6. #6
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Error detail

    10:10:28 CINK SQL> create or replace trigger trg1
    10:23:59 2 after insert on wm_order
    10:24:04 3 for each row
    10:24:06 4 begin
    10:24:08 5 if :new.orderstatus=1 then
    10:24:12 6 dbms_output.put_line('order ' || :new.orderid || ' has an orderstatus of 1');
    10:24:28 7 elsif :new.orderstatus=0 then
    10:24:33 8 dbms_output.put_line('order ' | :new.orderid | ' has an orderstatus of 0');
    10:24:40 9 end if;
    10:24:44 10 end;
    10:24:45 11 /

    Warning: Trigger created with compilation errors.

  7. #7
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Error

    I typed it all, but i am using || instead of | itself.
    It is more of a typo.
    Thanx

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Error detail

    Originally posted by aruneeshsalhotr
    10:10:28 CINK SQL> create or replace trigger trg1
    10:23:59 2 after insert on wm_order
    10:24:04 3 for each row
    10:24:06 4 begin
    10:24:08 5 if :new.orderstatus=1 then
    10:24:12 6 dbms_output.put_line('order ' || :new.orderid || ' has an orderstatus of 1');
    10:24:28 7 elsif :new.orderstatus=0 then
    10:24:33 8 dbms_output.put_line('order ' | :new.orderid | ' has an orderstatus of 0');
    10:24:40 9 end if;
    10:24:44 10 end;
    10:24:45 11 /

    Warning: Trigger created with compilation errors.
    When that happens, type in SHOW ERROR to see more details.

  9. #9
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Actual question on trigger on insert

    Hi
    I got the trigger to compile fine. There is however a reason behind creating the trigger.
    All the code and operation are based out of JSP, which used Oracle to store the information on its behalf. For me Oracle, is nothing more than a data-store.
    What I wan to accomplish is trap errors before they are notified.

    Thats why I want to capture the orderstatus field for being 0 or null.
    I created few sample orders, which creates wm_order row, but the trigger didnt fire.

    Any ideas.
    Aruneesh

  10. #10
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow

    Even the
    set serveroutput on
    was set.

    I dont see any output on the screen on the inserts.

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by aruneeshsalhotr
    Even the
    set serveroutput on
    was set.

    I dont see any output on the screen on the inserts.
    In SQL Plus? It works for me:

    SQL> create table wm_order( orderid int, orderstatus int );

    Table created.

    SQL> create or replace trigger trg1
    2 after insert on wm_order
    3 for each row
    4 begin
    5 if :new.orderstatus=1 then
    6 dbms_output.put_line('order ' || :new.orderid || ' has an orderstatus of 1');
    7 elsif :new.orderstatus=0 then
    8 dbms_output.put_line('order ' || :new.orderid || ' has an orderstatus of 0');
    9 end if;
    10 end;
    11 /

    Trigger created.

    SQL> set serveroutput on
    SQL> insert into wm_order(orderid,orderstatus) values(1,1);
    order 1 has an orderstatus of 1

    1 row created.

  12. #12
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Trigger initiated when inserts from application

    Hi Andrew
    Getting the trigger initiated through the SQL Plus interface is fine. But I want to get the trigger initiated when a call is made from my JSP (java class), and using JDBC, an insert happens on the DB.
    I want the trigger to initiate then.
    Hope I am making sense this time.
    Thanx and Regards
    Aruneesh

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Trigger initiated when inserts from application

    Originally posted by aruneeshsalhotr
    Hi Andrew
    Getting the trigger initiated through the SQL Plus interface is fine. But I want to get the trigger initiated when a call is made from my JSP (java class), and using JDBC, an insert happens on the DB.
    I want the trigger to initiate then.
    Hope I am making sense this time.
    Thanx and Regards
    Aruneesh
    The trigger is firing, it is just that DBMS_OUTPUT output is not visible automatically from JSP (or any other application) like it is from SQL Plus. To see the output you would need to call DBMS_OUTPUT.GET_LINE, and you would have to call DBMS_OUTPUT.ENABLE beforehand. SQL Plus does all this for you.

    This isn't really a practical use of triggers - what are you really trying to achieve?

  14. #14
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Email generated on possible errorneous record

    Hi Andrew
    I knew this was coming from you.
    I want to be notified through an email (thankfully the SMTP server is on the same box as Oracle), when a possible order has an incorrect data or was not fully populated.
    Hope the purpose of the discussion is much more clear now.
    Thanx and Regards
    Aruneesh

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Email generated on possible errorneous record

    Originally posted by aruneeshsalhotr
    Hi Andrew
    I knew this was coming from you.
    I want to be notified through an email (thankfully the SMTP server is on the same box as Oracle), when a possible order has an incorrect data or was not fully populated.
    Hope the purpose of the discussion is much more clear now.
    Thanx and Regards
    Aruneesh
    OK, so you are just using DBMS_OUTPUT to test the concept? How about this then: create a procedure to handle the error. For now, make it insert a row into a new table:

    create or replace procedure log_order_error
    ( p_error_text in varchar2 )
    is
    begin
    insert into error_table( text ) values (p_error_text);
    end;
    /

    Amend your trigger accordingly:

    create or replace trigger trg1
    after insert on wm_order
    for each row
    begin
    if :new.orderstatus=1 then
    log_order_error('order ' || :new.orderid || ' has an orderstatus of 1');
    elsif :new.orderstatus=0 then
    log_order_error('order ' || :new.orderid || ' has an orderstatus of 0');
    end if;
    end;
    /

    Now you can see whethe it is working by inspecting error_table (after committing the transaction of course).

    When you want to change to use email, you can just amend the procedure definition.

Posting Permissions

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