Create trigger to insert into another table

Question

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.

You need to find out what error occured during compilation/authorization failed for the trigger Then give us that error message. If you have TOAD, go to the trigger in Schema Browser, right click and Compile and it should give you the error message. If you just ran the script in SQLPLUS I guess it should have given you an error message there. Also I suggest you add a column list to your INSERT statement to reduce possible errors. — Nick.Mc
– Nick.Mc, Commented Nov 13, 2012 at 3:44
Can you try to do a insert into table BILL outside the trigger? And check whether you have read/write permissions for the tables you are using in your trigger. This will help you in debugging the issue. — Jacob
– Jacob, Commented Nov 13, 2012 at 5:25
I tried insert into the Table Bill w/o the trigger and it seems to be working fine. I tried hard-coding the value in the trigger and its working fine too, i believe the issue lies with the below statement: SELECT SeqReadNo.CurrVal INTO varReadNo FROM DUAL; Select MeterID INTO varMeterID From Reading Where ReadNo = varReadNo; — FirstTimer
– FirstTimer, Commented Nov 13, 2012 at 5:55
@FirstTimer Try executing these two as seperate sql statements and see whether you are able to see the output? SELECT SeqReadNo.CurrVal FROM DUAL; and Select MeterID From Reading Where ReadNo = :varReadNo; Of course substitute values for varReadNo — Jacob
– Jacob, Commented Nov 13, 2012 at 6:07
@Polppan. Test with both the statements, they are working fine as separate query — FirstTimer
– FirstTimer, Commented Nov 13, 2012 at 6:21

Petr Pribyl · Accepted Answer · 2012-11-13 14:26:54Z

4

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

edited Nov 13, 2012 at 14:26

answered Nov 13, 2012 at 6:11

Petr Pribyl

3,6151 gold badge22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Aldridge Over a year ago

Could even combine the insert and the select into a single statement.

RC. · Accepted Answer · 2012-11-13 03:39:36Z

0

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';

answered Nov 13, 2012 at 3:39

RC.

28.4k10 gold badges80 silver badges94 bronze badges

Comments

Jacob · Accepted Answer · 2012-11-13 06:32:20Z

0

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;

answered Nov 13, 2012 at 6:32

Jacob

14.8k69 gold badges215 silver badges323 bronze badges

2 Comments

FirstTimer Over a year ago

I tested with the exception; the new readings will be inserted however the bill will not be created. Any ideas in solving this?

Jacob Over a year ago

What exactly is bill? some id number getting generated?

Smart003 · Accepted Answer · 2014-07-23 11:05:01Z

0

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.

answered Jul 23, 2014 at 11:05

Smart003

1,1292 gold badges16 silver badges32 bronze badges

Collectives™ on Stack Overflow

Create trigger to insert into another table

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related