• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

oracle, database, 10, upon insert trigger

folks

I would like some assistance on to build the following trigger

if a record is inserted into table WO like so

insert into WO (projectid,type,priority)values('100','SAFE','1')

on the basis that the type is 'SAFE' then insert into another table like so

insert into SAFETY (projectid,ID,type,priority)SELECT WO.PROJECTID,
ID.NEXTVAL,WO.TYPE from WO

how can I build a trigger to perform this,a stored procedure wont work in this scenario

thanks in advance

r
0
rutgermons
Asked:
rutgermons
  • 2
1 Solution
 
jwahlCommented:
CREATE OR REPLACE TRIGGER wo_trg
AFTER INSERT ON wo
DECLARE
    v_new_id NUMBER;
BEGIN
    IF :NEW.type = 'SAFE' THEN
        SELECT ID.NEXTVAL
        INTO v_new_id
        FROM dual;
        --
        INSERT INTO SAFETY (projectid,ID,type,priority)
        VALUES (:NEW.PROJECTID, v_new_id, :NEW.TYPE);
    END IF;
END;

note: ID must be a sequence!
0
 
rutgermonsAuthor Commented:
j

got an error

ORA-04082: NEW OR OLD REFERENCES NOT allowed IN TABLE LEVEL TRIGGERS

could u advise?

tia
0
 
jwahlCommented:
sorry, please add FOR EACH ROW:

CREATE OR REPLACE TRIGGER wo_trg
AFTER INSERT ON wo
FOR EACH ROW
DECLARE
    v_new_id NUMBER;
BEGIN
 ....

0
 
Mark GeerlingsDatabase AdministratorCommented:
Yes, you need the "for each row" clause but this may cause another problem: the "mutating table" error *IF* there is a foreign key relationship between the two tables.  If you get that error, there is a mutli-step work-around available.  But if this application was not designed for Oracle, it may not take advantage of Oracle's referential integrity in the database, so you may not get the "mutating table" error.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now