I am not really good when it comes in trigger trigger thing. I just want that if the admin update the StudentsEnrollmentRecord, it will trigger the SubjectSectionTeacher and it will save in StudentsEnrolledSubject.
can you guys help me solve this problem? especially in PostgreSQL Trigger
What I have tried:
CREATE OR REPLACE FUNCTION public.enrollmentrecord()
RETURNS trigger
LANGUAGE 'plpgsql'
VOLATILE NOT LEAKPROOF
AS $BODY$BEGIN
INSERT INTO StudentsEnrolledSubject (Students_Enrollment_Records,Subject_Section_Teacher)
SELECT a.id AS Students_Enrollment_Records, b.ID AS Subject_Section_Teacher
FROM StudentsEnrollmentRecords AS a INNER JOIN
SubjectSectionTeacher AS b ON a.School_Year = b.School_Year AND a.Education_Levels = b.Education_Levels AND a.Courses = b.Courses
AND a.Section = b.Sections
Where a.EducationLevel=b.Education_Levels AND a.Course=b.Courses AND a.Section=b.Sections
AND a.Student_Users=StudentsEnrolledSubject.Students_Enrollment_Records;
END;
$BODY$;
ALTER FUNCTION public.enrollmentrecord()
OWNER TO unidadb_admin;
And this is my Django model.py
class SubjectSectionTeacher(models.Model):
School_Year = models.ForeignKey(SchoolYear, related_name='+', on_delete=models.CASCADE,null=True)
Education_Levels = models.ForeignKey(EducationLevel, related_name='+', on_delete=models.CASCADE,blank=True)
Courses= models.ForeignKey(Course, related_name='+', on_delete=models.CASCADE,null=True,blank=True)
Sections= models.ForeignKey(Section, related_name='+', on_delete=models.CASCADE,null=True)
Subjects= models.ForeignKey(Subject, related_name='+', on_delete=models.CASCADE,null=True)
Employee_Users= models.ForeignKey(EmployeeUser, related_name='+', on_delete=models.CASCADE,null=True)
Start_Date = models.DateField(null=True,blank=True)
End_Date = models.DateField(null=True,blank=True)
Remarks = models.TextField(max_length=500)
class StudentsEnrollmentRecord(models.Model):
Student_Users = models.ForeignKey(StudentProfile, related_name='students', on_delete=models.CASCADE,null=True)
School_Year = models.ForeignKey(SchoolYear, related_name='+', on_delete=models.CASCADE, null=True, blank=True)
Courses = models.ForeignKey(Course, related_name='+', on_delete=models.CASCADE, null=True, blank=True)
Section = models.ForeignKey(Section, related_name='+', on_delete=models.CASCADE, null=True,blank=True)
Payment_Type = models.ForeignKey(PaymentType, related_name='+', on_delete=models.CASCADE, null=True)
Education_Levels = models.ForeignKey(EducationLevel, related_name='+', on_delete=models.CASCADE,blank=True,null=True)
Remarks = models.TextField(max_length=500,null=True,blank=True)
class StudentsEnrolledSubject(models.Model):
Students_Enrollment_Records = models.ForeignKey(StudentsEnrollmentRecord, related_name='+', on_delete=models.CASCADE,null=True)
Subject_Section_Teacher = models.ForeignKey(SubjectSectionTeacher, related_name='+', on_delete=models.CASCADE,null=True)