Trigger logic

I want to create a Trigger that will update 2 columns that I have in my table. I want to Update these columns on Insert and Update.
If its an Insert I would like to populate my column 'CREATED_FROM' with
SELECT SYS_CONTEXT ('USERENV',  'SESSION_USER')  || '/' || SYS_CONTEXT ('USERENV', 'HOST')
   FROM DUAL
AND Column 'Action' with
'New row Inserted'

and if its an Update to the LCFNAME field I want to populate the  'CREATED_FROM' with the same as Insert
SELECT SYS_CONTEXT ('USERENV',  'SESSION_USER')  || '/' || SYS_CONTEXT ('USERENV', 'HOST')
   FROM DUAL
AND Column 'Action' with
'The row has been Updated'

I have had some trouble trying to get this to work..I have so far

CREATE OR REPLACE TRIGGER trgLCFCHECKTABLE
  AFTER UPDATE OR INSERT ON LCF_CHECK_TABLE  --I know I need to add the column here that is being updated?
DECLARE
  v_action VARCHAR2(255);
BEGIN
  IF UPDATING THEN
    v_action := 'The row has been Updated';
  END IF;
  IF INSERTING THEN
    v_action := 'New row Inserted';
  END IF;
  UPDATE TABLE LCF_CHECK_TABLE
     SET CREATED_FROM = (SELECT SYS_CONTEXT ('USERENV',  'SESSION_USER')  || '/' || SYS_CONTEXT ('USERENV', 'HOST'), v_action  FROM DUAL),
     ACTION = v_action;
END;
/

Any help would be appreciated
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
To update a single column, the select has to return a single value.

Your select is returning TWO values:
SYS_CONTEXT ('USERENV',  'SESSION_USER')  || '/' || SYS_CONTEXT ('USERENV', 'HOST')
and
v_action

You also don't need the variable v_action.  Just use a CASE statement in the update statement.

Your update also likely needs a WHERE clause.  Otherwise you are updating ALL rows.
0
johnsoneSenior Oracle DBACommented:
You don't need an update statement.  It actually will cause an error because you are updating the table the trigger is on.

CREATE OR REPLACE TRIGGER trgLCFCHECKTABLE
  AFTER UPDATE OR INSERT ON LCF_CHECK_TABLE  --I know I need to add the column here that is being updated?
  FOR EACH ROW
BEGIN
  IF UPDATING THEN
    :new.action := 'The row has been Updated';
  END IF;
  IF INSERTING THEN
    :new.action := 'New row Inserted';
  END IF;
   :new.CREATED_FROM = SYS_CONTEXT ('USERENV',  'SESSION_USER')  || '/' || SYS_CONTEXT ('USERENV', 'HOST');
END;
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
yeah that was meant to be taken out....

I actually was able to put that in the default for the CREATED_FROM field. I would like to put the text in the ACTION field if the LCFNAME  field has been UPDATED or INSERTED

If I do a where clause isn't the logic something like Where :OLD = :NEW or something like that?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
>>It actually will cause an error because you are updating the table the trigger is on.

Yep, completely missed that!!!
0
jknj72Author Commented:
I got this Johnson when I tried to run the trigger. Should I just make it BEFORE Update or INSERT?

Error report -
ORA-04084: cannot change NEW values for this trigger type
04084. 00000 -  "cannot change NEW values for this trigger type"
*Cause:    New trigger variables can only be changed in before row
           insert or update triggers.
*Action:   Change the trigger type or remove the variable reference.
0
johnsoneSenior Oracle DBACommented:
Sorry, I missed the AFTER.  Yes, you need to change it to BEFORE.
0
jknj72Author Commented:
yep that worked...Thanks johnsone
0
jknj72Author Commented:
Thanks again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.