SQL- Trigger I have two tables (below) I need to write a trigger that would delete everything...

Free

80.2K

Verified Solution

Question

Programming

SQL- Trigger
I have two tables (below) I need to write a trigger that woulddelete everything for a pid from the Appt table if the pid isdeleted 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 & Explanation Solved by verified expert

4.4 Ratings (606 Votes)

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)

}

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member