Articles on SQL, TSQL and Management Studio. You can download Microsoft supplied sample database here: https://drive.google.com/file/d/0B8H2WLrOYbPxcy13RG9JNUpRRHM/edit?usp=sharing

SQL-CLR Trigger Code Example
SQL Server

SQL CLR Trigger Explained

 In the last article, we saw creating a SQL CLR Function. I would suggest reading that article first before reading this one. Like CLR function, one can write SQL Trigger also in the C-Sharp. In this article, we will learn to create the SQL CLR Trigger and execute that in SQL Server Management studio. In this example, we are going to write a CLR Trigger that does not allow entering the Negative Bonus in the Emp table.

Configure SQL CLR and Create Assembly using T-SQL - Refer Code Listing 2
SQL Server

SQL-CLR Function – Creating & Using it

First, database developers write the functions in Dotnet framework and then expose that via a DLL. The SQL server, then loads the DLL into DB engine and starts picking up functions exposed by it. In native SQL language, one can write functions, stored procedures, and triggers. In Dotnet framework, besides these, one can write custom Aggregate Functions and can even define own User-defined Types. Using the Surface Area Configuration tool, one can ask SQL server to allow the injection of Dotnet Framework code in its core engine. This tool ships with the SQL Server installer. The below given steps show enabling the SQL-CLR integration via this tool.

Multi-line table valued function example
SQL Server

SQL Table-Valued Function Example

In the past article we saw creating a simple scalar-valued function. Now, here in this article, we will look at building the Table-Valued function. Unlike the scalar-valued function a table-valued function returns a table to the caller. There are two kinds of table-valued functions we can write. Those are: In-Line Table-Valued functions Multi-line table valued functions In this Example, we learn how to create both the functions. OK, let us start.

SQL Scalar-Valued function with Parameter
SQL Server

SQL Scalar-Valued Function Explained

Stored Procedure and function in SQL server are the compiled unit of SQL Program which performs a specific task. Unlike a stored procedure, a User-Defined Function in SQL server can take part in the SQL Select Queries. In this example, we will try to create and use the Scalar-Valued Function in the SQL Server. A Scalar-Valued function returns a single value to its caller. We create the Examples in this article using Microsoft supplied Titles table of Pubs database. You can download the SQL Script that creates the Pubs and NorthWnd database from the SQL 2005 Section of this site. Before we begin, here is the content of the Titles table that we can refer while going through the article:

Trigger Execution order when both AFTER & INSTEAD OF Exists
SQL Server

SQL Triggers Explained With Examples

An ‘SQL Trigger’ is a compiled unit of SQL Server procedure, which can run automatically when an event occurs on the database objects. For example, you can write a piece of SQL Script (the trigger), which can be called whenever an insert (the event) takes place on a specific table. There are various types of triggers possible in SQL Server. The two crucial types are: DDL Triggers: These triggers act on ‘Data Definition Language’; say, for example, a trigger procedure runs whenever a table is created. We can say DDL trigger as an ‘Database level trigger’ hence it acts on the DB. SQL Server invokes the Trigger procedure whenever the manipulation like Create, Alter and Drop takes place on its DB objects. DML Triggers: These triggers act on ‘Data Manipulation Language’. In DB world, the tables maintain the Data. So, we can do manipulating data through the tables by making use of Insert, Update and Delete statements on the table. We can call DML trigger as an ‘Table Level Trigger’ since it operates at table level. Simply,