Triggers & PL/SQL

From: Kenneth Schulstad <kschulst_at_ifi.uio.no>
Date: Sun, 3 Feb 2002 18:46:36 +0100
Message-ID: <a3jstk$sn4$1@maud.ifi.uio.no>

Problem 1:
I want to read the values that's been updated, inserted into or deleted from a certain table. I want to put these values into a variable, preferrably a VARCHAR. I'm using a trigger, but I get an error message when trying to access :old and :new values in the trigger code.

Example:
Let's say I have the following table (let's call it 'numbers'):

a                 b
------       -------
1111         191919
1111         565656
1111         454545
2222         323232

On this table I create a trigger, coded paritally pseudo, since the construction of this trigger is part of what puzzles me:

CREATE OR REPLACE TRIGGER trig
AFTER DELETE OR INSERT OR UPDATE ON numbers BEGIN
   DECLARE
        text VARCHAR2; -- or other type of variable to hold the data in question

     BEGIN
       text := <the actual data from the rows that is being updated>
       <further processing of the text-variable>
     END;

END;
/

So, if I execute the following SQL:

DELETE * FROM numbers WHERE a='2222';

...then I want the trigger's text-variable to hold something like "2222 323232" or I something like that.

Any suggestions to how I should construct my trigger to accomplish this?

---

---

Problem 2 (not as important):
I have tried using cursors in triggers, where the cursor's SELECT-statement does a query on the cetain table that the actual trigger is monitoring. It's okay to establish such a trigger, but when I later try to do any of the actions that would cause the trigger to activate (in the example under, this would be either to delete, insert or update anything to the table) I receive an error (ORA-1405 Fetched column value is Null). This is annoying, since what i would like to do when the table is updated is reading the values of the table that is updated into a record and process them further, but it seems that the trigger somehow locks (?) the table in question, thus hindering the cursor from getting the select-statement through.

CREATE OR REPLACE TRIGGER trig
AFTER DELETE OR INSERT OR UPDATE ON table1 BEGIN
 DECLARE
    CURSOR cur1 IS SELECT * FROM table1;      rec1 cur1%ROWTYPE;
 BEGIN

     open cur1;
     FETCH cur1 INTO rec1;


     <...>

 END;
END;
/

I'd be very grateful for any hints or comments to my problems. Problem 1 being the one that's most critical for me to solve immediately. Thanks for taking the time!

Regards
Kenneth Schulstad Received on Sun Feb 03 2002 - 11:46:36 CST