CODEWITHSUNDEEP
plsqltriggers
lidjan1
lidjan1

Reputation: 25

Update inside pl/sql Trigger

I have table 

messages ( 
  id number, 
  mess varchar2(4000), 
  short_mess(100) )

I want to create trigger that does something like that:

INSERT into messages ( id, message ) values ( some_id, some_message)

And then in table messages I would like to have ( some_id, some_message, some_message_shortcut)

My triggers looks like this:

create or replace trigger "MESSAGES_T1"
AFTER
insert on "MESSAGES"
for each row
begin
UPDATE  MESSAGES set "short_mess" = 'aaa'
where id = :new.id;
end;

But when i try to inserd new row I get error 

ORA-04091: table FIRSTAPP.MESSAGES is mutating, trigger/function may not see it ORA-06512: at "FIRSTAPP.MESSAGES", line 2 ORA-04088: error during execution of trigger 'FIRSTAPP.MESSAGES'

How should i do this? ( 'aaa' was just for testing purposes ) Thanks in advance.

Upvotes: 1

Views: 588

Answers (1)

user330315
user330315

Reputation:

Don't update, just assign the new value. But you need a BEFORE trigger for that to work, you can't modify the new row in an AFTER trigger:

create or replace trigger "MESSAGES_T1"
  BEFORE insert on "MESSAGES"
  for each row
begin
  :new.short_mess := 'aaa';
end;
/

Upvotes: 1

Related Questions