KTU DBMS Lab

9.sql

/*
AIM:
Create a trigger to print the following messages when insert, delete or update operation is performed on students table mentioned in the previous experiment for each tuple that is inserted, deleted or updated. Assume that semester grades of s1 and s2 are stored in students table along with Roll number and name of students

Operation	Action/Message
if name of student is updated	updated to sname (sname is the revised name of the
particular student)
roll, grd_s1 or grd_s2 is updated	inserting sname(sname is the entered name of the
particular student)
if a row/tuple is deleted	deleting sname(sname is the name of the deleted student)
*/

CREATE OR REPLACE FUNCTION students_trigger() RETURNS TRIGGER AS $$
BEGIN
    -- If a row is inserted
    IF TG_OP = 'INSERT' THEN
        -- Retrieve the name of the inserted student
        DECLARE
            student_name text;
        BEGIN
            SELECT name INTO student_name FROM students WHERE roll = NEW.roll;
            RAISE NOTICE 'inserting % (%, %)', student_name, NEW.roll, NEW.grd_s1, NEW.grd_s2;
            RETURN NEW;
        END;
    END IF;

    -- If a row is updated
    IF TG_OP = 'UPDATE' THEN
        -- Check if name, grd_s1, or grd_s2 is updated
        IF OLD.name <> NEW.name THEN
            RAISE NOTICE 'updated to %', NEW.name;
        END;
        IF OLD.grd_s1 <> NEW.grd_s1 OR OLD.grd_s2 <> NEW.grd_s2 THEN
            RAISE NOTICE 'updating % (%, %)', NEW.name, NEW.roll, NEW.grd_s1, NEW.grd_s2;
        END;
        RETURN NEW;
    END IF;

    -- If a row is deleted
    IF TG_OP = 'DELETE' THEN
        RAISE NOTICE 'deleting %', OLD.name;
        RETURN OLD;
    END IF;

    RETURN NULL;  -- Trigger should return NULL for other cases
END;
$$ LANGUAGE plpgsql;

-- Create the trigger
CREATE TRIGGER students_trigger
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW
EXECUTE FUNCTION students_trigger();