You Asked

Hi,
my table is with fist name , last name , status.
Now the thing is I want to change the status to "APPROVED" as soon as I made the entry in
last name, if last name column is empty status should be default lets say "PENDING".

I tried it using triggers but getting the mutating error.
Thanks

and Chris said...

Mutating table - you must running an update in the trigger!

There's no need for this. Set the value of :new.status in the trigger:

create table t (
  fname varchar2(100),
  lname varchar2(100),
  status varchar2(10) default 'PENDING'
);

create or replace trigger tt 
before insert or update on t
for each row
begin
  if :new.lname is not null then 
    :new.status := 'APPROVED';
  else
    :new.status := 'PENDING';
  end if;
end;
/

insert into t (fname, lname) values ('Chris', null);
insert into t (fname, lname) values ('Chris', 'Saxon');
select * from t;

FNAME  LNAME  STATUS    
Chris         PENDING   
Chris  Saxon  APPROVED

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Or use virtual column...

Toon, July 05, 2016 - 11:46 am UTC

The STATUS column is basically a redundant column at the row level, the way you describe your use-case. You could either just code it's derivation in a view, and use the view to query STATUS, or have STATUS be a virtual column to your table.

CREATE TABLE
(...
,STATUS varchar2(8)generated always as
  (case when lname is not null then 'APPROVED' else 'PENDING' end )
)

July 05, 2016 - 12:17 pm UTC

Yep, great point Toon

Perfect answer.

Nitesh, July 06, 2016 - 6:15 pm UTC

Answer was as accurate as it can be.
Keep helping us
Thanks and regards

July 06, 2016 - 11:52 pm UTC

glad we could help out

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer