ganjaam
ganjaam

Reputation: 1286

encountering "Warning: Trigger created with compilation errors." while creating multiple triggers

I was trying to create multiple triggers in an sql file. And the code is as follows:

CREATE OR REPLACE TRIGGER trigger_selling_price_change
AFTER INSERT OR UPDATE ON SellingPrice
FOR EACH ROW
BEGIN
    dbms_output.put_line('price of phone updated');
    INSERT INTO LogSellingPriceChange
    VALUES(sysdate, phone_id, :old.price, :new.price);
END;
/


CREATE OR REPLACE TRIGGER trigger_purchase_cost_change
AFTER INSERT OR UPDATE ON PurchaseCost
FOR EACH ROW
BEGIN
    dbms_output.put_line('purchase cost updated');
    INSERT INTO LogPurchaseCostChange
    VALUES(sysdate, phone_id, :old.cost, :new.cost);
END;
/

CREATE OR REPLACE TRIGGER trigger_inventory_addition
AFTER INSERT ON Inventory
FOR EACH ROW
BEGIN
    dbms_output.put_line('enlisted new phones in database');
    INSERT INTO LogInventoryAddition
    VALUES(sysdate, phone_id, :old.quantity, :new.quantity);
END;
/

But when I execute it in pl/sql I get Warning: Trigger created with compilation errors. three times.

Upon executing show_errors I get the following output:

Errors for TRIGGER TRIGGER_INVENTORY_ADDITION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/2      PL/SQL: SQL Statement ignored
4/18     PL/SQL: ORA-00984: column not allowed here

Declaration of the tables included in the query are as follows:

CREATE TABLE Inventory(
    id number,
    phone_id number,
    quantity number,
    PRIMARY KEY (id),
    FOREIGN KEY (phone_id) REFERENCES Phone(id)
);

CREATE TABLE PurchaseCost(
    id number,
    phone_id number,
    cost number,
    PRIMARY KEY(id),
    FOREIGN KEY (phone_id) REFERENCES Phone(id)
);

CREATE TABLE SellingPrice(
    id number,
    phone_id number,
    price number,
    PRIMARY KEY(id),
    FOREIGN KEY (phone_id) REFERENCES Phone(id)
);

CREATE TABLE LogSellingPriceChange(
    change_date date,
    phone_id number,
    old_price number,
    new_price number,
    FOREIGN KEY(phone_id) REFERENCES Phone(id)
);

CREATE TABLE LogPurchaseCostChange(
    change_date date,
    phone_id number,
    old_price number,
    new_price number,
    FOREIGN KEY(phone_id) REFERENCES Phone(id)
);

CREATE TABLE LogInventoryAddition(
    addition_date date,
    phone_id number,
    old_quantity number,
    new_quantity number,
    FOREIGN KEY(phone_id) REFERENCES Phone(id)
);

I executed the file for creating triggers after successfully creating the tables. However, I did not insert any values.

Upvotes: 1

Views: 85

Answers (1)

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

Column not allowed error because you are trying to access the phone_id column directly without the psudo key :new or :old,

CREATE OR REPLACE TRIGGER trigger_selling_price_change
AFTER INSERT OR UPDATE ON SellingPrice
FOR EACH ROW
BEGIN
    dbms_output.put_line('price of phone updated');
    INSERT INTO LogSellingPriceChange
    VALUES(sysdate, :new.phone_id, :old.price, :new.price);
END;
/

You need to change in other triggers as well.

Upvotes: 1

Related Questions