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 - 09:06:02 CST
