Re: Triggers & PL/SQL

In article <a3jstk$sn4$1_at_maud.ifi.uio.no>, "Kenneth says...
>
>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;
>/

create or replace trigger trig
after delete or insert or update on number FOR EACH ROW
declare

    old_text long;
    new_text long;
begin

    if ( inserting or updating ) then

       new_text := :new.a || ' ' || :new.b;     end if;
    if ( updating or deleting ) then

       old_text := :old.a || ' ' || :old.b;     end if;
    ........
end;

but remember, old_text and new_text are local to the trigger -- they vanish after the trigger is done. If you need this data in your app, you would:

create or replace package stateful_data
as

   old_text long;
   new_text long;
end;
/

and the trigger body would be

begin

    stateful_data.new_text := null;
    stateful_data.new_text := null;
    if ( inserting or updating ) then

       stateful_data.new_text := :new.a || ' ' || :new.b;     end if;
    if ( updating or deleting ) then

       stateful_data.old_text := :old.a || ' ' || :old.b;     end if;
    ........
end;

and also don't forget if you insert/update/delete more then one row -- only the "last" row will be represented here, you would have to goto a plsql table in the package in order to support multi-row operations. See

http://osi.oracle.com/~tkyte/Mutate/index.html

for ideas there.

>
>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.
>

Well, I cannot see how you would get that error but anyway. That is NOT the way triggers work.

To further process the record, use a

BEFORE INSERT OR UPDATE ON T
FOR EACH ROW trigger and just access the :new record values -- changing them at will.

Read:

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a76939/adg13trg.htm#376

before going on -- it'll be well worth your time.

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

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp