Genius trigger thoughts needed, Mufasa ? 3

[saustin]

Hi,
Want to save data to a table (TBLSDSHIPMENTS) after a new record is saved in table (SHIPPER_LINE). Because of other triggers and restrictions on this table am not able to save all the data i need to a table with an after insert trigger.

Can save of some (but not all) of the critical fields needed. Is there a work-around or timer that can be used to go in, check and populate the TBLSDSHIPMENTS table ?

Have used a vb app with a timer to monitor a table and perform the updates but was hoping for a nicer solution.

Thanks, Steve.

 

[BJCooperIT]

I may not be Mufasa, but I do have an idea. How about a second table? Example:

SQL> create table BJC1
  2  (
  3    ID   NUMBER,
  4    FLD1 VARCHAR2(5),
  5    FLD2 VARCHAR2(5)
  6  );

Table created.

SQL> 
SQL> create table BJC2
  2  (
  3    ID         NUMBER,
  4    EXTRA_DATA VARCHAR2(5)
  5  );

Table created.

SQL> 
SQL> create or replace trigger test_trigger
  2    after insert on BJC2 for each row
  3  declare
  4    --
  5  begin
  6    update BJC1 set FLD2 = :new.EXTRA_DATa where id = :new.id;
  7  end test_trigger;
  8  /

Trigger created.

SQL> 
SQL> insert into bjc1 (id,FLD1) values (100,'data');

1 row created.

SQL> 
SQL> insert into bjc2 (id,EXTRA_DATA) values (100,'more');

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select * from bjc1;

        ID FLD1  FLD2
---------- ----- -----
       100 data  more

SQL>

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[SantaMufasa]

Mufasa's contribution here is to give BJ a Purple Star, since her trigger example is excellent for Steve's need !

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[saustin]

That is exactly what i want to do ! Let me provide further details....

TBLSDSHIPMENTS is the second table we want to populate with data from several tables with the when a new record is inserted in SHIPPER_LINE. This is the original trigger.

DECLARE
mSHIPPED_DATE SYSADM.SHIPPER.SHIPPED_DATE%type;
mPACKLIST_ID SYSADM.SHIPPER.PACKLIST_ID%type;
mLINE_NO SYSADM.SHIPPER_LINE.LINE_NO%type;
mCUST_ORDER_ID SYSADM.SHIPPER_LINE.CUST_ORDER_ID%type;
mCUST_ORDER_LINE_NO SYSADM.SHIPPER_LINE.CUST_ORDER_LINE_NO%type;
mORDER_QTY SYSADM.CUST_ORDER_LINE.ORDER_QTY%type;
mUSER_SHIPPED_QTY SYSADM.SHIPPER_LINE.USER_SHIPPED_QTY%type;
mQTY_BO varchar2(10);
mDESCRIPTION SYSADM.PART.DESCRIPTION%type;
mCUSTOMER_ID SYSADM.CUSTOMER_ORDER.CUSTOMER_ID%type;
mCUSTOMER_PO_REF SYSADM.CUSTOMER_ORDER.CUSTOMER_PO_REF%type;
mSHIPPED_TIME varchar2(30);
mseqNo NUMBER;
mPART_ID SYSADM.CUST_ORDER_LINE.PART_ID%type;
mTOTALSHIPPEDQTY SYSADM.CUST_ORDER_LINE.TOTAL_SHIPPED_QTY%type;
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

SELECT
SYSADM.SHIPPER.SHIPPED_DATE,
SYSADM.SHIPPER.PACKLIST_ID,
:OLDDATA.LINE_NO,
:OLDDATA.CUST_ORDER_ID,
:OLDDATA.CUST_ORDER_LINE_NO,
SYSADM.CUST_ORDER_LINE.ORDER_QTY,
:OLDDATA.USER_SHIPPED_QTY,
SYSADM.PART.DESCRIPTION,
SYSADM.CUSTOMER_ORDER.CUSTOMER_ID,
SYSADM.CUSTOMER_ORDER.CUSTOMER_PO_REF,
SYSADM.SHIPPER.SHIPPED_TIME,
SYSADM.CUST_ORDER_LINE.PART_ID,
SYSADM.CUST_ORDER_LINE.TOTAL_SHIPPED_QTY

INTO mSHIPPED_DATE, mPACKLIST_ID, mLINE_NO, mCUST_ORDER_ID, mCUST_ORDER_LINE_NO, mORDER_QTY, mUSER_SHIPPED_QTY, mDESCRIPTION, mCUSTOMER_ID, mCUSTOMER_PO_REF, mSHIPPED_TIME, mPART_ID, mTOTALSHIPPEDQTY
FROM SYSADM.SHIPPER, SYSADM.CUST_ORDER_LINE, SYSADM.CUSTOMER_ORDER, SYSADM.CUSTOMER, SYSADM.PART
WHERE SYSADM.SHIPPER.PACKLIST_ID= :OLDDATA.PACKLIST_ID
AND :OLDDATA.CUST_ORDER_ID = SYSADM.CUST_ORDER_LINE.CUST_ORDER_ID
AND :OLDDATA.CUST_ORDER_LINE_NO = SYSADM.CUST_ORDER_LINE.LINE_NO
AND SYSADM.CUSTOMER_ORDER.CUSTOMER_ID = SYSADM.CUSTOMER.ID
AND SYSADM.CUST_ORDER_LINE.CUST_ORDER_ID = SYSADM.CUSTOMER_ORDER.ID
AND SYSADM.CUST_ORDER_LINE.PART_ID = SYSADM.PART.ID;
IF mCUSTOMER_ID='97' AND mUSER_SHIPPED_QTY >0 THEN
SELECT TBLSDSHIPMENTS_SEQUENCE.nextval INTO mseqNo FROM dual;
INSERT INTO TBLSDSHIPMENTS (SDID, SHIPPED_DATE, PACKLIST_ID, LINE_NO, CUST_ORDER_ID, CUST_ORDER_LINE_NO, ORDER_QTY, USER_SHIPPED_QTY, QTY_BO, DESCRIPTION, CUSTOMER_ID, CUSTOMER_PO_REF, SHIPPED_TIME, SDSHIPMENTSTATUS, PART_ID)
VALUES (mseqNo, mSHIPPED_DATE, mPACKLIST_ID, mLINE_NO, mCUST_ORDER_ID, mCUST_ORDER_LINE_NO, mORDER_QTY, mUSER_SHIPPED_QTY, mORDER_QTY - (mTOTALSHIPPEDQTY + mUSER_SHIPPED_QTY), mDESCRIPTION, mCUSTOMER_ID, mCUSTOMER_PO_REF, SUBSTR(mSHIPPED_TIME,12,8), 10, mPART_ID);
COMMIT;
END IF;
END;


Due to contraints and three other triggers on SHIPPER_LINE this after insert trigger above can in rare cases prevent a new record from being saved in SHIPPER_LINE. Can not modify the other triggers or constraints on this table (3rd party app) but was able to save off JUST the :OLDDATA fields into the second table without causing a problem. IF however i put an after insert trigger on the second table TBLSDSHIPMENTS to go back and get the rest of the fields i need the constraints kick in again and the SHIPPER_LINE record is not saved.
Can you spot anything here that can help ?

Thanks, Steve.

 

[BJCooperIT]

Ouch! Are you saying that, under these circumstances, you want to defeat those constraints & triggers by allowing data to be inserted that would otherwise be considered invalid?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[saustin]

Not at all, am saying that my attempt to copy off values from the SHIPPER_LINE table (and associated tables) can in rare cases prevent a SHIPPER_LINE record from being saved.
Specifically the join on the SHIPPER table due to other triggers on the SHIPPER_LINE table.
Am looking to copy off values only and NOT effect the normal operation of the 3 rd party app.

 

[BJCooperIT]

If timing is the issue (since you mentioned at timer), and you can determine the time when it would be OK to make the updates, have you considered using DBMS_JOBS to execute a database procedure?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[saustin]

Precisely what I'm looking at now. Was trying to do anything to avoid this however.

 

[saustin]

Hi Mufasa and BJCooperIT,
Many thanks for your support and inspiration. Turns out that after much debugging that the trigger was failing due to a left outer join . Amazing how this can prevent a record from saving but the moral of the story is you learn something new every day.