Table of Contents

1. Introduction
2. Why Use JSON in Relational Databases?
3. JSON Support Across SQL Dialects
4. Creating Tables with JSON Columns
5. Inserting JSON Data
6. Retrieving JSON Fields
7. Accessing Nested JSON Properties
8. Modifying JSON Data (JSON_SET, jsonb_set)
9. Querying JSON Conditions
10. Validating JSON Structure
11. JSON Creation Functions (JSON_OBJECT, json_build_object)
12. Aggregating JSON Data
13. Searching Within JSON Arrays
14. Indexing JSON Columns for Performance
15. Real-World Use Case: Flexible User Profiles
16. Real-World Use Case: Product Attributes and Variants
17. Pros and Cons of JSON in SQL
18. Best Practices for JSON Storage
19. JSON and Normalization — When to Avoid It
20. Summary and What’s Next

1. Introduction

JSON (JavaScript Object Notation) is widely used for handling semi-structured data. Modern relational databases like MySQL, PostgreSQL, and SQL Server now offer native JSON support, enabling you to combine the flexibility of NoSQL with the structure of SQL.

2. Why Use JSON in Relational Databases?

• Store flexible, dynamic data structures
• Avoid excessive normalization for optional or variable fields
• Work seamlessly with frontend APIs that consume or send JSON
• Support multi-valued or nested attributes

3. JSON Support Across SQL Dialects

4. Creating Tables with JSON Columns

MySQL:

sqlCopyEditCREATE TABLE users (
  id INT PRIMARY KEY,
  profile JSON
);

PostgreSQL:

sqlCopyEditCREATE TABLE users (
  id SERIAL PRIMARY KEY,
  profile JSONB
);

5. Inserting JSON Data

sqlCopyEditINSERT INTO users (id, profile)
VALUES (1, '{
  "name": "Alice",
  "age": 30,
  "hobbies": ["reading", "gaming"]
}');

Ensure valid JSON formatting — keys in double quotes, comma-separated.

6. Retrieving JSON Fields

MySQL:

sqlCopyEditSELECT profile->'$.name' AS name FROM users;

PostgreSQL:

sqlCopyEditSELECT profile->>'name' AS name FROM users;

• -> returns JSON object
• ->> returns text/string value

7. Accessing Nested JSON Properties

MySQL:

sqlCopyEditSELECT profile->'$.address.city' FROM users;

PostgreSQL:

sqlCopyEditSELECT profile->'address'->>'city' FROM users;

8. Modifying JSON Data

MySQL:

sqlCopyEditUPDATE users
SET profile = JSON_SET(profile, '$.age', 31)
WHERE id = 1;

PostgreSQL:

sqlCopyEditUPDATE users
SET profile = jsonb_set(profile, '{age}', '31', true)
WHERE id = 1;

9. Querying JSON Conditions

MySQL:

sqlCopyEditSELECT * FROM users
WHERE JSON_EXTRACT(profile, '$.age') > 25;

PostgreSQL:

sqlCopyEditSELECT * FROM users
WHERE (profile->>'age')::int > 25;

10. Validating JSON Structure

MySQL:

sqlCopyEditSELECT JSON_VALID(profile) FROM users;

PostgreSQL: Type casting to JSONB will automatically validate the content.

11. JSON Creation Functions

MySQL:

sqlCopyEditSELECT JSON_OBJECT('name', 'John', 'age', 25);

PostgreSQL:

sqlCopyEditSELECT jsonb_build_object('name', 'John', 'age', 25);

Also supports jsonb_build_array(), json_agg(), etc.

12. Aggregating JSON Data

MySQL:

sqlCopyEditSELECT JSON_ARRAYAGG(name) FROM users;

PostgreSQL:

sqlCopyEditSELECT json_agg(name) FROM users;

Great for building APIs that return a single JSON array.

13. Searching Within JSON Arrays

MySQL:

sqlCopyEditSELECT * FROM users
WHERE JSON_CONTAINS(profile->'$.hobbies', '["reading"]');

PostgreSQL:

sqlCopyEditSELECT * FROM users
WHERE 'reading' = ANY (jsonb_array_elements_text(profile->'hobbies'));

14. Indexing JSON Columns for Performance

PostgreSQL:

sqlCopyEditCREATE INDEX idx_profile_gin ON users USING GIN (profile);

MySQL:

sqlCopyEditCREATE INDEX idx_json_name ON users ((profile->>'$.name'));

Index only what you query often — not the entire JSON blob.

15. Real-World Use Case: Flexible User Profiles

jsonCopyEdit{
  "first_name": "Jane",
  "settings": {
    "theme": "dark",
    "notifications": true
  },
  "languages": ["English", "Hindi"]
}

Helps store optional or rarely changing user settings in one field.

16. Real-World Use Case: Product Attributes and Variants

jsonCopyEdit{
  "color": "blue",
  "size": "L",
  "tags": ["organic", "handmade"],
  "dimensions": {
    "height": 20,
    "width": 15
  }
}

Used in e-commerce platforms to handle customizable and dynamic fields without creating multiple columns.

17. Pros and Cons of JSON in SQL

✅ Pros:

• Schema flexibility
• Fast API development
• Easier integration with frontend
• Nested data support

❌ Cons:

• Harder to validate and constrain
• Joins and searches can be slower
• Limited cross-field querying
• Increases complexity when overused

18. Best Practices for JSON Storage

• Use JSON for semi-structured, optional, or nested data
• Normalize core business entities
• Index JSON expressions used in filters or joins
• Use CHECK (JSON_VALID(column)) if supported
• Avoid deeply nested structures in frequently queried data

19. JSON and Normalization — When to Avoid It

Avoid JSON when:

• You need strict relational integrity
• Data must be filtered/joined frequently
• You rely heavily on GROUP BY, JOIN, or ORDER BY on inner fields
• Field-specific indexing is critical for performance

20. Summary and What’s Next

SQL support for JSON allows you to combine structured and semi-structured data, enabling more flexibility without leaving the relational world. Use it wisely to store evolving fields, user preferences, or API-centric objects.

Table of Contents

1. Introduction
2. Why Use JSON in SQL Databases?
3. Storing JSON Data in Tables
4. JSON Data Types and Columns
5. Basic JSON Syntax
6. Inserting JSON Values
7. Retrieving JSON Data
8. Accessing JSON Fields (->, ->>, JSON_EXTRACT)
9. Modifying JSON Fields
10. Querying with JSON Conditions
11. Validating JSON with IS JSON, JSON_VALID()
12. Using JSON_OBJECT, JSON_ARRAY
13. JSON Aggregation Functions
14. Searching Inside JSON Arrays
15. Indexing JSON Columns
16. Real-World Example: Storing User Profiles
17. Real-World Example: Product Attributes
18. Limitations and Considerations
19. Best Practices for JSON in SQL
20. Summary and What’s Next

1. Introduction

JSON (JavaScript Object Notation) has become a popular format for storing semi-structured data. Many modern SQL databases now support native JSON operations, allowing developers to store, query, and manipulate JSON just like regular tabular data.

2. Why Use JSON in SQL Databases?

• Store flexible, dynamic schemas
• Embed nested or hierarchical data structures
• Exchange data easily with APIs and applications
• Avoid joining too many lookup tables for small, infrequent fields

3. Storing JSON Data in Tables

You can store JSON in:

• TEXT or VARCHAR columns (basic usage)
• JSON or JSONB (native types in PostgreSQL, MySQL 5.7+)

Example:

sqlCopyEditCREATE TABLE users (
  id INT PRIMARY KEY,
  profile JSON
);

4. JSON Data Types and Columns

5. Basic JSON Syntax

jsonCopyEdit{
  "name": "Alice",
  "age": 30,
  "hobbies": ["reading", "hiking"],
  "address": { "city": "Delhi", "zip": "110001" }
}

6. Inserting JSON Values

sqlCopyEditINSERT INTO users (id, profile)
VALUES (1, '{
  "name": "Alice",
  "age": 30,
  "hobbies": ["reading", "hiking"]
}');

Ensure valid JSON format and escape special characters if needed.

7. Retrieving JSON Data

sqlCopyEditSELECT profile FROM users WHERE id = 1;

Returns JSON string. To extract values, use access operators or functions.

8. Accessing JSON Fields

MySQL:

sqlCopyEdit-- Get field
SELECT profile->'$.name' FROM users;

-- Get scalar (text)
SELECT profile->>'$.name' FROM users;

PostgreSQL:

sqlCopyEdit-- Get JSON object
SELECT profile->'name' FROM users;

-- Get text
SELECT profile->>'name' FROM users;

9. Modifying JSON Fields

MySQL:

sqlCopyEditUPDATE users
SET profile = JSON_SET(profile, '$.age', 31)
WHERE id = 1;

PostgreSQL:

sqlCopyEditUPDATE users
SET profile = jsonb_set(profile, '{age}', '31', true)
WHERE id = 1;

10. Querying with JSON Conditions

MySQL:

sqlCopyEditSELECT * FROM users
WHERE JSON_EXTRACT(profile, '$.age') > 25;

PostgreSQL:

sqlCopyEditSELECT * FROM users
WHERE profile->>'age'::int > 25;

11. Validating JSON with IS JSON, JSON_VALID()

MySQL:

sqlCopyEditSELECT JSON_VALID('{"a":1}') AS is_valid; -- returns 1

You can also enforce JSON validity at the schema level with CHECK (JSON_VALID(column)).

12. Using JSON_OBJECT, JSON_ARRAY

MySQL:

sqlCopyEditSELECT JSON_OBJECT('name', 'Alice', 'age', 30);
SELECT JSON_ARRAY('reading', 'hiking');

PostgreSQL:

sqlCopyEditSELECT jsonb_build_object('name', 'Alice', 'age', 30);
SELECT jsonb_build_array('reading', 'hiking');

13. JSON Aggregation Functions

MySQL:

sqlCopyEditSELECT JSON_ARRAYAGG(name) FROM users;

PostgreSQL:

sqlCopyEditSELECT json_agg(name) FROM users;

Useful for returning arrays of values in a single JSON result.

14. Searching Inside JSON Arrays

MySQL:

sqlCopyEditSELECT * FROM users
WHERE JSON_CONTAINS(profile->'$.hobbies', '["reading"]');

PostgreSQL:

sqlCopyEditSELECT * FROM users
WHERE 'reading' = ANY (jsonb_array_elements_text(profile->'hobbies'));

15. Indexing JSON Columns

• MySQL: Functional indexes on extracted fields

sqlCopyEditCREATE INDEX idx_name ON users ((profile->>'$.name'));

• PostgreSQL: GIN indexes on JSONB for containment searches

sqlCopyEditCREATE INDEX idx_profile ON users USING GIN (profile jsonb_path_ops);

16. Real-World Example: Storing User Profiles

sqlCopyEdit{
  "first_name": "John",
  "last_name": "Doe",
  "settings": {
    "theme": "dark",
    "notifications": true
  }
}

This eliminates the need for multiple settings columns in the table.

17. Real-World Example: Product Attributes

jsonCopyEdit{
  "color": "red",
  "size": "L",
  "features": ["waterproof", "lightweight"]
}

Used in e-commerce systems for products with dynamic attributes.

18. Limitations and Considerations

• JSON data is not normalized — harder to join
• Slower to query than structured columns unless indexed
• Constraints are harder to enforce (e.g., mandatory fields)
• Can lead to schema drift if not managed carefully

19. Best Practices for JSON in SQL

• Use JSON for flexible or semi-structured data
• Always validate JSON inputs
• Combine JSON with relational columns when appropriate
• Create indexes on frequently queried fields
• Avoid overloading logic inside JSON — keep it readable and minimal

20. Summary and What’s Next

JSON support in SQL lets you handle semi-structured, flexible data inside a relational schema. With native functions and indexing, it’s now easier than ever to integrate JSON workflows into traditional SQL applications.

Table of Contents

1. Introduction
2. Why Error Handling Matters in SQL
3. Types of Errors in SQL
4. Syntax Errors vs Runtime Errors
5. Understanding Transaction Errors
6. Rollbacks for Safe Error Recovery
7. Error Handling in SQL Scripts
8. Handling Errors in Stored Procedures
9. Using DECLARE HANDLER in MySQL
10. Handling Errors in PostgreSQL (EXCEPTION)
11. SQL Server’s TRY...CATCH Syntax
12. Identifying Errors with SQLSTATE and Error Codes
13. Logging Errors for Auditing and Debugging
14. Real-World Use Case: Bank Transfer with Error Handling
15. Preventing Errors Through Constraints
16. Retrying, Skipping, or Continuing After Errors
17. Common Error Patterns and Troubleshooting
18. Best Practices for Error Handling
19. Database-Specific Features for Error Management
20. Summary and What’s Next

1. Introduction

Error handling is a critical aspect of SQL programming. Whether you’re writing a script, stored procedure, or a high-throughput application backend, managing exceptions and transaction failures properly can protect your data and improve system reliability.

2. Why Error Handling Matters in SQL

• Maintains data consistency and integrity
• Prevents partial updates or corrupt states
• Helps diagnose and log application-level issues
• Enables clean recovery or user-friendly messages
• Supports robust and maintainable code

3. Types of Errors in SQL

4. Syntax Errors vs Runtime Errors

• Syntax errors are caught during query parsing.
• Runtime errors occur during execution and can be caught or handled in stored routines.

Example:

sqlCopyEditSELECT 1 / 0; -- Runtime error: division by zero

5. Understanding Transaction Errors

Many SQL statements are executed inside transactions. If one fails, the entire transaction must be rolled back to maintain data integrity.

6. Rollbacks for Safe Error Recovery

sqlCopyEditSTART TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Error occurs here
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

ROLLBACK; -- Ensures nothing is committed

7. Error Handling in SQL Scripts

Basic SQL scripts usually stop execution when an error is encountered. To control this, use database-specific constructs like:

• DECLARE HANDLER in MySQL
• BEGIN ... EXCEPTION in PostgreSQL
• TRY...CATCH in SQL Server

8. Handling Errors in Stored Procedures

MySQL:

sqlCopyEditDECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;
  INSERT INTO error_log (message) VALUES ('Something failed');
END;

PostgreSQL:

sqlCopyEditBEGIN
  -- statements
EXCEPTION
  WHEN OTHERS THEN
    RAISE WARNING 'Error: %', SQLERRM;
END;

9. Using DECLARE HANDLER in MySQL

sqlCopyEditDECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  SET @has_error = TRUE;
END;

Handler types:

• CONTINUE: continue execution after error
• EXIT: exit the block
• UNDO: rarely supported

10. Handling Errors in PostgreSQL (EXCEPTION)

sqlCopyEditDO $$
BEGIN
  -- Attempt risky logic
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Divide by zero error';
  WHEN OTHERS THEN
    RAISE WARNING 'Unhandled error: %', SQLERRM;
END $$;

PostgreSQL provides GET STACKED DIAGNOSTICS for deeper debugging.

11. SQL Server’s TRY...CATCH Syntax

sqlCopyEditBEGIN TRY
  -- risky SQL
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
  ROLLBACK TRANSACTION;
END CATCH;

Also access: ERROR_NUMBER(), ERROR_LINE(), ERROR_SEVERITY()

12. Identifying Errors with SQLSTATE and Error Codes

Useful for creating custom error logic based on type.

13. Logging Errors for Auditing and Debugging

sqlCopyEditCREATE TABLE error_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  message TEXT
);

-- Log error
INSERT INTO error_log (message) VALUES ('Update failed at step 3');

14. Real-World Use Case: Bank Transfer with Error Handling

sqlCopyEditBEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    INSERT INTO error_log (message) VALUES ('Transfer failed');
  END;

  START TRANSACTION;

  UPDATE accounts SET balance = balance - 1000 WHERE id = 101;
  UPDATE accounts SET balance = balance + 1000 WHERE id = 202;

  COMMIT;
END;

15. Preventing Errors Through Constraints

Define constraints in schema:

• CHECK (quantity >= 0)
• NOT NULL to enforce required fields
• UNIQUE to prevent duplicates
• FOREIGN KEY to enforce relational integrity

16. Retrying, Skipping, or Continuing After Errors

Use CONTINUE HANDLER or write loop logic to skip faulty records during ETL.

sqlCopyEditDECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET @skipped_rows = @skipped_rows + 1;

17. Common Error Patterns and Troubleshooting

18. Best Practices for Error Handling

• Always wrap complex DML in transactions
• Log meaningful context with every failure
• Use specific error handling when possible
• Test edge cases (nulls, duplicates, failures)
• Avoid silent failure paths unless explicitly desired

19. Database-Specific Features for Error Management

20. Summary and What’s Next

Error handling in SQL empowers you to build robust, resilient, and fault-tolerant systems. Whether using handlers, exception blocks, or structured logs, it’s essential for modern database operations.

Table of Contents

1. Introduction
2. What Are Stored Procedures and Functions?
3. Benefits of Using Stored Routines
4. Stored Procedures vs Functions: Key Differences
5. Syntax for Creating Stored Procedures
6. Executing a Stored Procedure
7. Parameters in Stored Procedures (IN, OUT, INOUT)
8. Control Flow in Stored Procedures
9. Creating and Using Functions
10. Input Parameters in Functions
11. Return Statements and Data Types in Functions
12. Using Stored Functions in SQL Queries
13. Real-World Use Case: Generating Reports
14. Real-World Use Case: Reusable Business Rules
15. Error Handling in Stored Procedures
16. Security and Permissions
17. Performance Considerations
18. Modifying and Dropping Stored Routines
19. Best Practices for Writing Stored Routines
20. Summary and What’s Next

1. Introduction

In SQL, stored procedures and functions are blocks of SQL code that are stored in the database and executed when needed. They allow you to encapsulate complex operations into reusable and manageable routines.

2. What Are Stored Procedures and Functions?

• A Stored Procedure is a set of SQL statements that performs a task. It may or may not return a value.
• A Stored Function is similar but must return a value and is often used inside expressions.

Both are often referred to collectively as stored routines.

3. Benefits of Using Stored Routines

• Reusability: Write once, use many times
• Maintainability: Centralize business logic
• Security: Hide implementation details
• Performance: Reduce client-server communication
• Modularity: Break down large tasks into manageable pieces

4. Stored Procedures vs Functions: Key Differences

5. Syntax for Creating Stored Procedures

MySQL:

sqlCopyEditDELIMITER //

CREATE PROCEDURE GetEmployeeById(IN emp_id INT)
BEGIN
  SELECT * FROM employees WHERE id = emp_id;
END //

DELIMITER ;

PostgreSQL:

sqlCopyEditCREATE OR REPLACE PROCEDURE GetEmployeeById(IN emp_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT * FROM employees WHERE id = emp_id;
END;
$$;

6. Executing a Stored Procedure

MySQL:

sqlCopyEditCALL GetEmployeeById(3);

PostgreSQL:

sqlCopyEditCALL GetEmployeeById(3);

7. Parameters in Stored Procedures (IN, OUT, INOUT)

• IN: Input only
• OUT: Output only
• INOUT: Both input and output

Example:

sqlCopyEditCREATE PROCEDURE UpdateSalary(IN emp_id INT, IN raise DECIMAL(10,2), OUT new_salary DECIMAL(10,2))
BEGIN
  UPDATE employees SET salary = salary + raise WHERE id = emp_id;
  SELECT salary INTO new_salary FROM employees WHERE id = emp_id;
END;

8. Control Flow in Stored Procedures

You can use IF, CASE, LOOP, WHILE, and REPEAT:

sqlCopyEditIF status = 'active' THEN
  UPDATE users SET last_login = NOW() WHERE id = user_id;
END IF;

Also includes exception handling via DECLARE ... HANDLER.

9. Creating and Using Functions

sqlCopyEditCREATE FUNCTION GetTotalOrders(cust_id INT)
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE total INT;
  SELECT COUNT(*) INTO total FROM orders WHERE customer_id = cust_id;
  RETURN total;
END;

10. Input Parameters in Functions

Functions can take multiple IN parameters:

sqlCopyEditCREATE FUNCTION AddNumbers(a INT, b INT)
RETURNS INT
RETURN a + b;

11. Return Statements and Data Types in Functions

• Use RETURNS type to define return type
• Must include a RETURN statement
• Only one value can be returned directly

sqlCopyEditRETURN CONCAT(first_name, ' ', last_name);

12. Using Stored Functions in SQL Queries

sqlCopyEditSELECT name, GetTotalOrders(id) AS total_orders
FROM customers;

Stored functions can be used in:

• SELECT
• WHERE
• ORDER BY
• GROUP BY

13. Real-World Use Case: Generating Reports

Procedure:

sqlCopyEditCREATE PROCEDURE MonthlySalesReport(IN report_month DATE)
BEGIN
  SELECT product_id, SUM(quantity), SUM(total)
  FROM sales
  WHERE MONTH(sale_date) = MONTH(report_month)
  GROUP BY product_id;
END;

Used for dashboards or automated report generation.

14. Real-World Use Case: Reusable Business Rules

Function:

sqlCopyEditCREATE FUNCTION IsEligibleForDiscount(purchase_total DECIMAL(10,2))
RETURNS BOOLEAN
RETURN purchase_total > 500;

Used inside SELECT, WHERE, or other procedures.

15. Error Handling in Stored Procedures

Use DECLARE HANDLER to catch and handle errors:

sqlCopyEditDECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  -- Log error or set default values
END;

This improves stability in mission-critical operations.

16. Security and Permissions

• Routines can be granted EXECUTE privileges separately
• Users can call a procedure without knowing the internal query
• You can restrict DML operations inside routines with role control

17. Performance Considerations

• Reduces round-trips between client and server
• Use compiled execution plans
• Avoid excessive nesting or recursion
• Reuse prepared logic for heavy tasks

18. Modifying and Dropping Stored Routines

Modify:

sqlCopyEditDROP PROCEDURE IF EXISTS UpdateSalary;
CREATE PROCEDURE UpdateSalary(...) ...

Drop:

sqlCopyEditDROP PROCEDURE procedure_name;
DROP FUNCTION function_name;

Use SHOW PROCEDURE STATUS or SHOW CREATE FUNCTION to inspect definitions.

19. Best Practices for Writing Stored Routines

• Use clear, consistent naming (sp_, fn_, etc.)
• Comment thoroughly for maintainability
• Keep logic modular and focused
• Handle exceptions explicitly
• Separate input validation from core logic

20. Summary and What’s Next

Stored procedures and functions are vital for modular, secure, and high-performance SQL development. They help standardize business logic, automate tasks, and improve performance through reusable logic blocks.

Table of Contents

1. Introduction
2. What Is a Trigger in SQL?
3. Why Use Triggers?
4. Types of Triggers
5. Syntax of a Trigger
6. BEFORE vs AFTER Triggers
7. INSERT, UPDATE, and DELETE Triggers
8. Trigger Events: Row-Level vs Statement-Level
9. Creating a Simple Trigger
10. Using NEW and OLD Pseudorecords
11. Real-World Example: Logging Table Changes
12. Real-World Example: Enforcing Business Logic
13. Preventing Invalid Updates
14. Using Triggers for Auditing
15. Nested and Recursive Triggers
16. Managing Trigger Order and Dependencies
17. Performance Considerations
18. Best Practices for Triggers
19. Viewing, Modifying, and Dropping Triggers
20. Summary and What’s Next

1. Introduction

SQL triggers allow developers to automate actions in response to events on a table. Whether you’re auditing changes, enforcing complex rules, or cascading updates, triggers help enforce consistent behavior directly in the database.

2. What Is a Trigger in SQL?

A trigger is a stored set of instructions that is automatically executed (or “triggered”) when a specific database event occurs, such as an INSERT, UPDATE, or DELETE.

3. Why Use Triggers?

• Automatically enforce business rules
• Log changes for auditing
• Validate or transform data
• Synchronize or replicate changes
• Prevent invalid operations
• Reduce redundant application code

4. Types of Triggers

5. Syntax of a Trigger (General Form)

sqlCopyEditCREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
  -- trigger logic
END;

Varies slightly depending on DBMS.

6. BEFORE vs AFTER Triggers

• BEFORE: Ideal for validations or modifications to data before committing
• AFTER: Ideal for logging or notifications after the change is committed

7. INSERT, UPDATE, and DELETE Triggers

8. Trigger Events: Row-Level vs Statement-Level

• Row-Level Triggers: Executes once per row affected
• Statement-Level Triggers: Executes once per statement regardless of row count

PostgreSQL supports both via FOR EACH ROW and FOR EACH STATEMENT.

9. Creating a Simple Trigger

Example: Add timestamp on new row (MySQL):

sqlCopyEditCREATE TRIGGER set_created_at
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

This automatically sets created_at on insert.

10. Using NEW and OLD Pseudorecords

• NEW.column_name refers to the new value in INSERT/UPDATE
• OLD.column_name refers to the previous value in UPDATE/DELETE

sqlCopyEditCREATE TRIGGER audit_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_at)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());

11. Real-World Example: Logging Table Changes

sqlCopyEditCREATE TRIGGER log_user_activity
AFTER UPDATE ON users
FOR EACH ROW
INSERT INTO user_logs (user_id, action, timestamp)
VALUES (OLD.id, 'Profile Updated', NOW());

12. Real-World Example: Enforcing Business Logic

sqlCopyEditCREATE TRIGGER prevent_negative_stock
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.stock < 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Stock cannot be negative';
  END IF;
END;

13. Preventing Invalid Updates

sqlCopyEditCREATE TRIGGER block_salary_cut
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < OLD.salary THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Salary decrease not allowed';
  END IF;
END;

14. Using Triggers for Auditing

sqlCopyEditCREATE TRIGGER audit_delete_orders
AFTER DELETE ON orders
FOR EACH ROW
INSERT INTO deleted_orders (order_id, deleted_at)
VALUES (OLD.id, NOW());

Useful for tracking deletions or soft-deletion implementation.

15. Nested and Recursive Triggers

• Nested triggers: Trigger fired by another trigger
• Recursive triggers: A trigger firing itself directly or indirectly
  • May be disabled by default or require special config

Use SET NOCOUNT ON or control flags to avoid infinite loops.

16. Managing Trigger Order and Dependencies

Some databases (e.g., Oracle, SQL Server) allow you to define trigger execution order.

In general:

• Use modular triggers per use case
• Avoid creating large monolithic triggers

17. Performance Considerations

• Triggers add overhead — especially on bulk operations
• Use only where essential
• Avoid complex logic that introduces latency
• Beware of hidden side-effects (e.g., unexpected updates)

18. Best Practices for Triggers

• Name triggers clearly: e.g., before_insert_users, audit_delete_customers
• Keep them short and specific
• Use AFTER triggers for non-critical tasks
• Always test thoroughly before deploying
• Document trigger purpose and logic for future maintainers

19. Viewing, Modifying, and Dropping Triggers

View existing triggers:

sqlCopyEditSHOW TRIGGERS;              -- MySQL  
SELECT * FROM pg_trigger;   -- PostgreSQL  

Drop a trigger:

sqlCopyEditDROP TRIGGER trigger_name ON table_name; -- MySQL  
DROP TRIGGER trigger_name;               -- PostgreSQL  

20. Summary and What’s Next

Triggers allow automatic execution of logic in response to table events, making them ideal for auditing, validation, logging, and enforcing business rules. Use them wisely to ensure consistency without adding hidden complexity.