Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: trigger error

Re: trigger error

From: Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl>
Date: Tue, 06 Jan 2004 20:47:48 +0100
Message-ID: <aq3mvvopr8fmej7kvl89vgjisier3ikhbq@4ax.com>


On Tue, 6 Jan 2004 14:13:45 -0500, "Choung" <cchao_at_pmc.ca> wrote:

>I've created the following trigger but it keeps failing:
>
>create or replace trigger license_usage
>BEFORE DELETE on admuser.usession
>FOR EACH ROW
>DECLARE
> v_type VARCHAR2(10);
>BEGIN
> IF UPDATING THEN
> v_type := 'UPDATE'
> ELSIF DELETING THEN
> v_type := 'DELETE'
> END IF;
>
> UPDATE admuser.licenseaccess
> set licenseaccess.last_active_time = usession.last_active_time
> WHERE licenseacces.session_id = usession.session_id;
>
> IF SQL%NOTFOUND THEN
> INSERT INTO admuser.licenseaccess (user_id, session_id, login_time,
>last_active_time)
> select user_id, session_id, login_time, last_active_time
> from admuser.usession;
>
> UPDATE admuser.licenseaccess
> set user_name = (select user_name from admuser.users where
>licenseaccess.user_id = users.user_id);
> END IF;
>END;
>
>I get this error:
>
>Line # = 10 Column # = 5 Error Text = PL/SQL: SQL Statement ignored
>Line # = 11 Column # = 42 Error Text = PLS-00201: identifier
>'USESSION.LAST_ACTIVE_TIME' must be declared
>
>all my tables belong to a user named admuser.
>
>Any idea's?
>

1 Your trigger fires on delete only, yet you check whether the table is updating. This test is redundant, as the trigger fires on delete only
2 The trigger fires on delete of the usession table. In triggers you refer to the table on which it is firing by using the :new.<column_name> and :old.<column_name>. As this is a delete trigger only :old applies

So you get
  UPDATE admuser.licenseaccess
    set licenseaccess.last_active_time = :old.last_active_time     WHERE licenseacces.session_id = :old..session_id;

etc, etc
3 I don't see why you can't insert a new record with one single statement. Anyway the extra update should be avoided. Also IIRC, you will not reach the SQL%NOTFOUND test, but you will simply run into the NO_DATA_FOUND exception when you don't update any record. Performancewise you would better check the existence of the record, instead of trapping the notfound exception

All in all, I would advise you to return to your manual and check whether you understand the basic concept of a trigger, and the :old and the :new construct.

Regards

--
Sybrand Bakker, Senior Oracle DBA
Received on Tue Jan 06 2004 - 13:47:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US