Obtaining an inserted value within a trigger

[TomBarrand]

Hi,

I am using Oracle 9i and have written the following trigger. How do I obtain one of the values that has been inserted into the table so that I can use the value in the trigger.

The statement below is an example of the data that is inserted into the table. The value that I want to get within the trigger is LNGPERSONIDCNT for the inserted record.

INSERT INTO TEST.PERSONSGROUP (LNGPERSONIDCNT, LNGGROUPIDCNT, BISSERVING, INTGROUPPOSITION, UNIQUEID)
VALUES (13047, 814, -1, 1, 16720);

I have detailed the code that I have written below but it errors on the SELECT statement.

Many thanks

Tom

DECLARE
key INTEGER;
userID NUMBER;
BEGIN

IF INSERTING THEN
key := :NEW.LNGGROUPIDCNT;
ELSE
key := :OLD.LNGGROUPIDCNT;
END IF;

SELECT LNGPERSONIDCNT INTO userID FROM TEST.PERSONSGROUP WHERE LNGGROUPIDCNT = key;

INSERT INTO TEST.direct_process
(
record_id,
table_key,
event_type,
event_time,
table_name
)
VALUES
(
TEST.seq_log_record_id.nextval,
('PK_INTGROUPIDCNT=' || key),
6,
CURRENT_DATE,
'VIEW_GRP'
);

INSERT INTO TEST.direct_process
(
record_id,
table_key,
event_type,
event_time,
table_name
)
VALUES
(
TEST.seq_log_record_id.nextval,
('PK_LNGPERSONIDCNT=' || userID ),
6,
CURRENT_DATE,
'VIEW_USR'
);


END;

 

[Dagon]

The values of the variables being inserted can be obtained using the ":new" variables e.g. :new.table_key, :new.event_type.

 

[Dagon]

Sorry, only read the first line of your message and then noticed you were already using :new in the trigger. What error do you get ? Is it when creating the trigger or executing it ?

 

[slaing]

Hi Tom,


2 things spring to mind

1) Are you getting a 'mutating trigger' error ?
It looks like your SELECT is trying to read from the same table that the trigger is modifying.
Make it an AFTER trigger to avoid that.

2) Could the SELECT INTO return more than 1 row ?
That would give an error as well.
An OPEN / FETCH / CLOSE might get round that.


Steve