12.3 Triggers and stored procedures

Triggers and stored procedures are powerful tools in database management, allowing for automated actions and complex operations. They enhance data integrity, improve performance, and enable event-driven programming within the database itself.

These programmatic objects build upon the constraints discussed earlier in the chapter, providing more advanced ways to enforce business rules and maintain data consistency. They're essential for creating robust, efficient database systems.

Database Programming Objects

Programmatic Database Objects

Top images from around the web for Programmatic Database Objects

• 

  Lab 5. Relational Databases. Introduction to SQL [CS Open CourseWare] View original

  Is this image relevant?

• 

  Database systems View original

  Is this image relevant?

• 

  Chapter 13 Database Development Process – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Lab 5. Relational Databases. Introduction to SQL [CS Open CourseWare] View original

  Is this image relevant?

• 

  Database systems View original

  Is this image relevant?

1 of 3

Top images from around the web for Programmatic Database Objects

• 

  Lab 5. Relational Databases. Introduction to SQL [CS Open CourseWare] View original

  Is this image relevant?

• 

  Database systems View original

  Is this image relevant?

• 

  Chapter 13 Database Development Process – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Lab 5. Relational Databases. Introduction to SQL [CS Open CourseWare] View original

  Is this image relevant?

• 

  Database systems View original

  Is this image relevant?

1 of 3

• Triggers are special stored procedures automatically executed in response to certain events on a table or view in a database
• Stored procedures are subroutines available to applications that access a relational database management system (RDBMS)
  • Consist of pre-written SQL statements that are stored in the database and can be invoked by applications
• Procedural SQL extends standard SQL with procedural elements such as variables, conditions, loops, and exception handling
  • Allows for more complex database operations and application logic to be implemented directly in the database
• Database automation involves using programmatic objects like triggers and stored procedures to automate repetitive tasks, enforce business rules, and maintain data integrity

Benefits of Database Programming Objects

• Encapsulate complex database operations and business logic, making applications more maintainable and efficient
• Improve performance by reducing network traffic between the application and the database (stored procedures)
• Provide a layer of abstraction and security by limiting direct access to tables and views
• Enable event-driven programming and real-time data processing (triggers)

Trigger Types and Activation

Trigger Types

• Before triggers are executed before the associated SQL statement (INSERT, UPDATE, or DELETE) is executed
  • Can be used to validate or modify data before it is inserted, updated, or deleted
  • Example: Checking if a new product price is within an acceptable range before inserting it into the database
• After triggers are executed after the associated SQL statement is executed
  • Can be used to perform additional actions or calculations based on the affected rows
  • Example: Updating a customer's total_orders count after a new order is inserted
• Instead of triggers are executed instead of the associated SQL statement
  • Can be used to redirect the operation or perform alternative actions
  • Example: Redirecting an INSERT operation on a view to insert data into the underlying tables

Trigger Activation

• Triggers are activated automatically when the associated SQL statement (INSERT, UPDATE, or DELETE) is executed on the specified table or view
• Trigger activation can be conditional based on the values of the affected rows or other database states
  • Example: A trigger that only fires when a customer's credit limit is exceeded during an update
• Multiple triggers can be defined for the same table and event, with a specified execution order
• Triggers can be enabled, disabled, or dropped as needed to control their behavior

Trigger Use Cases

Data Validation and Integrity

• Triggers can enforce complex data validation rules that cannot be easily implemented using declarative constraints alone
  • Example: Ensuring that a new employee's salary is within the defined range for their job position
• Maintain data integrity by checking and updating related data across multiple tables
  • Example: Cascading updates or deletes to maintain referential integrity between a parent and child table

Auditing and Logging

• Triggers can automatically record changes made to sensitive or important data for auditing purposes
  • Example: Logging all updates to a customer's credit card information in a separate audit table
• Monitor and track database activity by capturing information about the user, timestamp, and nature of the changes
  • Example: Recording the user ID and timestamp for each record deleted from a financial transactions table

Event-Driven Actions

• Triggers enable event-driven programming within the database, allowing for real-time responses to data changes
  • Example: Sending an email notification to a customer when their order status is updated to "shipped"
• Automate complex business processes by triggering a series of actions based on specific database events
  • Example: Automatically generating an invoice and updating inventory levels when a new order is inserted