Home » SQL & PL/SQL » SQL & PL/SQL » PL SQL Trigger
PL SQL Trigger [message #407170] Mon, 08 June 2009 22:42 Go to next message
apexdev88
Messages: 3
Registered: March 2009
Junior Member
Quote:
CREATE OR REPLACE TRIGGER RENTALPAYMENTS
AFTER INSERT ON LMR_PROPERTY_DETAILS

DECLARE

v_rent LMR_PROPERTY_DETAILS.RENT%TYPE;
v_bond LMR_PROPERTY_DETAILS.BOND%TYPE;
v_startdate LMR_PROPERTY_DETAILS.START_DATE%TYPE;
v_lease_period LMR_PROPERTY_DETAILS.LEASE_PERIOD%TYPE;
v_payment_interval LMR_PROPERTY_DETAILS.PAYMENT_INTERVAL%TYPE;
v_property_id LMR_PROPERTY_DETAILS.PROP_PROPERTY_ID%TYPE;
v_tenant_id LMR_PROPERTY_DETAILS.TEN_TENANT_ID%TYPE;
v_owner_id LMR_PROPERTIES.OWN_LMR_OWNER_ID%TYPE;
v_due_date DATE;
v_date DATE;
v_lmr_property_detail_id NUMBER;

CURSOR property_detail_cur (id_in IN NUMBER ) IS SELECT RENT, BOND, START_DATE, LEASE_PERIOD, PAYMENT_INTERVAL, PROP_PROPERTY_ID, TEN_TENANT_ID FROM LMR_PROPERTY_DETAILS WHERE LMR_PROPERTY_DETAIL_ID = id_in;


BEGIN

SELECT LAST_NUMBER INTO v_lmr_property_detail_id FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'LMR_PAYMENT_ID_SEQ';
DBMS_OUTPUT.PUT_LINE('LMR_PROPERTY_DETAIL_ID: ' ||v_lmr_property_detail_id);


OPEN property_detail_cur(v_lmr_property_detail_id);
LOOP

FETCH property_detail_cur INTO v_rent, v_bond, v_startdate, v_lease_period, v_payment_interval, v_property_id, v_tenant_id;
EXIT WHEN property_detail_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('RENT: '||v_rent);
DBMS_OUTPUT.PUT_LINE('BOND: '||v_bond);
DBMS_OUTPUT.PUT_LINE('START DATE: '||v_startdate);
DBMS_OUTPUT.PUT_LINE('LEASE PERIOD (MONTHS): '||v_lease_period);
DBMS_OUTPUT.PUT_LINE('PAYMENT INTERVAL (WEEKS): '||v_payment_interval);
DBMS_OUTPUT.PUT_LINE('PROPERTY ID: '||v_property_id);
DBMS_OUTPUT.PUT_LINE('TENANT ID: '||v_tenant_id);


END LOOP;

SELECT OWN_LMR_OWNER_ID INTO v_owner_id FROM LMR_PROPERTIES WHERE LMR_PROPERTY_ID = v_property_id;
DBMS_OUTPUT.PUT_LINE('OWNER ID: '||v_owner_id);

v_due_date := ADD_MONTHS(v_startdate, v_lease_period);
v_date := v_startdate;

INSERT INTO LMR_PAYMENTS(AMOUNT_DUE, TRANSACTION, DUE_DATE, COMPLETED, OWN_LMR_OWNER_ID, TEN_LMR_TENANT_ID)
VALUES(v_bond, 'h', v_date, 'y', v_owner_id, v_tenant_id);


WHILE ADD_WEEKS(v_date, v_payment_interval) < v_due_date LOOP

v_date := ADD_WEEKS(v_date, v_payment_interval);
DBMS_OUTPUT.PUT_LINE('PAYMENT INTERVAL DATE: '||v_date);
INSERT INTO LMR_PAYMENTS(AMOUNT_DUE, TRANSACTION, DUE_DATE, COMPLETED, OWN_LMR_OWNER_ID, TEN_LMR_TENANT_ID)
VALUES(v_rent, 'a', v_date, 'n', v_owner_id, v_tenant_id);


END LOOP;


END;
/



Quote:
ORA-01403: no data found ORA-01403: no data found ORA-06512: at "GROUP03.RENTALPAYMENTS", line 41 ORA-04088: error during execution of trigger 'GROUP03.RENTALPAYMENTS'
Error Unable to process row of table LMR_PROPERTY_DETAILS.



On another forum I posted on someone mentioned that it shouldn't work because of the sequence.

I am grabbing the LAST_NUMBER used in the sequence from the USER_SEQUENCES table. The trigger fires AFTER an INSERT so of course that number is incremented by 1 before the trigger fires.
I don't see anything wrong with this.
Re: PL SQL Trigger [message #407172 is a reply to message #407170] Mon, 08 June 2009 22:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I don't see anything wrong with this.
If you are getting the results you desire, proceed with your code.
Re: PL SQL Trigger [message #407174 is a reply to message #407170] Mon, 08 June 2009 23:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
apexdev88 wrote on Tue, 09 June 2009 05:42
On another forum I posted on someone mentioned that it shouldn't work because of the sequence.

As you did not post, what "should work" means, it is hard to deduce. But this approach is having a serious flaw - it is going to trouble when multiple users fire INSERT at the (nearly) same time?
Anyway, LAST_NUMBER is not used in the rest of the code.
apexdev88 wrote on Tue, 09 June 2009 05:42
I am grabbing the LAST_NUMBER used in the sequence from the USER_SEQUENCES table. The trigger fires AFTER an INSERT so of course that number is incremented by 1 before the trigger fires.
I don't see anything wrong with this.

If the sequence number is incremented before, it is probably done by some "not important" process (BEFORE INSERT trigger?) which should store it anywhere (:NEW.<column_with_id>?). Why do you not take it from there? But, without any details provided by you, it is just guessing.

To the error you posted: what is the line 41? It is quite descriptive - SELECT INTO does not return any row.
As you run it, you are able to see DBMS_OUTPUT messages to spot the statement causing it better.

Just one remark - that LOOP on the same table you are inserting looks suspicious - seems like a design flaw to me. Taking values from the last fetched row (and throwing away all the previous) is at least inefficient.
Maybe you shall read about database normalization and appropriately adjust data model.
As you did not post any details about it (CREATE TABLE scripts, used scenarios and triggers involved), this is just a feeling based on that construction.
Re: PL SQL Trigger [message #407232 is a reply to message #407174] Tue, 09 June 2009 04:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In addition, unless you only ever insert into the table LMR_PROPERTY_DETAILS using the INSERT INO....VALUES... syntax, you're probably going to get a mutating table error with this trigger - it selects from the table that the insert was into.
Re: PL SQL Trigger [message #407233 is a reply to message #407232] Tue, 09 June 2009 04:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Don't use the Last_Number column from user/all/dba_sequences - it doesn't do what you think it does.

If you look at the documentation it describes it as:
Quote:
Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used.
Re: PL SQL Trigger [message #407356 is a reply to message #407170] Tue, 09 June 2009 20:44 Go to previous messageGo to next message
apexdev88
Messages: 3
Registered: March 2009
Junior Member
Thank you very much all for your response.


Quote:
If the sequence number is incremented before, it is probably done by some "not important" process (BEFORE INSERT trigger?) which should store it anywhere (:NEW.<column_with_id>?). Why do you not take it from there? But, without any details provided by you, it is just guessing.


How do I do that? I try just to test, the following:

DBMS_OUTPUT.PUT_LINE((:NEW.LMR_PROPERTY_DETAIL_ID));

And I get the following:

Quote:
END;
Error report:
ORA-04082: NEW or OLD references not allowed in table level triggers
04082. 00000 - "NEW or OLD references not allowed in table level triggers"
*Cause: The trigger is accessing "new" or "old" values in a table trigger.
*Action: Remove any new or old references.
Re: PL SQL Trigger [message #407359 is a reply to message #407170] Tue, 09 June 2009 21:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
apexdev88,

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

:NEW & :OLD are valid on row level triggers

[Updated on: Tue, 09 June 2009 21:05]

Report message to a moderator

Re: PL SQL Trigger [message #407360 is a reply to message #407356] Tue, 09 June 2009 21:04 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I still have no idea, what is this trigger supposed to achieve; however I overlooked that the trigger is the table level one, so Oracle is right about using :NEW values in it.

Anyway, if the sequence number was incremented in that session before (using <sequence_name>.NEXTVAL), you may access the last generated number in that session by using <sequence_name>.CURRVAL pseudocolumn.
Re: PL SQL Trigger [message #407363 is a reply to message #407170] Tue, 09 June 2009 22:49 Go to previous message
apexdev88
Messages: 3
Registered: March 2009
Junior Member
Quote:
You need to help us by following the Posting Guidelines as stated below.

Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


I will make sure to make a more detailed and informative post next time I ask for help.


Thank you everyone for your help, managed to fix my code and it is working perfectly now.
Would have been stuck for a while if I hadn't known about LAST_NUMBER in the user_sequences table not being a good place to get the current sequence value from.
Previous Topic: Dynamic RowType Columns
Next Topic: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" too small
Goto Forum:
  


Current Time: Sat Dec 10 18:37:40 CST 2016

Total time taken to generate the page: 0.04690 seconds