CODEWITHSUNDEEP
oracle11g
Abdul Basit
Abdul Basit

Reputation: 21

Oracle 11g Triggers

I have create a table person(id, name ,samenamecount).The samenamecount attribute can be null but for each row can store the row count for same names.I am achieving this by calling a stored procedure inside a after insert trigger.Below is my code.

create or replace procedure automatic(s in person.name%type)
AS
BEGIN
update person set samenamecount=(select count(*) from person where name=s) where name=s;
END;
create or replace trigger inserttrigger
after insert
on person
for each row
declare
begin
 automatic(:new.name);
end;

On inserting a row it is giving error like table ABCD.PERSON is mutating, trigger/function may not see it. Can somebody help me to figure out this?

Upvotes: 0

Views: 60

Answers (2)

MT0
MT0

Reputation: 167981

You can use the trigger:

CREATE TRIGGER inserttrigger
AFTER INSERT ON person
BEGIN
  MERGE INTO person dst
  USING (
    SELECT ROWID AS rid,
           COUNT(*) OVER (PARTITION BY name) AS cnt
    FROM   person
  ) src
  ON (src.rid = dst.ROWID)
  WHEN MATCHED THEN
    UPDATE SET samenamecount = src.cnt;
END;
/

fiddle

If you want to make it more efficient then you could use a compound trigger and collate the names that are being inserted and only update the matching rows.

Upvotes: 0

MT0
MT0

Reputation: 167981

If you have the table:

CREATE TABLE person (
  id   NUMBER
       GENERATED ALWAYS AS IDENTITY
       CONSTRAINT person__id__pk PRIMARY KEY,
  name VARCHAR2(20)
       NOT NULL
);

Then rather than creating a trigger, instead, you could use a view:

CREATE VIEW person_view (
  id,
  name,
  samenamecount
) AS
SELECT id,
       name,
       COUNT(*) OVER (PARTITION BY name)
FROM   person;

Upvotes: 1

Related Questions