CODEWITHSUNDEEP
oracle-databaseplsqltriggers
FirstTimer
FirstTimer

Reputation: 45

Create trigger to insert into another table

I have some problem executing the trigger below:

CREATE OR REPLACE TRIGGER AFTERINSERTCREATEBILL
AFTER INSERT
ON READING
FOR EACH ROW 

DECLARE

varReadNo   Int;
varMeterID  Int;
varCustID   Varchar(10);

BEGIN 

SELECT SeqReadNo.CurrVal INTO varReadNo FROM DUAL;

Select MeterID INTO varMeterID
From Reading 
Where ReadNo = varReadNo;

Select CustID INTO varCustID
From Address A
Join Meter M 
on A.postCode = M.postCode
Where M.MeterID = varMeterID;

INSERT INTO BILL VALUES 
(SEQBILLNO.NEXTVAL, SYSDATE, 'UNPAID' , 100 , varCustID , SEQREADNO.CURRVAL); 

END;

Error Message:

*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

*Action: Rewrite the trigger (or function) so it does not read that table.

Does it mean that I am not suppose to retrieve any details from table Reading?

I believe that the issue occur as I retrieving data from Table Reading while it is inserting the value:

SELECT SeqReadNo.CurrVal INTO varReadNo FROM DUAL;

Select MeterID INTO varMeterID
From Reading 
Where ReadNo = varReadNo;

It's there anyway to resolve this? Or is there a better method to do this? I need to insert a new row into table BILL after entering the table Reading where the ReadNo need to reference to the ReadNo I just insert.

Upvotes: 0

Views: 49718

Answers (4)

Smart003
Smart003

Reputation: 1119

we can combined insert and select statement 

CREATE OR REPLACE TRIGGER AFTERINSERTCREATEBILL
AFTER INSERT
ON READING
FOR EACH ROW 

DECLARE

varCustID   Varchar(10);

BEGIN 

 insert into bill 
 values 
 select SEQBILLNO.NEXTVAL, 
        SYSDATE, 
        'UNPAID' , 
        100 ,
        CustID,SEQREADNO.CURRVAL 
 From  Address A
 Join  Meter M 
 on    A.postCode = M.postCode
 Where M.MeterID = :new.MeterID; 

END;

try the above code.

Upvotes: 0

Petr Pribyl
Petr Pribyl

Reputation: 3575

You cannot retrieve records from the same table in a row trigger. You can access values from actual record using :new and :old (is this your case?). The trigger could then be rewritten to

CREATE OR REPLACE TRIGGER AFTERINSERTCREATEBILL
AFTER INSERT
ON READING
FOR EACH ROW 

DECLARE

  varCustID   Varchar(10);

BEGIN 

  Select CustID INTO varCustID
    From Address A
    Join Meter M 
      on A.postCode = M.postCode
    Where M.MeterID = :new.MeterID;

  INSERT INTO BILL VALUES 
  (SEQBILLNO.NEXTVAL, SYSDATE, 'UNPAID' , 100 , varCustID , SEQREADNO.CURRVAL); 

END;

If you need to query other record from READING table you have to use a combination of statement triggers, row trigger and a PLSQL collection. Good example of this is on AskTom.oracle.com

Upvotes: 4

Jacob
Jacob

Reputation: 14731

Add exception handling in your trigger and see what is happening, by doing it would be easy for you to track the exceptions.

CREATE OR REPLACE TRIGGER AFTERINSERTCREATEBILL
AFTER INSERT
ON READING
FOR EACH ROW 

DECLARE

  varCustID   Varchar(10);

BEGIN 

  -- your code

EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20299)));
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20298)));

END;

Upvotes: 0

RC.
RC.

Reputation: 28197

Make sure that you have the necessary permissions on all the tables and access to the sequences you're using in the insert.

I haven't done Oracle in awhile, but you can also try querying dba_errors (or all_errors) in order to try and get more information on why your SP isn't compiling.

Something to the tune of:

SELECT * FROM dba_errors WHERE owner = 'THEOWNER_OF_YOUR_SP';

Upvotes: 0

Related Questions