CODEWITHSUNDEEP
databaseoracle-database
user2787386
user2787386

Reputation: 303

Using a trigger to delete rows in a table in a seperate database. Oracle

I have two databases: A and B. I am trying to build a trigger in database B that will perform update, insert, and delete functions on a table in database A. my trigger so far looks like this:

DELIMITER //
CREATE OR REPLACE TRIGGER salesFragmentLesserTrigger
AFTER INSERT OR UPDATE OR DELETE ON sales
FOR EACH ROW
BEGIN

IF INSERTING THEN
    IF :new.sale_price < 5000000 THEN
        IF :new.sale_type = 'auction' THEN
        INSERT INTO LESSTHANFIVEMILLIONFRAGMENT@FIT5043A (sales_id,sales_date,sale_type,reserved_price,sale_price,deposit,balance,buyer_id,property_id)
            VALUES(:new.sales_id,:new.sales_date,:new.sale_type,:new.reserved_price,:new.sale_price,:new.deposit,:new.balance,:new.buyer_id,:new.property_id);
        END IF;
    END IF;
ELSIF DELETING THEN
DELETE FROM LESSTHANFIVEMILLIONFRAGMENT@FIT5043A
    WHERE [email protected]_id = :old.sales_id;
END IF;
END;
/

If I create the trigger with only the IF INSERTING block then it runs fine. It successfully updates the table in database A. When I add the ELSIF DELETING block however it compiles with the errors:

PL/SQL: ORA-04054: database link FIT5043A.SALES_ID does not exist
PL/SQL: SQL Statement ignored

Why does the insert statement succeed when the delete is asking for a database link? Is a database link absolutely necessary for what I am trying to do?

Upvotes: 0

Views: 842

Answers (1)

GolezTrol
GolezTrol

Reputation: 116180

Apparently it fails on the where clause and it thinks the alias name there is not FIT5043A but FIT5043A.SALES_ID. That is because database link names can contain dots.

I think the easiest solution is giving the table an alias, or omit the table altogether in the where clause.

Alias:

DELETE FROM LESSTHANFIVEMILLIONFRAGMENT@FIT5043A x
WHERE x.sales_id = :old.sales_id

Omitting:

DELETE FROM LESSTHANFIVEMILLIONFRAGMENT@FIT5043A
WHERE sales_id = :old.sales_id

Upvotes: 0

Related Questions