LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
MS SQL Server

User-Defined Functions, Triggers and Dynamic SQL

User-Defined Functions (UDFs) help us reuse database calculations. Triggers run automatically when specified database events happen. Dynamic SQL builds and executes SQL statements at runtime.

User-Defined Functions, Triggers and Dynamic SQL

Quick Answer: User-Defined Functions (UDFs) help us reuse database calculations. Triggers run automatically when specified database events happen. Dynamic SQL builds and executes SQL statements at runtime.

1. Why Do We Need User-Defined Functions, Triggers and Dynamic SQL?

Imagine Priya manages a grocery shop with 5,000 customer orders. Every order needs calculations, tracking and different reports.

Some orders need discounts. Every new order must also create an audit record. Priya sometimes needs reports from different order tables.

How can we handle these requirements without repeating database code?

The problem without these features

We could write the same calculation in every query. We could also repeat audit and reporting logic.

Example 1: Repeated grocery order calculations

Assume OrderItems contains Price and Quantity columns.

SELECT Product, Price * Quantity AS Total
FROM OrderItems;

SELECT Product, Price * Quantity * 0.90
    AS DiscountedTotal
FROM OrderItems;

Both queries work, but the design creates maintenance problems.

  1. Calculations are repeated across multiple queries.
  2. Audit logic must be added wherever orders are inserted.
  3. Different reporting requirements can produce many SQL statements.

Now imagine maintaining these queries across hundreds of business reports.

We need reusable calculations, automatic database actions and flexible queries.

The solution: Three SQL Server features

User-Defined Function = a reusable database routine that returns a value or table.

Trigger = a database routine that runs automatically after or instead of specified events.

Dynamic SQL = SQL statements built and executed while the application runs.

Each feature solves a different problem. Let's understand their responsibilities.

Animation 1: How three SQL Server features work

Priya uses three database features for different grocery shop requirements.

Priya Grocery shop owner
uses database services
SQL Server GroceryDB database
provides three features
User Function Price 100 × quantity 2
Trigger Audit new order 101
Dynamic SQL Search Orders for Rice
provides database results
Business Operations Calculate, audit and report
Reusable and query input Database processing Business result Current animation stage

Each feature handles a separate responsibility inside Priya's grocery database.

2. Implement User-Defined Functions, Triggers and Dynamic SQL in SQL Server

1. User-Defined Functions (UDFs)

Scalar Function = a function that returns one value.

Table-Valued Function = a function that returns a table.

Priya needs the same bill calculation in multiple queries. We can create one reusable function.

The function accepts price and quantity. It returns their calculated total.

2. Triggers

DML Trigger = a routine that responds to INSERT, UPDATE or DELETE operations.

Priya also needs an audit record whenever someone inserts an order.

The trigger handles this automatically. The inserted table contains newly inserted records.

3. Dynamic SQL

Dynamic SQL = a SQL statement created and executed at runtime.

Priya sometimes needs reports from different order tables. Dynamic SQL can build queries using approved table names.

Example 2: Create a function, trigger and execute Dynamic SQL

Assumes Orders, OrderAudit and ArchivedOrders tables already exist with the required columns.

CREATE FUNCTION dbo.CalculateLineTotal
(
    @Price DECIMAL(10,2),
    @Quantity INT
)
RETURNS DECIMAL(12,2)
AS
BEGIN
    RETURN @Price * @Quantity;
END;
GO

CREATE TRIGGER dbo.trg_Orders_Audit
ON dbo.Orders AFTER INSERT AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.OrderAudit(OrderId, ActionName)
    SELECT OrderId, 'INSERT' FROM inserted;
END;
GO

SELECT dbo.CalculateLineTotal(100, 2) AS LineTotal;

DECLARE @Table sysname = N'Orders',
        @sql NVARCHAR(MAX);

IF @Table NOT IN (N'Orders', N'ArchivedOrders')
    THROW 50001, 'Invalid table name', 1;

SET @sql = N'SELECT OrderId, Product FROM '
    + QUOTENAME(@Table)
    + N' WHERE Product = @Product';

EXEC sys.sp_executesql @sql,
    N'@Product VARCHAR(50)',
    @Product = 'Rice';

Expected output: Function calculation

LineTotal
---------
200.00

The function returns 200 for two units priced at 100 each.

The trigger writes audit records when orders are inserted. Dynamic SQL searches the approved table for Rice.

Important: Functions cannot directly modify database tables. Triggers can modify related tables, but should use set-based operations. Always parameterize dynamic SQL values and validate object names.

3. Quick Summary

User-Defined Functions vs Triggers vs Dynamic SQL

Comparison of three SQL Server programming features
Feature Main purpose Execution
User-Defined Function Reusable calculations and data retrieval Called explicitly in queries
Trigger Automatic database actions Runs when configured events occur
Dynamic SQL Build flexible SQL statements Executed at runtime

The important terms to remember

  • Scalar Function: Returns a single value.
  • Table-Valued Function: Returns a table.
  • DML Trigger: Responds to data modification events.
  • Inserted and Deleted: Special logical tables available inside DML triggers.
  • sp_executesql: Executes dynamic SQL with parameters.

4. Common Mistakes and Best Practices

1. Performing data modifications inside functions

SQL Server functions cannot directly modify database state.

Better approach: Use stored procedures or triggers for database modifications.

2. Writing triggers without considering multiple records

A single INSERT statement can insert many orders. A trigger must handle all affected rows.

Better approach: Use set-based operations with the inserted and deleted tables.

3. Concatenating untrusted input into Dynamic SQL

Untrusted input can introduce SQL injection vulnerabilities.

Better approach: Parameterize values with sp_executesql and validate identifiers using an allow-list and QUOTENAME.

Design tip: Keep functions focused on calculations. Keep triggers small and predictable. Use dynamic SQL only when query structure must change.

5. How These SQL Server Features Come Up in Interviews

Question 1: What is the difference between a stored procedure and a function?

What the interviewer expects: Explain how they differ in purpose and usage.

Strong answer: "A function returns a value or table and can be used in queries. A stored procedure performs database operations and can return result sets. They have different execution and usage rules."

Question 2: What are inserted and deleted tables?

What the interviewer expects: Explain trigger-related logical tables.

Strong answer: "These are special logical tables available inside DML triggers. Inserted contains new row versions, while deleted contains old row versions. We use them to process affected records."

Question 3: What is Dynamic SQL?

What the interviewer expects: Explain runtime query generation and safety.

Strong answer: "Dynamic SQL creates SQL statements at runtime. I prefer sp_executesql because it supports parameters and helps prevent SQL injection when used correctly."

6. Hands-On Practice

Beginner: Pharmacy billing

Create a scalar function that calculates medicine bill totals using price and quantity.

Expected result: Return the correct total for each medicine.

Intermediate: Hospital audit system

Create a trigger that records new patient appointments in an audit table.

Hint: Use the inserted logical table.

Advanced: Grocery reporting system

Build a reporting procedure that selects records from approved order tables dynamically.

  • Allow Orders and ArchivedOrders only.
  • Use QUOTENAME for table identifiers.
  • Use sp_executesql for search parameters.

Challenge: Add filters for product, date and order status safely.

7. Next Steps and Community

Previous article: Views, Stored Procedures and Parameters in SQL Server

Next article: Database Normalization – 1NF, 2NF, 3NF and BCNF

For more details, read the official Microsoft documentation:

Try Priya's grocery examples and create your own database routines. Share your implementation and discuss different design choices with other learners.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.