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 help

Re: trigger help

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 7 Jan 2004 13:06:25 -0500
Message-ID: <T9CdnZeDnbk-12GiRVn-hA@comcast.com>


Use a SELECT statement

"Choung" <cchao_at_pmc.ca> wrote in message news:_gVKb.199648$PD3.5924809_at_nnrp1.uunet.ca...
| How do I check if the row exists in the LICENSEACCESS table, then issue
| either an INSERT or an UPDATE
|
| "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
| news:-M2dnVOomOnHomaiRVn-sQ_at_comcast.com...
| >
| > "Choung" <cchao_at_pmc.ca> wrote in message
| > news:MSEKb.199367$PD3.5921590_at_nnrp1.uunet.ca...
| > | Hi,
| > |
| > | I don't know too much about triggers but here's my trigger:
| > |
| > | create or replace trigger license_usage
| > | BEFORE DELETE or UPDATE on admuser.usession
| > | FOR EACH ROW
| > |
| > | BEGIN
| >
| > this insert statement copies data from all rows of the USESSION table
into
| > the LICENSEACCESS table
| > -- where's the WHERE clause?
| >
| > | 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;
| > |
| >
| > this update statement updates all rows in the LICENSEACCESS table to
the
| > same username
| >
| > | UPDATE admuser.licenseaccess
| > | set user_name = (select user_name from admuser.users where
| > | licenseaccess.user_id = users.user_id);
| > |
| > | END;
| > |
| > | This works but it's giving me duplicate output in my table. What I
want
| > it
| > | to do is, when the usession table changes I want it to update my
| > | licenseaccess table if the session_id already exist but add it if it
| > | doesn't. What should I add or change to make it work that way. I
don't
| > | understand :new or :old. I've tried reading up on it but the books I
| have
| > | aren't clear.
| > |
| >
| > :new refers to a record structure that contains the new values of the
row
| > after modified by the triggering statement, :old refers to the orginal
| value
| > of the row
| >
| > you need WHERE clauses on your statements so that they affect the
correct
| > rows
| > you need to you IF THEN ELSE logic to test a condition and then execute
| the
| > proper action
| >
| > in your specific case
| > you've got the trigger in the right place, except you're not handling
| > INSERTS into the USESSION table
| > you need to check if the row exists in the LICENSEACCESS table, then
issue
| > either an INSERT or an UPDATE
| > you also need to determine if there should be different actions in
| response
| > to an INSERT, UPDATE, or DELETE on your USESSION table
| > likely the UPDATE is going to be the typical action, so one way to
| implement
| > your logic is to first issue an UPDATE (i.e., assume the row exists),
and
| if
| > the update fails (because the row doesn't exists, which I'm assuming
would
| > usually not be the case) then issue the INSERT
| >
| >
| > --
| > Mark C. Stock
| > mcstock -> enquery(dot)com
| > www.enquery.com training & consulting
| >
| >
| > | thanks.
| > |
| > |
| >
| >
|
|
Received on Wed Jan 07 2004 - 12:06:25 CST

Original text of this message

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