Trigger question

I create a trigger to automatically insert '-9999' into a column.
The code is like this:

CREATE OR REPLACE TRIGGER PERSONNEL_BSTATEMENT
BEFORE INSERT ON L_NWS_PERSONNEL
BEGIN
INSERT INTO L_NWS_PERSONNEL (GREMPID_KEY)
VALUES ('-9999');
END PERSONNEL_BSTATEMENT;

When I load data to fire this trigger, the error message comes out like this:

ORA-00036 Maximum number of recursive SQL level (50) exceeded.

I go to Oracle 9i online documentation. It only says to delete the trigger. Please help to tell me the reason. Thanks.

Frank

you understand why this is happening, right? The trigger detects an insert into the table -> the trigger inserts another row into the table -> the trigger detects the new row -> the trigger inserts another row etc.

This is presumably not what you wanted, so what do you really want to happen?

Maybe something like that?

Code:

---

CREATE OR REPLACE TRIGGER PERSONNEL_BSTATEMENT
BEFORE INSERT ON L_NWS_PERSONNEL
for each row
BEGIN
  :NEW.grempid_key := '-9999';
END PERSONNEL_BSTATEMENT;

---

P.S. As slimdave said, it is not very clear what you actualy want to do, so the above is only my guessing...

jmodic

Thanks for your input. What I want to do is, I only want to insert one row of -9999 into that table. Then I load data into Fact_table, it will work as a PK for the FK in Dimension_table.

Frank

Do u need to insert -9999 every time a new record is inserted into the table?

- Nandu

Nandu:

No. I only need to insert -9999 into the table before inserting or updating the table. I only need to insert one row into the table. Thanks.

Frank

Not sure what you mean. Insert the key value ONCE into the table - into 1 single row?
So what about subsequent rows?