looking for some solutions? You are welcome.

SOLVED: cannot create INSTEAD OF triggers on table, trying to do a manual cascade delete

Jenny Lawrence:

I am trying to have my information stored from a whole database every time a repair job is deleted which means it's child car_problems also needs to be deleted but for some reason it won't let me do instead of delete on repair_job. Basically my main task for this trigger is to log everything that is deleted from repair_job. Here is my code:

 CREATE TABLE repair_job(repairId varchar(5) PRIMARY KEY, licenceNo 
                        varchar(7), 
                        contact varchar(25),time_in timestamp,
                        time_out timestamp, employeeId varchar(5), 
                        laborHrs number,
                         FOREIGN KEY(licenceNo) REFERENCES 
                         car(licenceNo),
                         FOREIGN KEY(contact) REFERENCES 
                         Customer(contact),
                         FOREIGN KEY(employeeId) REFERENCES 
                         mechanic(employeeId));

    CREATE TABLE car_problems(probId varchar(7), repairId varchar(5), 
                partName varchar(25), partPrice number,
                FOREIGN KEY(probId) REFERENCES problem(probId), 
                FOREIGN KEY(repairId) REFERENCES 
                repair_job(repairId));
    CREATE TABLE repairLog(repairId varchar(5), licenceNo varchar(7), 
                        model varchar(25), name varchar(25), 
                        address varchar(30), contact varchar(25),
                        probId varchar(7), probType varchar(30),
                        employeeId varchar(5), mechName varchar(25),
                        mechPhone varchar(10), hrlyRate number,
                        partName varchar(25), partPrice number,
                        time_in timestamp, time_out timestamp, 
                        laborHrs number);

    CREATE OR REPLACE TRIGGER logRepair
    INSTEAD OF DELETE
        ON repair_job
        FOR EACH ROW
        DECLARE



    mod varchar(25);
    addr varchar(30);
    custName varchar(25);
    mechName varchar(25);
    mechPhone varchar(10);
    hrlyRate number;
    partName varchar(25);
    partPrice number;
    probId varchar(7);
    probType varchar(30);

    CURSOR parts is
        SELECT car_problems.partName , car_problems.partPrice,
                car_problems.probId , problem.probType 
                FROM car_problems, problem
                WHERE car_problems.repairId = :old.repairId 
                AND car_problems.probId = problem.probId;


    BEGIN 

        select model into mod from car where contact = :old.contact;
        select name, address into custName, addr from Customer 
            where contact = :old.contact;
        select mechName, mechPhone, hrlyRate into mechName, mechPhone, 
              hrlyRate 
            from mechanic where employeeId = :old.employeeId;

        OPEN parts;
        LOOP
        FETCH parts into partName, partPrice, probId, probType;
            EXIT WHEN parts%notfound;
            INSERT INTO repairLog(repairId,licenceNo,
                            model, name, address,contact,
                            probId,probType, employeeId,
                            mechName,mechPhone,hrlyRate,
                            partName,partPrice, time_in, 
                            time_out, laborHrs) 

              VALUES(:old.repairId,:old.licenceNo,mod,custName,addr,
                                :old.contact,probId,
                            probType, :old.employeeId,
                            mechName, mechPhone, hrlyRate,


     partName,partPrice,:old.time_in,:old.time_out,:old.laborHrs);
        END LOOP;
        CLOSE parts;
    DELETE FROM car_problems where repairId=:old.repairId;
    DELETE FROM repair_job where repairId=:old.repairId;


    END;
    /
    SHOW ERRORS;



Posted in S.E.F
via StackOverflow & StackExchange Atomic Web Robots
Share:

No comments:

Recent