Ef crud insert data into another table while insert or update

Question

I have an appoinment table on which I did crud operations .

I have another table appoinmentaudit

if I insert or update appoinment the record should be created in appoinmentaudit

Here is source code and here are table

https://github.com/KalyanAllam/PatientPortal/

CREATE TABLE public.appointmentaudit (
	appid int4 NULL,
	notes varchar(200) NULL,
	appdate date NOT NULL,
	patientid int4 NULL,
	clinicianid int4 NULL,
	statusid int4 NULL
);

CREATE TABLE public.appointment (
	appid int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	notes varchar(200) NULL,
	appdate date NOT NULL,
	patientid int4 NULL,
	clinicianid int4 NULL,
	statusid int4 NULL,
	CONSTRAINT pk__app PRIMARY KEY (appid)
);


-- public.appointment foreign keys

ALTER TABLE public.appointment ADD CONSTRAINT "fk_dbo.appointment_dbo.appstatus_id" FOREIGN KEY (statusid) REFERENCES public.appstatus(id);
ALTER TABLE public.appointment ADD CONSTRAINT "fk_dbo.appointment_dbo.clinician_clinicianid" FOREIGN KEY (clinicianid) REFERENCES public.clinician(clinicianid);
ALTER TABLE public.appointment ADD CONSTRAINT "fk_dbo.appointment_dbo.patient_patientid" FOREIGN KEY (patientid) REFERENCES public.patient(patientid);

Answer 1

Hi @Dotnet Engineer

Agree with Johan Smarius, it is better to create a trigger to insert the new records into the appointmentaudit table.

You can try to directly create the insert trigger on the database side, using the following query statement:

create Trigger function:

CREATE OR REPLACE FUNCTION function_copy() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO
        public.appointmentaudit(appid,notes,appdate,patientid,clinicianid,statusid)
        VALUES(new.appid,new.notes,new.appdate,new.patientid,new.clinicianid,new.statusid);

           RETURN new;
END;
$BODY$
language plpgsql;

Create Trigger:

CREATE TRIGGER trig_copy
     AFTER INSERT ON public.appointment
     FOR EACH ROW
     EXECUTE PROCEDURE function_copy();

Since we are using AFTER INSERT, after insert the new item into the public.appointment table, it will trigger this function_copy and copy the new item to the public.appointmentaudit table.

Or, if you want to create the trigger via EF core, after executing the add-migration migration command, you can execute the create trigger sql command in the migration file, like this:

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"create trigger .....");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"drop trigger <triggerName>");
    }

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion

Answer 2

The most robust way to achieve this is by writing a trigger for your appointment table with the after insert, update setting. The db will then ensure that the other table is updated. You can of course also write this code in EF. You could use the DbContext.SavedChanges event for this. Given the code in your question, I get the impression that you don't work code first, so the first option would probably be the best option for you.