CTE in SQL Server

A Common Table Expression (CTE) in SQL Server is a feature that allows you to create a temporary result set that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE is defined using the WITH clause and is followed by a SELECT, INSERT, UPDATE, or DELETE statement. The SELECT statement defines the structure of the CTE and the SELECT, INSERT, UPDATE, or DELETE statement that follows it can reference the CTE.

A CTE is useful for breaking down a complex query into simpler parts, making it easier to read and understand. It can also be used to simplify the process of querying hierarchical data, such as an organizational chart.

Here is an example of a CTE that is used to calculate the total sales for each product category:

WITH SalesCTE (Category, TotalSales)
AS
(
    SELECT Category, SUM(Sales)
    FROM Products
    GROUP BY Category
)
SELECT Category, TotalSales
FROM SalesCTE

In this example, the subquery is embedded in the SELECT statement and calculates the total sales for each category. This can be less readable than a CTE and can be more complex if there are multiple subqueries. It is important to note that CTEs are only visible to the query immediately following the WITH clause and it is not stored in the database like a table, so it is not accessible to other queries or sessions.

SQL Server Trigger

A trigger is a special type of stored procedure that automatically executes in response to certain events on a table or view in a SQL Server database. Here is an example of a trigger that updates the “testEmployee” table whenever a new employee is inserted:

In this example, the trigger is named “UpdateEmployees” and is set to execute on the “testEmployee” table. The “AFTER INSERT” clause specifies that the trigger should fire after a new row has been inserted into the table.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.testEmployee
(EmployeeId Int identity (1,1),
FirstName NVARCHAR(100),
LastName NVARCHAR(100),
Salary Decimal(12,2),
InsertDate DATETIME CONSTRAINT DF_TS_UTC1 DEFAULT GETUTCDATE(),
UpdateDate DATETIME CONSTRAINT DF_TS_UTC2 DEFAULT GETUTCDATE()
)
GO


CREATE TRIGGER [dbo].[UpdateEmployees]
ON [dbo].[testEmployee]
AFTER INSERT
AS
BEGIN
    UPDATE testEmployee
    SET Salary = Salary * 1.1
    WHERE EmployeeID IN (SELECT EmployeeID FROM inserted)
END
GO

The code inside the trigger updates the salary of any employee that was just inserted by multiplying their current salary by 1.1. The “inserted” virtual table is used to reference the newly inserted rows and the “WHERE” clause filters the update to only apply to rows whose EmployeeID is present in the inserted table.

This trigger will run automatically every time a new employee is inserted into the testEmployee table and update their salary by 10%.