Insert "variable from update" using Triggers in PL/SQL

Question

I have a table like this one:

create table person(
id_person number(5),
city varchar(20)
);

insert into person values (1, 'NY');
insert into person values (2, 'Los Angeles');

I want to register "person" movements into a new table:

create table move_city(
new_location varchar(200)
);

And I have a trigger to do this:

create or replace trigger person_movement
after update of city on person
for each row
begin
insert into move_city values('The person has moved from ' ||:old.city|| ' to ' || :new.city);
end;
/

And is working as expected.

If I do:

update person set city='Boston' where id_person=1;

I have on move_city: 'The person has moved from NY to Boston'

My problem is that I want to put the 'id_person' in 'move_city' and obtain something like 'The person with id .... has moved from NY to Boston', and I don't know what i have to do in my trigger.

I mean something like this on the trigger.

insert into move_city values('The person with ' ||id_person|| ' has moved from ' ||:old.city|| ' to ' || :new.city);

But it doesn't work.

Any suggestion?

Update:

I have tried with:

create or replace trigger person_movement
after update of city on person
for each row
declare
id2 person.id_person%type;
begin
select id_person into id2 from person where id_person=:new.id_person;
insert into move_city values('The person with ' ||id2|| ' has moved from ' ||:old.city|| ' to ' || :new.city);
end;
/

But when I do:

update person set city='Boston' where id_person=1;

I obtain:

ORA-04091: table SYSTEM.PERSON is mutating, trigger/function may not see it

ORA-06512: at "SYSTEM.PERSON_MOVEMENT", line 4

ORA-04088: error during execution of trigger 'SYSTEM.PERSON_MOVEMENT'

David Faber · Accepted Answer · 2018-04-23 16:52:27Z

1

You can use either :new or :old here:

INSERT INTO move_city
  ( new_location )
VALUES
  ( 'The person with ' || :new.id_person|| ' has moved from ' ||:old.city|| ' to ' || :new.city);

However, I think for your move_city table the best thing to do would be to have separate columns for id_person, old_location, and new_location, then add the supporting information when you retrieve the data from the table. That way you might do:

INSERT INTO move_city
  ( id_person, old_location, new_location )
VALUES
  ( :new.id_person, :old.city, :new.city );

(It would also be a good idea to have a DATE column in move_city with a default value of SYSDATE.)

And in your trigger you could only do the insert where old.city differs from new.city:

IF ( old.city != new.city OR old.city IS NULL ) THEN
    INSERT INTO move_city
      ( id_person, old_location, new_location )
    VALUES
      ( :new.id_person, :old.city, :new.city );
END IF;

Hope this helps.

answered Apr 23, 2018 at 16:52

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arlet09 Over a year ago

Thanks for the idea but I have to do it as I said

David Faber Over a year ago

Well, the answer to your question is at the top. Good luck

Collectives™ on Stack Overflow

Insert "variable from update" using Triggers in PL/SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related