0

Okay so I have been stuck on this for about 2 hours now and I still couldn't find a solution.

I have 2 database instances.

Site 1 has lets say, Table A id attrib1 foreignKey - (primary key of table B)

Site 2 has, Table B id attrib1

I want to create a trigger on delete of a record of Table A. Which basically checks if Site 2 Table B has a reference to that particular record. If it does have that record, I want to prevent the deletion from happening. So far I have come up with this,

CREATE OR REPLACE TRIGGER CHECK_DEALERSHIP_USAGE
BEFORE DELETE on TBL_CARDEALERSHIP
FOR each ROW
 declare
  rowcnt number;
  begin
  SELECT COUNT(DEALERSHIP_ID) INTO rowcnt 
  from TBL_SALESPEOPLE@SITE1
  where DEALERSHIP_ID = :NEW.DEALERSHIP_ID;
  if (rowcnt>0) THEN
     Raise_Application_Error (-20100, 'This dealership is used in the sales people table.');
  end if;
end;

Then I do this,

delete from TBL_CARDEALERSHIP
where DEALERSHIP_ID='83';

But it still deletes it, even thought I have a record in the database

Hades
  • 3,916
  • 3
  • 34
  • 74
  • 2
    In a delete trigger you need to reference the **old** record e.g. `where DEALERSHIP_ID = :OLD.DEALERSHIP_ID;` – DrabJay Sep 13 '14 at 16:08

1 Answers1

0

As DrabJay put it in the comments, the NEW record in a delete trigger is NULL, since it's after the deletion.

But, frankly, you're going at this wrong. This sort of thing should be done with foreign keys, not in a trigger.

CREATE TABLE TBL_CARDEALERSHIP (
    ... columns ...
    CONSTRAINT fk_salesppl FOREIGN KEY (dealership_id)
        REFERENCES tbl_salespeople (dealership_id)
);
eaolson
  • 14,717
  • 7
  • 43
  • 58