Page 109 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Stored procedures are a valuable feature in SQL that allows you to encapsulate complex database operations,
enforce security, and promote code reusability. They are widely used in database-driven applications and are an
important part of database management and development.

Creating Trigger, Creating Cursor, Using Cursor

Introduction to Triggers

Certainly! Triggers in the context of databases refer to special types of stored procedures that automatically execute
in response to specific events on a particular table or view. These events typically involve data manipulation
operations, such as INSERT, UPDATE, DELETE, or even certain schema-level events like CREATE, ALTER, or
DROP.
“A trigger is an automated code segment, acting as a procedure, that is executed in response to specific events
occurring in a table or view within a database. In contrast, a cursor is a control structure utilized in databases for
traversing through records. It’s noteworthy that a cursor can be declared and employed within the context of a
trigger “
Key Concepts:
1 Events
• INSERT: Triggered after a new row is added to the table.

• UPDATE: Triggered after one or more existing rows are modified.
• DELETE: Triggered after one or more rows are removed from the table.
2 Timing
• BEFORE Triggers: Executed before the triggering event, allowing modification of the data before it is
actually written to the database.
• AFTER Triggers: Executed after the triggering event has occurred.
3 Row-Level and Statement-Level Triggers
• Row-Level Triggers: Executed once for each row affected by the triggering event.

• Statement-Level Triggers: Executed once for each triggering event, regardless of the number of rows
affected.
Use Cases

1 Data Validation
• Ensure that certain conditions are met before allowing data changes.
2 Enforcing Business Rules
• Implement complex business logic or rules automatically.
3 Audit Trails
• Log changes made to a table for auditing purposes.

4 Cascade Operations
• Automatically perform additional operations on other tables when a specified event occurs.
5 Synchronization
• Keep multiple tables in sync by triggering actions in response to changes in one table.

Syntax (for an AFTER INSERT Trigger):
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name

96

CITS : IT&ITES - Computer software application - Lesson 18 - 36