Scribd Logo
Upload

Welcome to Scribd!

  • 82 views

    Part A Aim:: 1. The Triggering Event

    Uploaded by

    Shahrukh Pinjari
    The document discusses database triggers, which are stored programs that execute automatically in response to events like data modifications or logon/logoff. Triggers have three main parts - a triggering event, an optional condition, and an action. Triggers can be used for maintaining data integrity, auditing, replication, and more. The document provides the syntax for DML triggers and examples of writing triggers to log changes to employee data and track the number of employees per department.

    Copyright:

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

    Part A Aim:: 1. The Triggering Event

    Uploaded by

    Shahrukh Pinjari
    82 views3 pages
    The document discusses database triggers, which are stored programs that execute automatically in response to events like data modifications or logon/logoff. Triggers have three main parts - a triggering event, an optional condition, and an action. Triggers can be used for maintaining data integrity, auditing, replication, and more. The document provides the syntax for DML triggers and examples of writing triggers to log changes to employee data and track the number of employees per department.

    Original Description:

    dbms nmims 4th sem experiment

    Original Title

    Exp 11

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses database triggers, which are stored programs that execute automatically in response to events like data modifications or logon/logoff. Triggers have three main parts - a triggering event, an optional condition, and an action. Triggers can be used for maintaining data integrity, auditing, replication, and more. The document provides the syntax for DML triggers and examples of writing triggers to log changes to employee data and track the number of employees per department.

    Copyright:

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

    Part A Aim:: 1. The Triggering Event

    Uploaded by

    Shahrukh Pinjari
    The document discusses database triggers, which are stored programs that execute automatically in response to events like data modifications or logon/logoff. Triggers have three main parts - a triggering event, an optional condition, and an action. Triggers can be used for maintaining data integrity, auditing, replication, and more. The document provides the syntax for DML triggers and examples of writing triggers to log changes to employee data and track the number of employees per department.

    Copyright:

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

    Part A

    Aim:
    i) To understand triggers
    ii) To implement triggers in SQL

    Prerequisite: SQL Server, SQL, Programming Language concepts.

    Outcome: Understanding and implementing Trigger.

    Theory:
    Triggers are stored programs, which are automatically executed or fired when some events occur.
    Or
    A Trigger is a code that associated with insert, update or delete operations. The code is executed
    automatically whenever the associated query is executed on a table. Triggers can be useful to maintain
    integrity in database. Triggers are, in fact, written to be executed in response to any of the following
    events:
     DML Trigger: A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
     DDL Trigger: A database definition (DDL) statement (CREATE, ALTER, or DROP).
     Logon Trigger: A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or
    SHUTDOWN).

    Parts of a Trigger
    1. The Triggering Event
    A triggering event or statement is the SQL statement, database event, or user event that causes a
    trigger to fire. A triggering event can be one or more of the following:
     An INSERT, UPDATE, or DELETE statement on a specific table (or view, in
    some cases)
     A CREATE, ALTER, or DROP statement on any schema object
     A database startup or instance shutdown
     A specific error message or any error message
     A user logon or logoff

    2. Trigger Condition
    A trigger restriction specifies a Boolean expression that must be true for the trigger to fire. The
    trigger action is not run if the trigger restriction evaluates to false or unknown. In the example,
    the trigger restriction is:
    new.parts_on_hand < new.reorder_point
    Consequently, the trigger does not fire unless the number of available parts is less than a present
    reorder amount.

    3. Trigger Action
    A trigger action is the procedure (PL/SQL block, Java program, or C callout) that contains the
    SQL statements and code to be run when the following events occur:
     A triggering statement is issued.
     The trigger restriction evaluates to true.
    Benefits of Triggers
    Triggers can be written for the following purposes:
     Generating some derived column values automatically
     Enforcing referential integrity
     Event logging and storing information on table access
     Auditing
     Synchronous replication of tables
     Imposing security authorizations
     Preventing invalid transactions

    Syntax: (DML Trigger)

    CREATE TRIGGER tr_trigger_name


    ON { table | view }
    { FOR | AFTER | INSTEAD OF }
    { [ INSERT| UPDATE | DELETE ] } //Event
    [ WITH APPEND ]
    [ NOT FOR REPLICATION ]
    AS
    BEGIN
    Declaration-statements
    WHEN (condition) //Condition
    Executable-statements //Action
    Exception-handling-statements
    END;

     CREATE [OR ALTER] TRIGGER trigger_name: Creates or replaces an existing trigger with
    the trigger_name.
     {FOR | AFTER | INSTEAD OF}: This specifies when the trigger would be executed. The
    INSTEAD OF clause is used for creating trigger on a view.
     {INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation.
     [ON table_name]: This specifies the name of the table associated with the trigger.
     WHEN (condition): This provides a condition for rows for which the trigger would fire. This
    clause is valid only for row level triggers.

    Procedure:
    1. Formulate the query for given problem.
    2. Write the SQL query with proper input.
    3. Execute the query.

    Practice Exercise:
    1. Write a trigger to insert an entry in Emp_audit table on insertion of each row in EMP table. (On
    Insert Trigger)
    Entry should be in following format:
    New employee with Emp_no = XXXX is added on [current date] 18-10-2016 2:40 PM.
    2. Write a trigger to insert an entry in Emp_audit table on deletion of a row in EMP table. (On
    Delete trigger)
    Entry should be in following format:
    An Existing employee with Emp_no = XXXX is deleted on [current date] 18-10-2016 2:40
    PM.
    3. Write a trigger to insert an entry in Emp_audit table on updation of salary of any employee in
    EMP table. (On Update trigger)
    Entry should be in following format:
    Salary of employee with Emp_no = XXXX is increased/decreased on [current date] 18-10-
    2016 2:40 PM, and now salary is XXXX instead of YYYY.
    4. Insert a new column No_of_emp in department table which will contain number of employees
    in each department. Write a trigger to update the value of No_of_Emp column in department
    table based on insertion or deletion in EMP table.(eg. If employee is added then No_of_Emp
    should be incremented).
    Instructions:
    1. Write and execute the query in Oracle SQL server.
    2. Paste the snapshot of the output in input & output section.

    Part B
    Code and Output:
    Perform the operation and paste the running code here.

    Observation & Learning:


    Write your observation and learning after performing the task.

    Conclusion:
    Write statement of conclusion here.

    Questions:
    1. What is the difference between Trigger and Stored Procedure?
    2. Define various types of triggers?
    3. How many triggers you can have on a table?
    4. Explain trigger classes i.e. instead of and after trigger.
    5. Differentiate between row level trigger and statement level trigger?

    You might also like