Triggers
The ‘trigger’ is a statement that the DBMS system executes automatically as a side effect of a modification made to the database.
To design a trigger mechanism, the following two requirements are used.
- Specifying when the trigger has to be executed.
- Specifying the actions to be taken, when the trigger is executed.
Consider the following relation ‘student’.
R1: STUD-2 R2: COURSE-1
SNo SName DNo Tot-Credits CNo Title DNo Credits
121 xyz 21 80 61 DBMS 21 10
123 pqr 21 70 62 DAA 21 10
126 mnp 22 68 64 SE 22 8
128 abc 22 38 65 CO 22 8
130 jkl 23 48 67 OS 23 10
————————————————— ————————————-
Consider the following relation:
R7 : TAKES
Stud_id Course_id Sec_id Semester Year Grade
121 61 1 4 2021 A
123 62 1 4 2021 B
126 64 1 4 2021 B
128 65 2 4 2021 F
130 67 2 4 2021 C
Create Trigger Credits_Earned after update of takes on (grade)
referencing new row as “newrow”
referencing old row as “oldrow”
for each row
when newrow.grade <> ‘F’ and newrow.grade is not null and (oldrow.grade = ‘F’ or oldrow.grade is null) begin atomic update student-2 set tot_credits = tot_credits + (select credits from course-1 where course.course-1.CNo = newrow.course.id ) where student.id = newrow.id ; end ;
Note: Here, the ‘referencing old row as’ clause may be used to create a variable storing the old value of an updated or deleted row.
The ‘referencing new row as’ clause can be used with updates in addition to inserts.
In other words, ‘Triggers’ define actions to be executed automatically when certain events occur and corresponding conditions are satisfied.
