Views and Triggers in SQL Server - Microsoft SQL Server Tutorial Series Part 12

---

Microsoft SQL Server Tutorial Series: Beginner to Expert

Part 12: Views and Triggers in SQL Server

Welcome back to our SQL Server tutorial series! In this session, we will explore two powerful database objects: Views and Triggers. These tools help you organize, simplify, and automate tasks in your SQL Server databases.

---

In this tutorial, you will learn:

• What are Views in SQL Server and why use them
• How to create and manage Views
• What are Triggers and their types
• How to create Triggers to automate database actions
• Best practices for Views and Triggers

---

👁️ What is a View in SQL Server?

A View is a virtual table based on the result set of a SQL query. It does not store data itself but displays data stored in one or more tables. Views help you simplify complex queries, enhance security, and present data in a meaningful way.

Think of a View as a saved query you can treat like a table.

---

🛠️ Creating a Simple View

Let's create a View named ActiveStudentsView that shows only active students from our Students table:

CREATE VIEW ActiveStudentsView AS
SELECT StudentID, FirstName, LastName, BirthDate
FROM Students
WHERE IsActive = 1;
GO

Now, you can query this View like a table:

SELECT * FROM ActiveStudentsView;
GO

---

🔄 Updating Data through Views

Simple Views allow updating underlying tables, but complex Views (joining multiple tables, aggregations, etc.) may not support updates.

---

⚡ What is a Trigger?

A Trigger is a special kind of stored procedure that automatically runs in response to certain events on a table, such as INSERT, UPDATE, or DELETE.

Triggers help enforce business rules, maintain audit trails, and automate processes.

---

🔧 Types of Triggers in SQL Server

Trigger Type	Description
AFTER Trigger	Executes after the triggering SQL statement completes
INSTEAD OF Trigger	Executes instead of the triggering SQL statement (useful for Views)

---

📝 Creating an AFTER INSERT Trigger

Suppose we want to log whenever a new student is added. First, create a log table:

CREATE TABLE StudentInsertLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    StudentID INT,
    InsertedAt DATETIME DEFAULT GETDATE()
);
GO

Now, create the trigger on the Students table:

CREATE TRIGGER trgAfterStudentInsert
ON Students
AFTER INSERT
AS
BEGIN
    INSERT INTO StudentInsertLog (StudentID)
    SELECT StudentID FROM inserted;
END;
GO

Now, every time a student is inserted, an entry will be added to StudentInsertLog.

---

⚠️ Best Practices for Views and Triggers

Area	Best Practice
Views	Keep Views simple and efficient; avoid heavy computations
Triggers	Use triggers sparingly; excessive triggers can impact performance
Documentation	Always document the purpose and logic of Views and Triggers

---

🧾 Quick SQL Cheat Sheet

-- Create a View
CREATE VIEW [ViewName] AS
SELECT Column1, Column2 FROM TableName WHERE Condition;
GO

-- Create an AFTER INSERT Trigger
CREATE TRIGGER [TriggerName]
ON [TableName]
AFTER INSERT
AS
BEGIN
    -- Trigger Logic Here
END;
GO
  

---

🌟 Summary

• Views are virtual tables that simplify and secure data access
• Triggers automate actions in response to data changes
• Use AFTER and INSTEAD OF triggers based on your needs
• Keep Views and Triggers optimized for performance

---

🔗 What’s Next?

In the upcoming part, we will explore Transactions and Isolation Levels to understand how SQL Server manages data consistency and concurrency.

---

Have questions or want to share your experiences with Views or Triggers? Drop a comment below! 🙌

← Previous Next →

Stored Procedures and User-Defined Functions in SQL Server

---

Microsoft SQL Server Tutorial Series: Beginner to Expert

Part 11: Stored Procedures and User-Defined Functions in SQL Server

Welcome to Part 11 of our SQL Server series! In this post, you’ll learn how to structure your database logic with Stored Procedures and User-Defined Functions (UDFs).

---

🧠 What is a Stored Procedure?

A Stored Procedure is a set of SQL statements that can be saved and reused. It allows you to encapsulate logic, improve performance, and enforce security.

✅ Benefits:

• Reusability and modularity
• Improved performance due to pre-compilation
• Security via controlled execution

📘 Basic Syntax Example:

CREATE PROCEDURE GetAllStudents
AS
BEGIN
    SELECT * FROM Students;
END;
GO

🛠️ Execute the Procedure:

EXEC GetAllStudents;

🧩 With Parameters:

CREATE PROCEDURE GetStudentByID
    @StudentID INT
AS
BEGIN
    SELECT * FROM Students WHERE StudentID = @StudentID;
END;
GO

-- Execute it
EXEC GetStudentByID @StudentID = 1;

---

🧮 What is a User-Defined Function (UDF)?

A User-Defined Function allows you to create reusable SQL logic that returns a single value or a table.

🔹 Scalar Function Example:

CREATE FUNCTION GetStudentAge (@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE());
END;
GO

-- Use the function
SELECT dbo.GetStudentAge('2005-03-15') AS Age;

🔹 Table-Valued Function Example:

CREATE FUNCTION GetActiveStudents()
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Students WHERE IsActive = 1
);
GO

-- Use the function
SELECT * FROM dbo.GetActiveStudents();

---

📊 Comparison Table: Stored Procedure vs Function

Feature	Stored Procedure	User-Defined Function
Return Type	0 or more result sets	Single value or a table
Parameters	Input and Output	Input only
Transaction Control	Yes	No
Use in SELECT	No	Yes
Typical Use	Business logic & data changes	Reusable calculations, filters

---

🏫 Real-World Example: School System

Use procedures to insert students and functions to calculate GPA or get full names.

CREATE PROCEDURE AddStudent
    @StudentID INT,
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @BirthDate DATE
AS
BEGIN
    INSERT INTO Students (StudentID, FirstName, LastName, BirthDate, IsActive)
    VALUES (@StudentID, @FirstName, @LastName, @BirthDate, 1);
END;
GO

CREATE FUNCTION FullName(@FirstName VARCHAR(50), @LastName VARCHAR(50))
RETURNS VARCHAR(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;
GO

---

🧾 SQL Server Cheat Sheet

-- Stored Procedure
CREATE PROCEDURE [Name] AS BEGIN ... END;

-- Scalar Function
CREATE FUNCTION [Name]() RETURNS DataType AS BEGIN RETURN ... END;

-- Table-Valued Function
CREATE FUNCTION [Name]() RETURNS TABLE AS RETURN (SELECT ...);

---

💡 Best Practices

Tip	Recommendation
Naming Conventions	Use usp_ prefix for procedures and fn_ for functions
Security	Grant EXECUTE rights selectively
Performance	Avoid functions in WHERE clause if possible
Readability	Use comments and consistent formatting

---

✅ Summary

• Stored Procedures = reusable blocks of logic
• Functions = return data (value or table) for use in queries
• Both help you write cleaner, more efficient SQL

---

🔗 What's Next?

Next up in Part 12, we’ll cover: Views and Triggers in SQL Server.

🔁 Previous Parts:

• Part 1: What is SQL Server?
• Part 10: Subqueries and CTEs

Share your own stored procedures or UDF tips in the comments below!

← Previous Next →