Scribd Logo
Upload

Welcome to Scribd!

  • 0 views

    Triggers

    Uploaded by

    Deepa Deepa
    Triggers concept of dbms

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Triggers

    Uploaded by

    Deepa Deepa
    0 views8 pages
    Triggers concept of dbms

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Triggers concept of dbms

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    0 views8 pages

    Triggers

    Uploaded by

    Deepa Deepa
    Triggers concept of dbms

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 8

    Triggers

    Row-level trigger:
    CREATE TABLE customers (
    ID INT PRIMARY KEY,
    NAME VARCHAR(50),
    AGE INT,
    ADDRESS VARCHAR(100),
    SALARY DECIMAL(10, 2)
    );

    INSERT INTO customers (ID, NAME, AGE, ADDRESS, SALARY)


    VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00);

    INSERT INTO customers (ID, NAME, AGE, ADDRESS, SALARY)


    VALUES (2, 'Khilan', 25, 'Delhi', 1500.00);

    INSERT INTO customers (ID, NAME, AGE, ADDRESS, SALARY)


    VALUES (3, 'Kaushik', 23, 'Kota', 2000.00);

    INSERT INTO customers (ID, NAME, AGE, ADDRESS, SALARY)


    VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00);
    INSERT INTO customers (ID, NAME, AGE, ADDRESS, SALARY)
    VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00);

    INSERT INTO customers (ID, NAME, AGE, ADDRESS, SALARY)


    VALUES (6, 'Komal', 22, 'MP', 4500.00);

    CREATE OR REPLACE TRIGGER display_salary_changes


    BEFORE DELETE OR INSERT OR UPDATE ON customers
    FOR EACH ROW
    WHEN (NEW.ID > 0)
    DECLARE
    sal_diff number;
    BEGIN
    sal_diff := :NEW.salary - :OLD.salary;
    dbms_output.put_line('Old salary: ' || :OLD.salary);
    dbms_output.put_line('New salary: ' || :NEW.salary);
    dbms_output.put_line('Salary difference: ' || sal_diff);
    END;
    /
    INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
    VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

    UPDATE customers
    SET salary = salary + 500
    WHERE id = 2;

    EX02:
    SQL> CREATE TABLE students (
    2 student_id NUMBER PRIMARY KEY,
    3 name VARCHAR2(50),
    4 major VARCHAR2(50),
    5 gpa NUMBER(3, 2)
    6 );

    Table created.

    SQL> CREATE TABLE student_audit (


    2 action_type VARCHAR2(10
    3 student_id NUMBER,
    4 name VARCHAR2(50),
    5 major VARCHAR2(50),
    6 gpa NUMBER(3, 2)
    7 );

    Table created.

    SQL> CREATE OR REPLACE TRIGGER before_insert_student


    2 BEFORE INSERT ON students
    3 FOR EACH ROW
    4 BEGIN
    5 IF :NEW.gpa < 0 OR :NEW.gpa > 10.0 THEN
    6 RAISE_APPLICATION_ERROR(-20001, 'GPA must be between 0
    and 10.0');
    7 END IF;
    8 END;
    9 /

    Trigger created.

    SQL> CREATE OR REPLACE TRIGGER after_insert_student


    2 AFTER INSERT ON students
    3 FOR EACH ROW
    4 BEGIN
    5 INSERT INTO student_audit (action_type, student_id, name, major, gpa)
    6 VALUES
    ('INSERT', :NEW.student_id, :NEW.name, :NEW.major, :NEW.gpa);
    7 END;
    8 /

    Trigger created.

    SQL> CREATE OR REPLACE TRIGGER before_update_student


    2 BEFORE UPDATE ON students
    3 FOR EACH ROW
    4 BEGIN
    5 IF :NEW.gpa < 0 OR :NEW.gpa > 10.0 THEN
    6 RAISE_APPLICATION_ERROR(-20002, 'GPA must be between 0
    and 10.0');
    7 END IF;
    8 END;
    9 /

    Trigger created.

    SQL> CREATE OR REPLACE TRIGGER after_update_student


    2 AFTER UPDATE ON students
    3 FOR EACH ROW
    4 BEGIN
    5 INSERT INTO student_audit (action_type, student_id, name, major, gpa)
    6 VALUES
    ('UPDATE', :NEW.student_id, :NEW.name, :NEW.major, :NEW.gpa);
    7 END;
    8 /

    Trigger created.

    SQL> CREATE OR REPLACE TRIGGER after_delete_student


    2 AFTER DELETE ON students
    3 FOR EACH ROW
    4 BEGIN
    5 INSERT INTO student_audit (action_type, student_id, name, major, gpa)
    6 VALUES
    ('DELETE', :OLD.student_id, :OLD.name, :OLD.major, :OLD.gpa);
    7 END;
    8 /

    Trigger created.

    SQL> INSERT INTO students (student_id, name, major, gpa) VALUES (1,
    'Alice', 'Computer Science', 8.8);

    1 row created.

    SQL> INSERT INTO students (student_id, name, major, gpa) VALUES (2,
    'Bob', 'Mathematics',7.6);
    1 row created.

    SQL> INSERT INTO students (student_id, name, major, gpa) VALUES (3,
    'Charlie', 'Physics', 8.9);

    1 row created.

    SQL> INSERT INTO students (student_id, name, major, gpa) VALUES (4,
    'Diana', 'Chemistry', 6.7);

    1 row created.

    SQL> INSERT INTO students (student_id, name, major, gpa) VALUES (5,
    'Evan', 'Biology', 7.5);

    1 row created.

    SQL> SELECT * FROM student_audit;

    ACTION_TYP STUDENT_ID NAME MAJOR


    GPA
    INSERT 1 Alice Computer Science
    8.8

    INSERT 2 Bob Mathematics


    7.6

    INSERT 3 Charlie Physics


    8.9
    NSERT 4 Diana Chemistry
    6.7

    INSERT 5 Evan Biology


    7.5

    CREATE OR REPLACE TRIGGER


    auto_generate_student_id
    BEFORE INSERT ON students
    FOR EACH ROW
    BEGIN
    IF :NEW.student_id IS NULL THEN
    SELECT NVL(MAX(student_id), 0) + 1
    INTO :NEW.student_id FROM students;
    END IF;
    END;
    /

    Trigger created.

    INSERT INTO students (name, major, gpa) VALUES ('Eve',


    'Biology', 6.6);

    You might also like