trigger not firing 3

[jcarrott]

I have a table PURCHORDER that is located in the schema LAWSON.
I had a trigger running on that table.

CREATE OR REPLACE TRIGGER PROCURE."LAWSON_PO_ISSUED_FLAG_UPD_TRG"
AFTER UPDATE OF "ISSUED_FLAG"
ON "LAWSON"."PURCHORDER"
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF ( uppero.issued_flag) = 'N' AND uppern.issued_flag) = 'Y' AND uppern.revised_fl) <> 'Y' AND uppern.released_fl) = 'Y') THEN
INSERT into PROCURE.RECPO (company, po_number, vendor, buyer_code) values o.company, .po_number, .vendor, .buyer_code);
END IF;
END;

I copied it and made changes to create a new trigger on a different field in the same table

CREATE OR REPLACE TRIGGER PROCURE."LAW_PO_LEA_RELEASED_FL_UPD_TRG"
AFTER UPDATE OF "RELEASED_FL"
ON "LAWSON"."PURCHORDER"
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF ( uppero.released_fl) = 'N' AND uppern.released_fl) = 'Y' AND uppero.po_code) = 'LEA' AND uppero.closed_fl) = 'N' AND
uppero.cancelled_fl) = 'N' AND uppero.tot_order_amt) > 0) THEN
INSERT into PROCURE.PO_LEASE (company, po_number, tot_order_amt, nbr_lines, closed_lines, buyer_code, closed_fl)
values o.company, .po_number, .tot_order_amt, .nbr_lines, .closed_lines, .buyer_code, .closed_fl);
END IF;
END;

The issue is that this second trigger never fires. Can anybody see what I am doing wrong?

Thank you.

 

[SantaMufasa]

Without some representative data with which we can test, I can offer only some things to check out:[ul][li]Are any of these columns NULLable?:[/li][ul][LI]RELEASED_FL[/li][li]PO_CODE[/LI][LI]CLOSED_FL[/LI][LI]CANCELLED_FL[/LI][LI]TOT_ORDER_AMT[/LI][/UL]If any are NULL, then the trigger does not fire.[LI]Can you tell us more about why you want to INSERT a new row in the table made up entirely of existing/old values? (This doesn't explain why the trigger is not firing, but I am curious.)[/li][/ul]If you can provide us with a "CREATE TABLE..." statement and a few lines of INSERTs, and UPDATEs that illustrate the non-firing, I'm sure we can be more helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[jcarrott]

All fields are set to Null? equals N.

This trigger is on our Purchase Order table. More importantly these are PO's for leased items. There is a need for emails to be sent out, a Invoice to be created, and a credit memo to be created so we can automate some of this process.

I am loading a temp table that will be read hourly be a Perl program. All I need is the few details from the table, and yes the data did not change - just the released flag.

I am not a DBA, we are closing the PO using a Lawson program and the only field that changes is the flag. When the flag changes I wanted to save off a few fields.

 

[SantaMufasa]

You can do some value confirmations by placing the following code immediately following your "BEGIN" statement:

  dbms_output.put_line('For the trigger to fire:'||chr(10)||
    '['||upper(:o.released_fl)||'] (s/b = "N") AND '||chr(10)||
    '['||upper(:n.released_fl)||'] (s/b = "Y") AND '||chr(10)||
    '['||upper(:o.po_code)||'] (s/b = "LEA") AND '||chr(10)||
    '['||upper(:o.closed_fl)||'] (s/b = "N") AND '||chr(10)||
    '['||upper(:o.cancelled_fl)||'] (s/b = "N") AND '||chr(10)||
    '['||upper(:o.tot_order_amt)||'] (s/b > 0)'||'.');

If your output environment is SQL*Plus, you must execute the following command in your session prior to executing code that fires the trigger:

set serveroutput on format wrap

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Dagon]

Are you sure that both ISSUED_FLAG and RELEASED_FL are being updated?

For Oracle-related work, contact me through Linked-In.

 

[jcarrott]

Issued_flag works, but the release_fl does not.

I think that what ever is wrong with the released_fl is also wrong with the closed_fl. So I am handling those 2 triggers as one and they both update the new table po_lease.

 

[Dagon]

You didn't answer my question, which was "Are you sure that both ISSUED_FLAG and RELEASED_FL are being updated?", not "Are both triggers firing?".

For Oracle-related work, contact me through Linked-In.

 

[jcarrott]

I am sorry for not reading and responding to the question.

Yes when I look at the database using Toad the flags were changed.

 

[Beilstwh]

Did you issue a commit on the session doing the change. The insert will not be visible until committed.

Bill
Lead Application Developer
New York State, USA

 

[jcarrott]

The solution turned out to be a security setting. All the triggers work just fine and no code was changed.