1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Trigger to update table

Discussion in 'SQL PL/SQL' started by akika, Mar 27, 2017.

  1. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Code (SQL):
    CREATE OR REPLACE TRIGGER TEST_TRG
    BEFORE UPDATE ON TEST_TBL FOR EACH ROW DECLARE
    v_pt_code VARCHAR2(20); v_pt_user VARCHAR2(30); v_pt_date DATE;
    BEGIN IF :NEW.PT_DATE IS NULL THEN SELECT substr(b.CODE,3,10) INTO V_PT_CODE FROM order_tbl b, empl_tbl a WHERE a.ID = b.ID AND SUBSTR(a.emp_name,1,15) =:APP_USER; SELECT nvl(v('APP_USER'), USER) INTO V_PT_USER FROM dual; :NEW.PT_CODE := V_PT_CODE;
    :NEW.PT_USER := V_PT_USER;
    :NEW.PT_DATE := SYSDATE; END IF;
    END;
    How to make it work getting bind variable error and it's not updating the fields when date is entered in table
    Any idea
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Triggers cannot use bind variables; there is absolutely NO way to populate them. If :APP_USER is supposed to be 'APP_USER' then code that into the select statement, otherwise code the exact user name you are expecting to process.

    Since the trigger is invalid it can't update anything; fix the error by replacing the bind variable with literal text.

    I did get it to work following my instructions:

    Code (SQL):

    SQL> CREATE TABLE test_tbl(
      2  pt_code    varchar2(20),
      3  pt_user    varchar2(30),
      4  pt_date    DATE);

    TABLE created.

    SQL>
    SQL> CREATE TABLE order_tbl(
      2  id NUMBER,
      3  code       varchar2(20));

    TABLE created.

    SQL>
    SQL> CREATE TABLE empl_tbl(
      2  id NUMBER,
      3  emp_name   varchar2(40));

    TABLE created.

    SQL>
    SQL> CREATE OR REPLACE FUNCTION v(p_user IN varchar2)
      2  RETURN varchar2
      3  AS
      4     v_user varchar2(30):= 'SMORG';
      5  BEGIN
      6     IF p_user = 'APP_USER' THEN
      7             v_user := 'SMORG';
      8     ELSE
      9             v_user := 'SMORG';
     10     END IF;
     11     RETURN v_user;
     12  END;
     13  /

    FUNCTION created.

    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> CREATE OR REPLACE TRIGGER TEST_TRG
      2  BEFORE UPDATE ON TEST_TBL
      3  FOR EACH ROW
      4  DECLARE
      5     v_pt_code VARCHAR2(20);
      6     v_pt_user VARCHAR2(30);
      7     v_pt_date DATE;
      8  BEGIN
      9     IF :NEW.PT_DATE IS NULL THEN
     10             SELECT substr(b.CODE,3,10) INTO V_PT_CODE FROM order_tbl b, empl_tbl a WHERE a.ID = b.ID AND SUBSTR(a.emp_name,1,15) ='APP_USER';
     11             SELECT DISTINCT nvl(v('APP_USER'), USER) INTO V_PT_USER FROM dual;
     12             :NEW.PT_CODE := V_PT_CODE;
     13             :NEW.PT_USER := V_PT_USER;
     14             :NEW.PT_DATE := SYSDATE;
     15     END IF;
     16  END;
     17  /

    TRIGGER created.

    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> INSERT ALL
      2  INTO empl_tbl
      3  VALUES(1, 'APP_USER')
      4  INTO empl_tbl
      5  VALUES(2, 'SMORG')
      6  INTO empl_tbl
      7  VALUES(3, 'GALOPNING')
      8  SELECT * FROM dual;

    3 ROWS created.

    SQL>
    SQL> INSERT ALL
      2  INTO order_tbl
      3  VALUES(1, 'PONGO')
      4  INTO order_tbl
      5  VALUES(2, 'PINGO')
      6  INTO order_Tbl
      7  VALUES(3, 'ORP')
      8  SELECT * FROM dual;

    3 ROWS created.

    SQL>
    SQL> INSERT INTO test_tbl
      2  SELECT code, emp_name, NULL
      3  FROM empl_tbl JOIN order_tbl ON (order_tbl.id = empl_tbl.id);

    3 ROWS created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> SELECT substr(b.CODE,3,10) FROM order_tbl b, empl_tbl a WHERE a.ID = b.ID AND SUBSTR(a.emp_name,1,15) ='APP_USER';

    SUBSTR(B.C
    ----------
    NGO

    SQL>
    SQL> SELECT v('APP_USER') FROM dual;

    V('APP_USER')
    --------------------------------------------------------------------------------
    SMORG

    SQL>
    SQL> SELECT * FROM test_tbl;

    PT_CODE              PT_USER                        PT_DATE
    -------------------- ------------------------------ ---------
    PONGO                APP_USER
    PINGO                SMORG
    ORP                  GALOPNING

    SQL>
    SQL> UPDATE test_tbl
      2  SET pt_user = 'GLORPUS' WHERE pt_user = 'SMORG';

    1 ROW updated.

    SQL>
    SQL> SELECT * FROM test_tbl;

    PT_CODE              PT_USER                        PT_DATE
    -------------------- ------------------------------ ---------
    PONGO                APP_USER
    NGO                  SMORG                          27-MAR-17
    ORP                  GALOPNING

    SQL>
    Hopefully this will help you.
     
  3. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Hi,
    Ive tried what u have mentioned above.. the trigger has been compiled BUT its not working..

    1. ive done added a substring in the where condition of the select and its not getting any value.. even when its being run separately.

    2. the trigger is not working...
    the input in that will be pt_id, pt_status... in the form user will input the pay_date for that id & status and need to fire the trigger to update table for pt_code, pt_user and pt_date.
    Dont have a primary key in the table.

    Can you please check where i'm wrong in below code.


    Code (SQL):

    CREATE TABLE
    (
    pt_id varchar2(20),
    pt_status CHAR(1),
    pt_code varchar2(20),
    pt_pay_date DATE,
    pt_user varchar2(30)
    pt_ord CHAR(1),
    pt_ship varchar2(30)
    );
      CREATE OR REPLACE TRIGGER TEST_TRG
       BEFORE UPDATE ON TEST_TBL
       FOR EACH ROW
       DECLARE
         v_pt_code VARCHAR2(20);
         v_pt_user VARCHAR2(30);
         v_pt_date DATE;
     
       BEGIN
       IF :NEW.PT_PAY_DATE IS NOT NULL THEN
        SELECT substr(b.CODE,3,10)
        INTO V_PT_CODE
        FROM order_tbl b, empl_tbl a
        WHERE a.ID = b.ID
        AND LOWER(SUBSTR(a.emp_name,1,15)) =LOWER(SUBSTR(V('APP_USER')1,15)||'.com';
     
       SELECT DISTINCT nvl(v('APP_USER'), USER) INTO V_PT_USER FROM dual;
                  :NEW.PT_CODE := V_PT_CODE;
                  :NEW.PT_USER := V_PT_USER;
                  :NEW.PT_DATE := SYSDATE;
          END IF;
       END;
       /          
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then there are no users meeting that condition. Run the select by itself, outside of the trigger and see what is returned. I expect it displays 'no rows selected'.
     
  5. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    there's the users.. when not using the substring getting the data.. ive run it outside ..
    even if i have hardcode the data for the first select .. the trigger not working.. any idea whts wrong or wht i have missed out in it?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "when not using the substring getting the data.. " which points to that as the possible problem.

    AND LOWER(SUBSTR(a.emp_name,1,15)) =LOWER(SUBSTR(V('APP_USER')1,15)||'.com';

    Nothing can match that comparison since you're trying to compare a 15-character string with a 19-character string. Maybe you need to do this:

    AND LOWER(SUBSTR(a.emp_name,1,15))||'.com' =LOWER(SUBSTR(V('APP_USER')1,15)||'.com';

    You really need to give careful thought to what you write and the results it returns.
     
  7. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    yea.. ur right I agree with u .. i've messed out on this conditions.. Thanks !!
    But still the trigger is not working :S
    Any idea? wht else ive missed out.. its compiling but not updating the fields...
    via form user will choose date for pay_date field then for this ID, status it need to update the other fields code, user, date ....
    while running trigger manually only ID is set as input value 1 ..
     
    Last edited: Mar 30, 2017
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to prove this isn't working; it compiles without error and presuming you correct your where condition I can't understand why you claim it isn't. Of course no one here knows what you are doing since you don't post screen captures of the 'action'.
     
  9. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Hi,

    Actually, Ive created a form.. When I click on edit.. below form is opened and user can choose date from date picker and when click on save.. pay date is updated in the table test_tbl for that ID and status.

    However, It should fire the trigger to update the pt_user, pt_date and pt_code for that ID and status which is not working.

    ive attached the screenshots of form and table.
     

    Attached Files:

  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This has been moved to the proper forum where those working with forms can help.

    It would have been good to explain the ENTIRE problem at the beginning rather than waiting until now to explain the issue.
     
  11. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    Is there a way that I could test the trigger individually without the form?? Atleast to know that the trigger is doing what it should... By the way if the pt_pay_date is null and via form user entering the data shouldn't it fire the trigger to update the other columns?
     
    Last edited: Mar 31, 2017
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No since properly written forms trigger relies on input from blocks in the form. To ensure that it IS doing what you want it to do you must test it through the forms interface. Also forms triggers are different from database triggers; that I could get this to work in the database doesn't mean that it will do the same job in the form, as you have found out.
     
  13. akika

    akika Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    New Delhi
    the trigger will be on the database only.. The form is on apex5.0
    Ive tried to run the trigger via SQL devlpr and pt_id is as input value and all other fields is null
    The field pt_status also should be in the return..
    As user will get for this pt_Id and pt_status...
    I will try to check in another apex forum
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,645
    Likes Received:
    370
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    As I posted before I got the trigger to function properly; once you fix the WHERE Clause to return data it SHOULD work for you as well. I have tried to copy and paste your code and ran into avoidable errors; the fixed code is shown below in a working example you should run:

    Code (SQL):
    CREATE TABLE test_tbl
    (
    pt_id varchar2(20),
    pt_status CHAR(1),
    pt_code varchar2(20),
    pt_pay_date DATE,
    pt_user varchar2(30),
    pt_ord CHAR(1),
    pt_ship varchar2(30)
    );

    CREATE TABLE order_tbl(
    id NUMBER,
    code       varchar2(20));

    CREATE TABLE empl_tbl(
    id NUMBER,
    emp_name   varchar2(40));

    INSERT INTO empl_tbl VALUES(1, 'SMORG');
    INSERT INTO empl_tbl VALUES(2, 'APP_USER');
    commit;

    INSERT INTO order_Tbl VALUES (1, 'BORBO');
    INSERT INTO order_tbl VALUES(2, 'BERBO');
    commit;

    BEGIN
       FOR i IN 1..10 loop
               INSERT INTO test_tbl
               VALUES(i, 'S', 'BORBO', sysdate-i, 'APP_USER', 'S', 'SHIPPED');
       END loop;

       commit;
    END;
    /

    CREATE OR REPLACE FUNCTION v(p_user IN varchar2)
      RETURN varchar2
      AS
         v_user varchar2(30):= 'SMORG';
      BEGIN
         IF p_user = 'APP_USER' THEN
                 v_user := 'SMORG';
         ELSE
                 v_user := 'SMORG';
         END IF;
         RETURN v_user;
      END;
      /

      CREATE OR REPLACE TRIGGER TEST_TRG
       BEFORE UPDATE ON TEST_TBL
       FOR EACH ROW
       DECLARE
         v_pt_code VARCHAR2(20);
         v_pt_user VARCHAR2(30);
         v_pt_date DATE;
     
       BEGIN
       IF :NEW.PT_PAY_DATE IS NOT NULL THEN
        SELECT substr(b.CODE,3,10)
        INTO V_PT_CODE
        FROM order_tbl b, empl_tbl a
        WHERE a.ID = b.ID
        AND LOWER(SUBSTR(a.emp_name,1,15))||'.com' =LOWER(SUBSTR(V('APP_USER'),1,15))||'.com';
     
       SELECT DISTINCT nvl(v('APP_USER'), USER) INTO V_PT_USER FROM dual;
                  :NEW.PT_CODE := V_PT_CODE;
                  :NEW.PT_USER := V_PT_USER;
                  :NEW.PT_PAY_DATE := SYSDATE;
          END IF;
       END;
       /        

    SELECT * FROM test_tbl;

    UPDATE test_tbl SET pt_Ord = 'P';

    commit;

    SELECT * FROM test_Tbl;