Introduction to Triggers and Cursor.
Introduction to Triggers and Cursor.
Creating Trigger, Creating Cursor, Using Cursor
Embedded Files
Introduction to Triggers
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:
Key Concepts:
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.
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.
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
Use Cases
Data Validation:
Ensure that certain conditions are met before allowing data changes.
Enforcing Business Rules:
Implement complex business logic or rules automatically.
Audit Trails:
Log changes made to a table for auditing purposes.
Cascade Operations:
Automatically perform additional operations on other tables when a specified event occurs.
Synchronization:
Keep multiple tables in sync by triggering actions in response to changes in one table.
Syntax (for an AFTER INSERT Trigger):
Syntax (for an AFTER INSERT Trigger):
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
-- Trigger logic here
END;
trigger_name: The name you assign to the trigger.
AFTER INSERT ON table_name: Specifies the timing and event that trigger the execution.
FOR EACH ROW: Indicates that the trigger will be executed once for each row affected by the triggering event.
BEGIN...END: The block where you define the logic of the trigger.
Example:
Example:
Let's consider a simple example where we want to create an audit trail for an employees table:
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (employee_id, action, timestamp)
VALUES (NEW.employee_id, 'INSERT', NOW());
END;
In this example, every time a new row is inserted into the employees table, the trigger logs the action into an employee_audit table with details like the employee ID, the action performed (INSERT), and the timestamp.
Triggers are powerful tools, but they should be used with caution to avoid unintended consequences and to ensure they do not negatively impact database performance.
Introduction to Cursor
Introduction to Cursor
A cursor in the context of databases is a programming construct or a database object that enables the traversal and manipulation of records in a result set. It provides a mechanism for iterating over a set of rows returned by a SQL query. Cursors are often used in procedural languages, such as PL/SQL or T-SQL, to perform operations on a row-by-row basis.
There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors.
1.Implicit Cursors, often referred to as the Default Cursors of SQL Server, are automatically assigned by SQL Server when users execute Data Manipulation Language (DML) operations. These cursors are generated by the system without explicit declaration by the user.
2.explicit cursors:-Users create explicit cursors when needed. These cursors are specifically crafted by users for the purpose of retrieving data from a table in a row-by-row manner.
How To Create Explicit Cursor?
How To Create Explicit Cursor?
Declare Cursor Object
Declare Cursor Object
Syntax:
Syntax:
DECLARE cursor_name CURSOR FOR
SELECT * FROM table_name
Query:
DECLARE s1 CURSOR FOR
SELECT * FROM studDetails
2. Open Cursor Connection
2. Open Cursor Connection
Syntax:
OPEN cursor_connection
Query:
OPEN s1
3.Close cursor connection
3.Close cursor connection
Syntax:
CLOSE cursor_name
Query:
CLOSE s1
4.Deallocate cursor memory
Syntax:
DEALLOCATE cursor_name
Query:
DEALLOCATE s1
How To Create an Implicit Cursor?
How To Create an Implicit Cursor?
Syntax (for a Simple Cursor):
Syntax (for a Simple Cursor):
-- Cursor Declaration
DECLARE cursor_name CURSOR FOR
SELECT column1, column2
FROM table_name
WHERE condition;
-- Cursor Opening
OPEN cursor_name;
-- Cursor Fetching and Processing
FETCH NEXT FROM cursor_name INTO variable1, variable2;
-- Loop through the result set
WHILE @@FETCH_STATUS = 0
BEGIN
-- Process the current row (variable1, variable2)
-- Fetch the next row
FETCH NEXT FROM cursor_name INTO variable1, variable2;
END;
-- Cursor Closing
CLOSE cursor_name;
Example:
Example:
Suppose we want to process each employee's name and salary from an employees table:
DECLARE emp_cursor CURSOR FOR
SELECT employee_name, salary
FROM employees;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @employee_name, @salary;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Process the current employee (e.g., print or update)
PRINT CONCAT('Employee: ', @employee_name, ', Salary: ', @salary);
-- Fetch the next employee
FETCH NEXT FROM emp_cursor INTO @employee_name, @salary;
END;
CLOSE emp_cursor;
In this example, the cursor iterates through the result set of the query, fetching the employee name and salary for each row and then processing them within a loop. Cursors provide a flexible mechanism for handling individual rows in a result set within procedural code.
Page updated
Google Sites
Report abuse