Advanced Triggers & Views: Performance & Security Impact

In This Chapter

Detailed Study of Triggers
Creating and Managing Views
Triggers & Views: Performance and Security

1 Detailed Study of Triggers

Building on the trigger basics from the Intermediate course, let's examine how triggers work internally and explore advanced patterns.

Trigger Execution Order

When you run an INSERT, here's the exact sequence:

BEFORE INSERT trigger fires — you can modify NEW values
Row is inserted into the table
Constraints checked (PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE)
Indexes updated
AFTER INSERT trigger fires — you can read NEW but not modify it

Key insight: BEFORE triggers can change the data being written (SET NEW.col = value). AFTER triggers cannot — the row is already committed. Use BEFORE for validation/normalization, AFTER for logging/syncing.

Multi-Statement Trigger

SQL

DELIMITER //
CREATE TRIGGER trg_order_complete
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 1. Update inventory
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;

    -- 2. Log the sale
    INSERT INTO sales_log (order_id, product_id, qty, logged_at)
    VALUES (NEW.id, NEW.product_id, NEW.quantity, NOW());

    -- 3. Update customer stats
    UPDATE customers
    SET total_orders = total_orders + 1,
        last_order_date = NOW()
    WHERE id = NEW.customer_id;
END //
DELIMITER ;

Trigger chains: If a trigger on table A updates table B, and table B has its own trigger, that trigger fires too. This can create cascading chains that are difficult to debug. Keep triggers simple and document them thoroughly.

Key Takeaways

BEFORE triggers can modify NEW values; AFTER triggers cannot
Triggers execute in order: BEFORE → row write → constraints → indexes → AFTER
Multi-statement triggers can update inventory, log events, and sync tables automatically
Be cautious of trigger chains — they create invisible side effects

2 Creating and Managing Views

Top

Beyond the basics, let's explore advanced view patterns, updatable views, and view management strategies.

Updatable Views

Some views allow INSERT, UPDATE, and DELETE through them — if they meet certain conditions:

SQL

-- This view is updatable (single table, no aggregation)
CREATE VIEW active_employees AS
SELECT id, name, department, salary
FROM employees
WHERE is_active = TRUE;

-- You can UPDATE through the view!
UPDATE active_employees
SET salary = salary * 1.05
WHERE department = 'Engineering';

WITH CHECK OPTION

Prevents inserting/updating rows that would disappear from the view:

SQL

CREATE VIEW senior_staff AS
SELECT * FROM employees
WHERE salary >= 80000
WITH CHECK OPTION;

-- This fails! Setting salary to 50K would remove the row from the view
UPDATE senior_staff SET salary = 50000 WHERE id = 1;
-- ERROR: CHECK OPTION failed

Conditions for Updatable Views

Allowed	Not Allowed
Single base table	JOINs (usually)
No DISTINCT, GROUP BY, HAVING	Aggregate functions (SUM, COUNT)
No subqueries in SELECT	UNION, INTERSECT, EXCEPT
All NOT NULL columns included	Derived/calculated columns

Key Takeaways

Simple single-table views can be updatable (INSERT/UPDATE/DELETE through them)
WITH CHECK OPTION prevents writes that would make rows disappear from the view
Views with JOINs, aggregates, or DISTINCT are generally not updatable

3 Performance and Security Impact

Top

Trigger Performance

Impact	Details	Mitigation
Slower writes	Every INSERT/UPDATE/DELETE runs the trigger code	Keep trigger logic minimal; avoid queries inside triggers
Cascading overhead	Trigger A updates table B, which fires trigger B	Avoid trigger chains; document dependencies
Hidden I/O	An INSERT on table A might write to 3 other tables	Monitor total writes with `EXPLAIN`
Lock contention	Trigger holds locks on all tables it touches	Keep triggers fast to reduce lock duration

Views for Security

Views are a powerful security tool — they let you expose only specific data to specific users:

SQL

-- Create a view that hides salary data
CREATE VIEW public_employee_directory AS
SELECT name, department, hire_date
FROM employees;
-- Salary, email, phone are NOT exposed

-- Grant access to the view, not the table
GRANT SELECT ON public_employee_directory TO 'intern_user'@'%';
-- intern_user can query the view but NOT the employees table

View Performance

Views don't cache results. A view on a complex 5-table JOIN runs that JOIN every time you query the view. For read-heavy analytics, consider: (1) Materialized views (PostgreSQL) that store results and refresh periodically, (2) Summary tables that you rebuild with a scheduled procedure, or (3) Proper indexing on the underlying tables.

Key Takeaways

Triggers add overhead to every write operation — keep them lightweight
Views are excellent for row/column-level security (hide sensitive columns)
Views don't cache — they re-execute the underlying query each time
Use GRANT on views (not tables) to control access for different user roles

What's next? Advanced Normalization & Denormalization — learn when to intentionally break normalization rules for performance.

All Chapters in SQL Advanced Course

Previous ChapterStored Procedures & Functions Next Chapter Advanced Normalization

Triggers & Views

In This Chapter

1 Detailed Study of Triggers

Trigger Execution Order

Multi-Statement Trigger

Key Takeaways

2 Creating and Managing Views

Updatable Views

WITH CHECK OPTION

Conditions for Updatable Views

Key Takeaways

3 Performance and Security Impact

Trigger Performance

Views for Security

View Performance

Key Takeaways

All Chapters in SQL Advanced Course

Courses

Services

Support

Connect