2 Replies Latest reply: May 16, 2013 7:33 PM by davidp 2 RSS

    Can inseat of trigger be Serialized

    n_shah18
      Hi ,
      I am using a instead of inert trigger on a view , I need to insert in two different table using a common form.
      PK of table is Fk other second table.
      Can i make this process atomic , to get the value PK(FK) for second insert to work.


      Thanks
      create or replace
      TRIGGER INSTD_OF_INSERT_ADD_OFFENDER 
      instead of INSERT ON SOR_ADD_OFF_V 
      FOR EACH ROW 
      DECLARE
                  x INTEGER;
                  y INTEGER;
        
                  l_server sor_email_config.server%type;
                  l_sender sor_email_config.sender%type;
      
                  error_message varchar2(500);
      BEGIN
       
       
                  SELECT COUNT(*)
                  INTO x
                  FROM sor_offender
                  WHERE offender_id = :NEW.offender_id;
                  /*
                  IF(:new.offender_id IS NULL)and x=0 THEN
                    SELECT sor_offender_seq.nextval INTO :new.offender_id FROM DUAL;
                  END IF;*/
                
                
                --SELECT sor_offender_seq.nextval INTO y FROM DUAL;
      
                select server,  SENDER 
                    into l_server,  l_sender 
                    from sor_EMAIL_config scon
                    where upper(scon.current_setting)='Y'
                    and scon.configuration_id=(select max(scon2.configuration_id)
                                            from sor_EMAIL_config scon2
                                            where scon2.current_setting='Y'
                    );
                    
               SOR_GOOGLE_MAP_SERVICE.Send_Alert_Email(l_server, l_sender, 'neel.shah@lsgsolutions.com', to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss'), 'test', 'offender_ID '||:NEW.offender_id);
                         
      
        IF x = 0 THEN
          INSERT INTO sor_offender
          (doc_number,first_name, middle_name,last_name,sex,SIR_NAME,entry_user,entry_date)
          VALUES
          (:new.doc_number,:new.FIRST_NAME, :new.MIDDLE_NAME,:new.LAST_NAME,:new.SEX,:new.SIR_NAME,:new.so_user, sysdate);
        END if ;
        
        /*
         IF(:new.reg_offender_id IS NULL) and x=0 THEN
                SELECT registration_offender_xref_seq.nextval INTO :new.reg_offender_id FROM DUAL;
              END IF;
      */
        
        IF x = 0 THEN
         INSERT INTO REGISTRATION_OFFENDER_XREF
        (REG_TYPE_ID,offender_id, STATUS,REGISTRATION_DATE,SENTENCE_END_DATE,END_REGISTRATION_DATE,AGGRAVATED,HABITUAL,COMMENTS,entry_user,entry_date)
          VALUES
       (:NEW.REG_TYPE_ID,:NEW.offender_id,:NEW.STATUS,:NEW.REGISTRATION_DATE,:NEW.SENTENCE_END_DATE, :NEW.END_REGISTRATION_DATE,:NEW.AGGRAVATED,:NEW.HABITUAL,:NEW.COMMENTS,:new.entry_user,sysdate)
       ;
       
       
        END if;
        
        
       
      
        end; 
        • 1. Re: Can inseat of trigger be Serialized
          damorgan
          I really don't understand your question but from within an INSTEAD-OF trigger you can do anything you want that is syntactically valid.

          For example ... put all of your code into a stored procedure and just call the proc passing values to IN parameters.
          • 2. Re: Can inseat of trigger be Serialized
            davidp 2
            Can i make this process atomic , to get the value PK(FK) for second insert to work.
            You should look into using the RETURNING clause on your insert - it lets you get the sequence value generated in the INSERT back into a variable in a single statement:
            declare l_seq integer;
            begin
              insert into test_parent (parent_id) values (seq_id.nextval) returning parent_id into l_seq;
              insert into test_child(parent_id, child_pos) values (l_seq, 1);
            end;
            I think that might be what you are asking about.
            regards,
            David