Trigger in Postgresql

Question

Need help in creating trigger in Postgresql , if a record is inserted or updated in appoinment create a record in appointmentaudit

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);

CREATE TABLE public.appointmentaudit (
	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__appaudit PRIMARY KEY (appid)
);


-- public.appointmentaudit foreign keys

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

Answer 1

create Trigger function:

SQL

Copy
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:

SQL

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

Answer 2

Please check this
https://learn.microsoft.com/en-us/answers/questions/1355461/ef-crud-insert-data-into-another-table-while-inser


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:

SQL

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