Question

SQL- Trigger

I have two tables (below) I need to write a trigger that would delete everything for a pid from the Appt table if the pid is deleted from the patient table.

Create table Appt(
                pid numeric Foreign Key references patient(pid),
                ptname varchar(50) Foreign Key references patient(name),
                dob date Foreign Key references patient(dob),
                dr varchar(20),
                appdate date,
                apptime time,
);

and

Create table Patient(
                pid numeric primary key,
                name varchar(50),
                dob date,
                pdr varchar(20),
                phnum numeric(10),
              email varchar(50),
                addr varchar(50)
);

Answer 1

Below is the changes you need to done

ON DELETE CASCADE trigger is used to perform such action.

Create table Appt(
                pid numeric Foreign Key references patient(pid),
                ptname varchar(50) Foreign Key references patient(name) ON DELETE CASCADE,
                dob date Foreign Key references patient(dob),
                dr varchar(20),
                appdate date,
                apptime time,
);

and

Create table Patient(
                pid numeric primary key,
                name varchar(50),
                dob date,
                pdr varchar(20),
                phnum numeric(10),
              email varchar(50),
                addr varchar(50)

}