1 Reply Latest reply on Oct 9, 2012 1:01 PM by Vedant

    System date and time insert incorrect using trigger

    Vedant
      Dear All,

      i am ussing trigger to insert record from one schema to another schema.
      create or replace TRIGGER  "AFT_INS_CRM_SALES_DEAL" 
         AFTER INSERT ON WEBENQ.CRM_SALES_DEAL
         FOR EACH ROW
      
      declare
      PRAGMA AUTONOMOUS_TRANSACTION;
      
      i_id varchar2(30);
      t_id varchar2(30);
      e_id varchar2(30);
      a timestamp;
      
      begin
      --select lpad ( CRMDEAL.nextval, 8, '0' ) into i_id from dual;
      
      --DELETE FROM WEBENQ.CRM_SALES_DEAL;
      
      select LPAD(NVL(MAX(TO_NUMBER (ID)),0)+ 1,8,'0') into t_id from TATEST.CRM_SALES_DEAL;
      
      select to_char(sysdate, 'DD-MON-YYYY HH:MIPM') into a from dual;
      
      INSERT INTO TATEST.CRM_SALES_DEAL(ID,DEAL_NAME,REFER_NO,PHONE_NO,MOBILE_NO,EMAIL_ID,CUSTOMER_PROFILE,ENQUIRY_SOURCE,WEBSITE,SURNAME,POSTAL_CODE,ID_WEBENQ,
      DATE_ENQUIRY) 
      SELECT t_id,DEAL_NAME,REFER_NO,PHONE_NO,MOBILE_NO,EMAIL_ID,CUSTOMER_PROFILE,ENQUIRY_SOURCE,WEBSITE,SURNAME,
      POSTAL_CODE,id,a FROM WEBENQ.CRM_SALES_DEAL;
      
      DELETE FROM WEBENQ.CRM_SALES_DEAL;
      
      COMMIT;
      
      end;
      select to_char(sysdate, 'DD-MON-YYYY HH:MIPM') into a from dual;

      value of variable a is insert 08-OCT-2020 12:07PM but this is not correct .
      i want to insert current date and time.


      Thanks
        • 1. Re: System date and time insert incorrect using trigger
          Vedant
          CREATE OR REPLACE TRIGGER "AFT_INS_CRM_SALES_DEAL"
          AFTER INSERT ON WEBENQ.CRM_SALES_DEAL
          FOR EACH ROW

          declare
          ----------PRAGMA AUTONOMOUS_TRANSACTION;

          i_id varchar2(30);
          t_id varchar2(30);
          e_id varchar2(30);
          a timestamp;
          begin
          --select lpad ( CRMDEAL.nextval, 8, '0' ) into i_id from dual;

          --DELETE FROM WEBENQ.CRM_SALES_DEAL;

          select LPAD(NVL(MAX(TO_NUMBER (ID)),0)+ 1,8,'0') into t_id from TATEST.CRM_SALES_DEAL;

          select to_date(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS') into a from dual;

          INSERT INTO TATEST.CRM_SALES_DEAL
          (ID,DEAL_NAME,REFER_NO,PHONE_NO,MOBILE_NO,EMAIL_ID,CUSTOMER_PROFILE,ENQUIRY_SOURCE,WEBSITE,SURNAME,POSTAL_CODE,ID_WEBENQ,DATE_ENQUIRY)
          values(t_id,:NEW.DEAL_NAME,:NEW.REFER_NO,:NEW.PHONE_NO,:NEW.MOBILE_NO,:NEW.EMAIL_ID,:NEW.CUSTOMER_PROFILE,:NEW.ENQUIRY_SOURCE,
          :NEW.WEBSITE,:NEW.SURNAME,:NEW.POSTAL_CODE,:NEW.id,a);

          ------SELECT t_id,DEAL_NAME,REFER_NO,PHONE_NO,MOBILE_NO,EMAIL_ID,CUSTOMER_PROFILE,ENQUIRY_SOURCE,WEBSITE,SURNAME,
          ------POSTAL_CODE,id,a FROM WEBENQ.CRM_SALES_DEAL;
          ------MYTEST;
          -------commit;
          -------DELETE FROM WEBENQ.CRM_SALES_DEAL;

          end;

          add to_date with to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS' into a from dual; 
          
          like
          
          select to_date(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS') into a from dual;