Trigger question

[polaris_ws]

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

[slimdave]

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?

[jmodic]

Maybe something like that?

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...

[polaris_ws]

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.

[nandu]

Frank

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

- Nandu

[polaris_ws]

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

[JMac]

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