• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • 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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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