Re: trigger help

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

|

|