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.
- Calculations are repeated across multiple queries.
- Audit logic must be added wherever orders are inserted.
- 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.
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
| 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
QUOTENAMEfor table identifiers. - Use
sp_executesqlfor 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:
- Microsoft Learn: User-Defined Functions
- Microsoft Learn: DML Triggers
- Microsoft Learn: sp_executesql
Try Priya's grocery examples and create your own database routines. Share your implementation and discuss different design choices with other learners.