09 - SQL Functions, Procedures, Triggers & Security

🎯 Learning Objectives

• Write SQL functions and procedures
• Create row-level and statement-level triggers
• Understand transition tables (OLD ROW/NEW ROW)
• Use GRANT and REVOKE for access control
• Explain SQL injection and how to prevent it

📋 Prerequisites

• 07 - Advanced SQL — Subqueries, set operations, views
• Understanding of database security concepts

📖 Core Content

9.1 Intuition: Making the Database Do More

• Functions: Computations that return a value
• Procedures: Sequences of operations that modify data
• Triggers: Automatic actions when data changes
• Security: Controlling who can access what These features push logic closer to the data, improving performance and consistency.

9.2 Functions vs. Procedures

Functions

sql
CREATE FUNCTION dept_instructor_count(dept_name VARCHAR(20))
RETURNS INTEGER AS 

$$
DECLARE
    count INTEGER;
BEGIN
    SELECT COUNT(*) INTO count
    FROM instructor
    WHERE instructor.dept_name = dept_name;
    RETURN count;
END;
$$
 LANGUAGE plpgsql;
-- Using the function
SELECT dept_instructor_count('Comp. Sci.');

Procedures

sql
CREATE PROCEDURE give_raise(
    inst_id VARCHAR(5),
    amount NUMERIC(8,2)
)
LANGUAGE SQL
AS 

$$
UPDATE instructor
    SET salary = salary + amount
    WHERE ID = inst_id;
$$
;
-- Invoking the procedure
CALL give_raise('10101', 5000);

9.3 Triggers

Row-Level Trigger

sql
CREATE TRIGGER credits_earned
AFTER UPDATE OF grade ON takes
REFERENCING NEW ROW AS nrow
            OLD ROW AS orow
FOR EACH ROW
WHEN (nrow.grade <> 'F' AND nrow.grade IS NOT NULL
      AND (orow.grade = 'F' OR orow.grade IS NULL))
BEGIN ATOMIC
    UPDATE student
    SET tot_cred = tot_cred + (
        SELECT credits
        FROM course
        WHERE course.course_id = nrow.course_id
    )
    WHERE student.ID = nrow.ID;
END;

Statement-Level Trigger

sql
CREATE TRIGGER log_grade_changes
AFTER UPDATE OF grade ON takes
REFERENCING OLD TABLE AS ot OLD
            NEW TABLE AS nt NEW
FOR EACH STATEMENT
INSERT INTO grade_audit_log (timestamp, affected_rows)
VALUES (CURRENT_TIMESTAMP, (SELECT COUNT(*) FROM nt));

Transition Tables

9.4 GRANT and REVOKE

GRANT

sql
GRANT SELECT ON instructor TO user1;
GRANT INSERT ON department TO user1, user2;
GRANT UPDATE (salary) ON instructor TO manager;  -- column-level!
GRANT ALL PRIVILEGES ON instructor TO user3;

REVOKE

sql
REVOKE SELECT ON instructor FROM user1;
REVOKE INSERT ON department FROM user1, user2;
REVOKE ALL PRIVILEGES ON instructor FROM user3;

Roles

sql
CREATE ROLE instructor_role;
GRANT SELECT, UPDATE ON instructor TO instructor_role;
GRANT instructor_role TO user1, user2;

9.5 SQL Injection

python
Run Inline
# VULNERABLE code
def get_user(username):
    query = f"SELECT * FROM users WHERE name = '{username}'"
    # If username = "'; DROP TABLE users; --"
    # The query becomes:
    # SELECT * FROM users WHERE name = ''; DROP TABLE users; --'

Prevention

1. Use parameterized queries (prepared statements):

python
Run Inline
# SAFE
cursor.execute("SELECT * FROM users WHERE name = %s", (username,))

1. Validate input: Reject unexpected characters
2. Limit privileges: Use read-only accounts where possible
3. Use stored procedures: Pre-compiled SQL with parameters

9.6 Worked Examples

Example 1: Trigger to prevent salary reduction

sql
CREATE TRIGGER prevent_salary_cut
BEFORE UPDATE OF salary ON instructor
FOR EACH ROW
WHEN (NEW.salary < OLD.salary)
BEGIN
    RAISE EXCEPTION 'Salary cannot be reduced';
END;

Example 2: Grant read-only access

sql
CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT read_only TO analyst1, analyst2;

📐 Key Formulas / Concepts

⚠️ Common Pitfalls

Pitfall 1: Triggers Causing Infinite Loops

Pitfall 2: SQL Injection via String Concatenation

Pitfall 3: Granting Too Many Privileges

📝 Practice Questions

Q1. What is the difference between a row-level trigger and a statement-level trigger?

Answer

• Row-level (FOR EACH ROW): Fires once for each row affected by the triggering statement. Can access old and new values of each row.
• Statement-level (FOR EACH STATEMENT): Fires once for the entire statement, regardless of how many rows are affected.

Example: An UPDATE affecting 100 rows fires the row trigger 100 times but the statement trigger once.

Q2. Write a GRANT statement that allows user1 to SELECT and UPDATE on the instructor table.

Answer

sql
GRANT SELECT, UPDATE ON instructor TO user1;
-- Or limit UPDATE to specific columns:
GRANT SELECT, UPDATE (name, dept_name) ON instructor TO user1;

Q3. What is SQL injection and how do you prevent it?

Answer

SQL injection: An attacker inserts malicious SQL code via user input that gets concatenated into a query. Example: Input ' OR 1=1; -- turns a login query into a query that returns all users.

Prevention:

1. Use parameterized queries (prepared statements) — always
2. Validate/sanitize user input
3. Use least-privilege database accounts
4. Use stored procedures with parameters

Q4. Write a function that returns the number of courses taught by a given instructor.

Answer

sql
CREATE FUNCTION course_count(instructor_id VARCHAR(5))
RETURNS INTEGER AS 

-- Usage: SELECT course_count('10101');

Q5. What is the purpose of a role in SQL security?

Answer

A role is a named collection of privileges that can be granted to multiple users. Benefits:

• Centralized management: Change the role's privileges → all users in that role are updated
• Consistency: No need to grant individual permissions
• Organization: Match roles to job functions (manager, analyst, clerk)

Q6. Write a trigger that logs whenever a row is deleted from the instructor table.

Answer

sql
CREATE TRIGGER log_instructor_delete
AFTER DELETE ON instructor
REFERENCING OLD ROW AS o
FOR EACH ROW
INSERT INTO audit_log (event_type, table_name, record_id, timestamp)
VALUES ('DELETE', 'instructor', o.ID, CURRENT_TIMESTAMP);

Q7. What's the difference between a SQL function and a SQL procedure?

Answer

Q8. What transition table references are available in different trigger events?

Answer

🔗 Cross-References

• Next Topic: 10 - Relational Algebra
• Previous Topic: 08 - SQL Joins
• Related: BSCS2003 (MAD 1) — Web application security
• Related: BSCS4024 (Networks) — Authentication, access control
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 4 (Intermediate SQL), Chapter 5 (Advanced SQL) Join Discord Previous08 - SQL JoinsNext10 - Relational Algebra