This discussion is archived
1 Reply Latest reply: Oct 9, 2012 6:01 AM by Vedant RSS

System date and time insert incorrect using trigger

Vedant Explorer
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points