CODEWITHSUNDEEP
databaseplsqltriggersdml
Yun8483
Yun8483

Reputation: 27

Show which record was updated by using trigger

I am a freshmen in PL/SQL and a have such a problem:

I want to create a trigger that shows surname of the person, whose tel number was updated

what I have done

Set SERVEROUTPUT ON

Create or replace trigger inform
after update of scontact_number on SUPLIER
for each row
DECLARE 
x SUPLIER.ssurname%TYPE;
BEGIN
Select SSURNAME
into x
from SUPLIER;
dbms_output.put_line('New tel number for ' || x);
END;
/ 

UPDATE SUPLIER
SET SCONTACT_NUMBER = 6765092654
WHERE ID_SUPLIER = 1;

trigger was compiled, but when I had tried to fire it, I got an error 'table is mutating'

How can I fix it? I will be grateful for any answer

Upvotes: 1

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You don't need a table reference in the trigger. Just use :new:

Create or replace trigger inform
after update of scontact_number on SUPLIER
for each row
DECLARE 
    x SUPLIER.ssurname%TYPE;
BEGIN
    Select :new.SSURNAME
    into x
    from dual;

    dbms_output.put_line('New tel number for ' || x);
END;
/

Or, more simply:

BEGIN
    dbms_output.put_line('New tel number for ' || :new.SSURNAME);
END;

And no variable is required.

Upvotes: 2

Related Questions