Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: trigger error
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 DBAReceived on Tue Jan 06 2004 - 13:47:48 CST