Having problems with sql triggers

Question

Hi there,

At the moment I am learning sql and begin to encounter problems with certain triggers. This is very basic but I do not know how to solve it.

The problem is this: I have two tables Person and BankAccountInfo. The table Personincludes personal information. as identification number, name, birth date, etc. . TheBankAccountInfo` table contains banking information for each person in the Person table.

Person (ID number (12) primary key
      , name varchar (60)
      , phone number 
      , ...)

BankAccountInfo (ID number (12) references Person
                 , bankaccount number (8) primary key
                 , ...)

What I want is when I remove a person, the row for this person also be removed from the table BankAccountInfo, DELETE Person WHERE ID = 123456.

The problem is that I do not know how the information goes into the trigger

CREATE 
TRIGGER DELETEINFO
BEFORE DELETE ON Person
BEGIN
DELETE BankAccountInfo where ID = ?????? <- What do i put here?
END;

Answer 1

Is this Oracle? You should simply create a foreign key with delete cascade option of the parent table and Oracle will take care of deleting the bank account info when the person is deleted:

CREATE TABLE BankAccountInfo (
ID number (12) references Person ON DELETE CASCADE,
bankaccount number (8) primary key, ...)

Otherwise, if you still want to know how to access row values in Oracle triggers, use :new and :old implicit cursors (for delete trigger, :old.ID should do the trick).

More about constraints here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_constraints.htm#i1006974

Answer 2

Pop has it right with the ON DELETE CASCADE syntax. However, if you do want to use a trigger the syntax is:

CREATE TRIGGER DELETEINFO
BEFORE DELETE ON Person
FOR EACH ROW
BEGIN
    DELETE BankAccountInfo where ID = :OLD.ID;
END;