CODEWITHSUNDEEP
sql-serverentity-framework-coreasp.net-core-mvc
Maryam
Maryam

Reputation: 47

Insert Record to another table automatically

I have tables named additionalInsuranceLog, AdditionInsurance and Patient

additionalInsuranceLog .cs

public partial class additionalInsuranceLog 
    {
        public long PatientId { get; set; }
        public byte AdditionInsuranceId { get; set; }
        public DateTime ChangeDate { get; set; }

        public virtual AdditionInsurance AdditionInsurance { get; set; }
        public virtual Patient Patient { get; set; }
    }

AdditionInsurance.cs

public partial class AdditionInsurance
    {
        public AdditionInsurance()
        {
            Patient = new HashSet<Patient>();
        }

        public byte Id { get; set; }
        public string Name { get; set; }

        public virtual ICollection<Patient> Patient { get; set; }
    }

Patient.cs

public partial class Patient
    {
        public Patient()
        {
        }
        public long Id { get; set; }
        public string NationalCode { get; set; }
        public string FName { get; set; }
        public string LName { get; set; }        
        public string Education { get; set; } 
        public string CellPhone { get; set; }
        public byte? AdditionInsurance { get; set; }        
        public DateTime EditDate { get; set; }

        public AdditionInsurance AdditionInsuranceNavigation { get; set; }
    }

AdditionInsurance is filled in sql, I want that everytime a patient record get inserted or updated in db , if AdditionInsurance field get changed, a new record get inserted in additionalInsuranceLog table with date of today, I insert or update patient table in multi places in my web app, I want a best solution that not to code everywhere that I update or insert Patient record to insert a record in additionalInsuranceLog I want it to be done always, is there an optimal solution for it?

I'm using asp.net core 3 and entityframework core

Update I didn't know about triggers, should I or can I use triggers for my purpose? plz tell me details about how to do it?

Upvotes: 0

Views: 460

Answers (1)

Underhill Julian
Underhill Julian

Reputation: 29

Yes you can use a trigger.

Something like this.

CREATE TRIGGER AdditionInsurance_trg
ON AdditionInsurance 
AFTER UPDATE,INSERT
AS 
BEGIN
    INSERT INTO AdditionalInsuranceLog(
        PatientID,
        Field1New,
        Field1old)
    SELECT 
        i.PatientID,
        i.Field1New,
        d.Field1Old        
    FROM inserted i
    LEFT JOIN deleted d
        ON i.item_id = d.item_id
   END;

Upvotes: 1

Related Questions