How to make a Trigger work like a Foreign Key?

Question

I need to make a Foreign Key using Triggers instead of just using an alter table like this:

 alter table WORKER
      add constraint WORKER_idDepartment_FK
      foreign key (idDepartment)
      references DEPARTMENT (idDepartment);

Can someone please help me?

"make a Foreign Key using Triggers" makes no sense. Can you explain more what you're trying to do? — D Stanley, Nov 10 '17 at 20:55
An example of what you've tried already would go a long way to helping us understand the actual problem you're trying to solve — Taegost, Nov 10 '17 at 20:58
@DStanley For example conditional FK. There are few scenarios — Lukasz Szozda, Nov 10 '17 at 20:59
@lad2025 Maybe i'll need this later. For now i just want to make a Trigger that acts like a foreing key. This is possible, right? But i dont know how to do it. And i dont find any examples — Gabriel Faria, Nov 10 '17 at 21:23
@GabrielFaria, this does not compute. My brain is having a compile error understanding what a trigger that acts like a foreign key even means. — Alan, Nov 10 '17 at 21:25
@Alan This is for college. Maybe I got it wrong (I missed some lessons). I'll ask my teacher directly. — Gabriel Faria, Nov 10 '17 at 21:34
@GabrielFaria Yes, it is possible (you will need INSERT/UPDATE trigger on child table and UPDATE/DELETE trigger parent table). Then you check in child trigger if records exists in parent and vice versa. If not just raise error to stop processing. — Lukasz Szozda, Nov 11 '17 at 05:46
Try to answer the question: "What does FK do?" And do the same in the trigger.(Joker - you can use exception) If you then have a problem, show what you did and ask. — Val Marinov, Nov 11 '17 at 10:49

score 6 · Accepted Answer · answered Nov 12 '17 at 08:43

There are rare situations when you give up standard FOREIGN KEY mechanism in favor of something else. For example, we had one database with really huge table where tens of FKs where created. Those foreign keys took hundreds of megabytes of disk space. By their nature they referenced tables with records count in 10000-100000 times less than in main table. Having switched to triggers to support data integrity we reduced db file size noticeable and get some overall performance increase.

Foreign key like this:

 alter table WORKER
      add constraint WORKER_idDepartment_FK
      foreign key (idDepartment)
      references DEPARTMENT (idDepartment);

Can be emulated with two triggers:

 CREATE TRIGGER aiu_worker FOR WORKER
   AFTER INSERT OR UPDATE
   POSITION 0
 AS BEGIN
   IF (NOT EXISTS(
      SELECT idDepartment 
      FROM DEPARTMENT 
      WHERE idDepartment = NEW.idDepartment)) THEN
    EXCEPTION my_exception 'There is no department with id=' ||
      NEW.idDepartment;
 END

 CREATE TRIGGER ad_department FOR DEPARTMENT
   AFTER DELETE
   POSITION 0
 AS BEGIN
   IF (EXISTS (
       SELECT idDepartment 
       FROM WORKER 
       WHERE idDepartment = OLD.idDepartment)) THEN
   BEGIN
     EXCEPTION my_exception 'Can not delete record';
   END
 END

You can easily adapt these triggers to implement CASCADE rules or support nullable fields.

How to make a Trigger work like a Foreign Key?

1 Answers1

Linked