_edited_edited.png)
Stored procedures
A stored procedure is a prepared SQL code (precompiled collections of SQL statements)that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. Stored procedures in SQL Server are optional control-of-flow statements such as such as IF statements and loops, stored under a name and processed as a unit.
Execution: Stored procedures must be called explicitly or executed by the user or application.
Parameters: Accepts input parameters and returns output parameters.
Example
Creating a simple stored procedure in SQL Server:
CREATE PROCEDURE GetEmployeeDetails
@EmployeeID INT
AS
BEGIN
SELECT FirstName, LastName, Title, Department
FROM Employees
WHERE EmployeeID = @EmployeeID;
END;Executing the stored procedure (created above) by using the SQL statement:
EXEC GetEmployeeDetails @EmployeeID = 1;Parameters in Stored Procedures
Stored procedures can accept parameters, both input and output. Here’s an example with input and output parameters.
CREATE PROCEDURE GetEmployeeInfo
@EmployeeID INT,
@FirstName NVARCHAR(50) OUTPUT,
@LastName NVARCHAR(50) OUTPUT
AS
BEGIN
SELECT @FirstName = FirstName, @LastName = LastName
FROM Employees
WHERE EmployeeID = @EmployeeID;
END;Executing the above stored procedure with output parameters:
DECLARE @FirstName NVARCHAR(50), @LastName NVARCHAR(50);DECLARE @FirstName NVARCHAR(50), @LastName NVARCHAR(50);
EXEC GetEmployeeInfo @EmployeeID = 1, @FirstName = @FirstName OUTPUT, @LastName = @LastName OUTPUT;
SELECT @FirstName AS FirstName, @LastName AS LastName;Views
A view in SQL is a virtual table that is based on the result set of a SQL SELECT query. It does not store the data itself but rather provides a way to access and manipulate the data stored in one or more tables. Views can be used to simplify complex queries, enhance security by restricting access to specific data (columns or rows), and present data in a specific format. Views can often be updated, but this depends on the complexity of the view. Some views are read-only. Views are created using the CREATE VIEW statement.
CREATE VIEW view_name
AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;Once created, views can be queried like regular tables.
SELECT * FROM view_name;Execution: Views are used in queries like regular tables but do not contain data themselves; they fetch data from the underlying tables.
Parameters: Views do not accept parameters.
Types of Views:
Simple View: Based on a single table and does not include functions, groups of data, or multiple tables.
Complex View: Based on multiple tables and can include functions, joins, and group data.
Example
Assume you have a table employees:
CREATE TABLE employees (id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(100),
salary DECIMAL(10, 2));You can create a view to display employees from the ‘Sales’ department:
CREATE VIEW sales_employees
AS
SELECT id, name, salary
FROM employees
WHERE department = 'Sales';You can then query the view like this:
SELECT * FROM sales_employees;This will return only the employees who belong to the ‘Sales’ department.
Triggers
A trigger in SQL is a procedural code that is automatically executed in response to certain events on a specified table. An SQL trigger allows you to specify SQL actions that should be executed automatically when a specific event occurs in the database. These events can be insertions, updates, or deletions on a table or view. For example, you can set a trigger to automatically update a record in one table whenever a record is inserted into another table.
Execution: Triggers are implicitly executed when the specified event occurs.
Parameters: They do not accept parameters.
Types of triggers
DML Triggers (Data Manipulation Language): These triggers will be executed in response to data manipulation events such as INSERT, UPDATE, DELETE.
DDL Triggers (Data Definition Language): These triggers will be executed in response to changes in the database schema such as CREATE, ALTER, DROP.
Logon Triggers: These triggers will be executed in response to logon events to the SQL Server.
Variants of Triggers
BEFORE Triggers: These triggers gets executed before the triggering action is performed.
AFTER Triggers: These triggers gets executed after the triggering action is performed.
INSTEAD OF Triggers: These triggers will replaces the triggering action.
However, it’s important to note that SQL Server does not support BEFORE triggers. Instead, it only supports AFTER and INSTEAD OF triggers.
After Trigger
After trigger execute after the triggering SQL statement such as INSERT, UPDATE, or DELETE has completed and the data has been inserted, updated, or deleted.Often used for enforcing business rules, auditing changes, or cascading changes to related tables.
CREATE TABLE Employees (EmployeeID int PRIMARY KEY,
FirstName nvarchar(50),
LastName nvarchar(50),
Salary money);
CREATE TABLE EmployeeAudit (AuditID int IDENTITY(1,1) PRIMARY KEY,
EmployeeID int,
OldSalary money,
NewSalary money,
ChangeDate datetime);
CREATE TRIGGER trgAfterUpdate
ON Employees AFTER UPDATE
AS
BEGIN
INSERT INTO EmployeeAudit (EmployeeID,OldSalary,NewSalary,ChangeDate)
SELECT inserted.EmployeeID,deleted.Salary
AS OldSalary,inserted.Salary
AS NewSalary,GETDATE()
FROM inserted
JOIN deleted ON inserted.EmployeeID = deleted.EmployeeID;
END;Instead Of Trigger
Instead of trigger execute in place of the triggering SQL statement. The actual data modification (INSERT, UPDATE, DELETE) defined by the trigger must be manually specified within the trigger’s logic if needed.Useful for complex view updates, handling non-standard logic, or preventing certain actions.
CREATE TABLE EmployeeView (EmployeeID int,
FullName nvarchar(100),
Salary money);
CREATE VIEW vwEmployees
AS
SELECT EmployeeID,FirstName + ' ' + LastName
AS FullName,Salary
FROM Employees;
CREATE TRIGGER trgInsteadOfInsert
ON vwEmployees INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Employees(EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID,SUBSTRING(FullName, 1, CHARINDEX(' ', FullName) - 1), SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, LEN(FullName)),Salary
FROM inserted;
END;Before Trigger
In SQL Server, there is no direct support for BEFORE triggers as found in some other relational database management systems like Oracle or MySQL. However, similar functionality can be achieved using INSTEAD OF triggers or by implementing constraints and additional logic within your application or stored procedures.
To simulate the behavior of a BEFORE trigger, you can use an INSTEAD OF trigger to perform actions before the actual operation. Here’s an example to illustrate this:
Example
CREATE TABLE Employees (EmployeeID int PRIMARY KEY,
FirstName nvarchar(50),
LastName nvarchar(50),
Salary money);Instead of trigger to simulate before trigger
CREATE TRIGGER trgInsteadOfInsert
ON Employees INSTEAD OF INSERT
AS
BEGIN -- Check constraint condition IF EXISTS (SELECT 1 FROM inserted WHERE Salary <= 0)
BEGIN -- If validation fails, raise an error
RAISERROR ('Salary must be greater than zero.', 16, 1);
ROLLBACK TRANSACTION;
RETURN;
END -- If validation passes, insert the data
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID, FirstName, LastName, Salary
FROM inserted;
END;Explanation: The trigger is created using INSTEAD OF INSERT on the Employees table. Inside the trigger, we check if any of the new rows being inserted have a Salary less than or equal to zero. If the validation fails, an error is raised using RAISERROR, the transaction is rolled back using ROLLBACK TRANSACTION, and the insert operation is aborted. If the validation passes, the data from the inserted pseudo-table is inserted into the Employees table.
Thanks!
Keep Learning!!
Comentarios