CS-GY 6083: Principles of Database Systems
1.0.0
1.0.0
  • Introduction
  • DBMS Basics
    • Introduction to DBMS
    • Why use a DBMS instead of a File System?
    • Levels of Abstraction
    • Instances and Schemas
  • Data Models
    • Introduction to Data Models
    • Database Languages
    • Database Design
  • DBMS Internals
    • Introduction to DBMS Internals
    • Storage Manager
    • Query Processor
    • Transaction Management
    • Database Users
    • Database Architecture
  • DBMS History
  • Some Popular Database Systems
  • OLTP, OLAP, and Data Mining
  • Databases vs. Information Retrieval
  • The Entity-Relationship Model - Details
    • Introduction
    • Cardinality Constraints
    • ER Diagram Components
    • ER Diagram to Relational Schema
    • Design Issues
  • The Relational Model - Details
    • Relations
    • Keys
    • Relational Query Languages
      • Relational Algebra
      • Relational Calculus
      • Relative Expressive Power
    • Relational Operators
  • SQL
    • Introduction to SQL
    • Domain Types in SQL
    • DDL Commands
      • Creating a Table
      • Alter and Drop
    • DML Commands
      • Basic Query Structure
      • Select
      • From
      • Where
      • Joins
      • Rename
      • String Operations
      • Ordering
      • Set Operations
      • Group By and Having
      • Nested Subqueries
      • Test for Empty Relations
      • Test for Duplicate Tuples
      • Derived Relations
      • With
      • Database Modification
    • Intermediate SQL
      • Joins Revisited
      • Views
      • Transactions
      • Integrity Constraints
      • More SQL Data Types and Schemas
        • Other Features
      • Authorization
    • Advanced SQL
      • Accessing SQL From a Programming Language
        • ODBC and JDBC
        • Embedded SQL
        • PHP
        • Some Security Issues
      • Accessing Metadata
      • Text Operations
        • Like
        • Contains
      • Cursors
      • Functions and Procedures
        • Procedural Constructs
        • External Language Routines
      • Triggers
      • Ranking
      • Windowing
      • OLAP
Powered by GitBook
On this page

Was this helpful?

  1. SQL
  2. Advanced SQL

Triggers

A trigger is a statement that is executed upon modification to the database.

We must specify:

  • under what condition a trigger must be executed

  • the action to be performed when the trigger is executed

For example, we can use a trigger to enforce referential integrity:

Say we have a relation called timeslot, and time_slot_id is not a primary key of timeslot, so we cannot create a foreign key constraint from another relation section to timeslot.

We can use a trigger on section and timeslot to enforce referential integrity:

create trigger timeslot_check1 after insert on section
referencing new row as nrow
for each row
when (nrow.time_slot_id not in (select time_slot_id from time_slot)) /* time_slot_id not present in time_slot */
begin
 rollback
end;
  • Triggering events can be insert, delete or update

  • Triggers on update can be restricted to specific attributes

    Ex. after update of takes on grade

  • Values of attributes before and after an update can be referenced as follows:

    • referencing old row as: for deletes and updates

    • referencing new row as: for inserts and updates

  • Triggers can be activated before an event, which can serve as extra constraints Ex. convert blank grades to null before they are inserted:

    create trigger setnull_trigger before update of takes
    referencing new row as nrow
    for each row
     when (nrow.grade = ‘ ‘)
     begin atomic
      set nrow.grade = null;
     end;

Statement-Level Triggers

Instead of executing a separate action for each affected row, a single action can be executed for all rows affected by a transaction:

  • Use for each statement instead of for each row

  • Use referencing old table or referencing new table to refer to temporary tables (called transition tables) containing the affected rows

This is more efficient when dealing with SQL statements that update a large number of rows/

PreviousExternal Language RoutinesNextRanking

Last updated 5 years ago

Was this helpful?