6 Replies Latest reply: Mar 28, 2012 5:43 AM by Paul Horth RSS

    Trigger Help 2

    user11876003
      I have write a trigger like below

      create or replace trigger test_trig_drcrinst
      after update or insert on t_debit_credit_note
      REFERENCING OLD AS OLD NEW AS NEW
      for each row
      declare
      -- local variables here
      begin
      IF :NEW.dcn_clm_claim_id IS NULL THEN
      IF UPDATING  THEN

      GWP_REPORT_PAK_THUSITHA(:new.dcn_debit_credit_note_no);

      ELSIF INSERTING THEN
      GWP_REPORT_PAK_THUSITHA(:new.dcn_debit_credit_note_no);

      END IF;

      END IF;

      end test_trig_drcrinst;

      But it's not working in INSERTING what is the issue . It's working in UPDATING properly
        • 1. Re: Trigger Help 2
          Paul  Horth
          How can we tell if you don't tell us the insert that is being used to fire the trigger? My immediate suspicion is that
          :NEW.dcn_clm_claim_id is not null on an insert.
          • 2. Re: Trigger Help 2
            user11876003
            :NEW.dcn_clm_claim_id is null I have check that and when the procedure is run manually then its also work .
            • 3. Re: Trigger Help 2
              Paul  Horth
              Please post the insert statement and the CREATE TABLE statement

              Edited by: Paul Horth on 28-Mar-2012 03:03
              • 4. Re: Trigger Help 2
                prakash
                HI,
                See this
                SQL> set serveroutput on size 145000
                SQL> ;
                  1  CREATE OR REPLACE TRIGGER emp_audit
                  2     AFTER INSERT OR DELETE OR UPDATE
                  3     ON emp
                  4     FOR EACH ROW
                  5  DECLARE
                  6  BEGIN
                  7     CASE
                  8        WHEN INSERTING
                  9        THEN
                 10           DBMS_OUTPUT.put_line ('Adding new emps in emp table ........');
                 11        WHEN UPDATING
                 12        THEN
                 13           DBMS_OUTPUT.put_line ('Modifying emps data ...........');
                 14        WHEN DELETING
                 15        THEN
                 16           DBMS_OUTPUT.put_line ('Deleting   emps data ......... ');
                 17     END CASE;
                 18* END;
                SQL> /
                
                Trigger created.
                
                SQL> insert into emp select * from emp;
                Adding new emps in emp table ........
                Adding new emps in emp table ........
                Adding new emps in emp table ........
                Adding new emps in emp table ........
                Adding new emps in emp table ........
                Adding new emps in emp table ........
                Adding new emps in emp table ........
                
                 
                
                SQL> UPDATE EMP SET SAL=100;
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                Modifying emps data ...........
                 
                
                SQL> DELETE FROM EMP;
                Deleting   emps data .........
                Deleting   emps data .........
                Deleting   emps data .........
                Deleting   emps data .........
                 
                
                SQL> 
                
                
                It works for me , for also it should work 
                
                You must inserting :NEW.dcn_clm_claim_id  as not null , please check   that .
                Thanks,P Prakash

                Edited by: prakash on Mar 28, 2012 3:07 AM
                • 5. Re: Trigger Help 2
                  user11876003
                  Insert statement is procedure can't post its too long ,
                  But I have used same procedure when the update or INSERT , when update the relevant table its working but when the inserting data to relevant
                  table its not working .
                  • 6. Re: Trigger Help 2
                    Paul  Horth
                    I need to see your INSERT and the CREATE TABLE before I can help further.

                    The fact that it works when you invoke the procedures manually indicates it is the logic of your trigger. The obvious thing is that, when inserting
                    the column is not null.

                    Edited by: Paul Horth on 28-Mar-2012 03:43