Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

SQL Server 2012 : Transact-SQL - Functions, Triggers

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
11/30/2014 8:36:17 PM

Functions

In many instances, you can accomplish a task using either a stored procedure or a function. Both functions and stored procedures can be custom defined and part of any application. They both can be written in T-SQL or .NET and contain a header and body. They can both accept parameters and provide output to the calling application or user.

Creating a Function

To illustrate, let’s create a function that accepts a pet weight and returns a table that lists the pets who exceed that weight and their owners. The sample script is as follows:

CREATE FUNCTION GiantPets (@minWeight INTEGER)
RETURNS @retGiantPetsTable TABLE
(
pet_name     VARCHAR(50) NOT NULL,
pet_weight    INT                   NOT NULL,
owner_name VARCHAR(50) NOT NULL
)
AS
BEGIN
     INSERT @retGiantPetsTable
     SELECT p.pet_name, p.pet_weight,o.owner_name
     FROM Pets p, Owners o
     WHERE p.pet_id=o.pet_id AND p.pet_weight > @minWeight

     RETURN
END

Invoking a Function

Functions are designed with the idea that they will send their output to a query or T-SQL statement. With the exception of scalar functions, functions are called differently than stored procedures. Since the example function returns a table, you can call it as part of a SELECT statement by referencing it in the FROM clause, for example:

SELECT * FROM GiantPets(50)

This will return the following result set:


pet_name   pet_weight      owner_name

Lady          155              Bryan

Zeus          185                Rob

System-Defined Functions

The GiantPets function is a user-defined function. As with system stored procedures, SQL Server comes with many built-in functions. There are so many that they are grouped into different categories. Scalar functions use a single value or list of values, as opposed to data from multiple rows of a table. An example of a scalar function is GETDATE(), which returns the current date:

SELECT GETDATE()

Aggregate functions utilize multiple rows of data and return a value to the user. To find the average value for all the rows in a column, you use the AVG function as follows:

SELECT AVG(pet_weight) FROM Pets

There are also a lot of security-related functions. If you wanted to know whether the current user is a member of the sysadmin group, you would use the IS_SVRROLEMEMBER as follows:

SELECT IS_SRVROLEMEMBER('sysadmin')

This function will return 1 for true and 0 for false.

Triggers

A trigger is a special kind of stored procedure that is executed when a special event happens within SQL Server. An event could include the execution of a given DDL or DML statement or a login to SQL Server. For example, if you wanted to make sure MicroChipID was valid before allowing a new pet to be added to the Pets table, you could create a trigger on INSERT. The script follows:

USE [VetClinic]
GO
CREATE TRIGGER ValidateMicroChip
ON Pets
FOR INSERT
AS

IF EXISTS(
SELECT MicroChipID FROM MicroChips
WHERE MicroChipID IN (
SELECT MicroChipID FROM inserted)
)

RAISERROR ('The chip was found!', 16, 1)
ELSE

BEGIN
     RAISERROR ('The chip was NOT found!', 16, 1)
     ROLLBACK
END

GO

Now, when you attempt to insert a new pet into the table using a microchip ID that is not in the MicroChips table, an error will be issued, and the pet information will not be added to the table, as shown in the following script:

INSERT INTO Pets VALUES (8,'Sushi',5,'0034DDA')

This will result in the following result:


Msg 50000, Level 16, State 1, Procedure ValidateMicroChip, Line 13

The chip was NOT found!

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

Since the only valid microchip ID is 34BA123, if you used this value, the result would be as follows:


Msg 50000, Level 16, State 1, Procedure ValidateMicroChip, Line 10

The chip was found!



(1 row(s) affected)

Normally, you would not have to raise an error on a successful insert; this was done here only to help illustrate the code path that was executed in the trigger.

If you take a look at the trigger definition, you’ll see that a table called inserted was referenced in the IF EXISTS clause. Within a DML trigger, SQL Server provides this table and a deleted table for DELETE triggers. Together, these tables allow your triggers to do something with the data and, depending on your business requirements, roll back any transactions. In this sample scenario, if the chip wasn’t found, the trigger rolled back the transaction, causing the pet information not to be written to the table.

Other -----------------
- SQL Server 2012 : Transact-SQL - Stored Procedures
- SQL Server 2012 : Transact-SQL - Transactions
- SQL Server 2012 : Transact-SQL - Data Manipulation Language (part 2)
- SQL Server 2012 : Transact-SQL - Data Manipulation Language (part 1)
- SQL Server 2012 : Transact-SQL - The VetClinic Sample Database Revisited, Data Types
- Microsoft Sharepoint 2013 : Claims Authentication and Oauth - Server-to-Server Authentication
- Microsoft Sharepoint 2013 : Application Authentication (part 3) - App Authentication - App Catalog App Authentication
- Microsoft Sharepoint 2013 : Application Authentication (part 2) - App Authentication - SharePoint Store App Authentication
- Microsoft Sharepoint 2013 : Application Authentication (part 1) - Cloud App Model, OAuth
- Microsoft Sharepoint 2013 : User Authentication (part 4) - Using Claims-Based Identity - Federated User Authentication Process
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
Trailers Game
- The Banner Saga 2 [PS4/XOne/PC] PC Launch Trailer
- Welkin Road [PC] Early Access Trailer
- 7th Dragon III Code: VFD [3DS] Character Creation Trailer
- Human: Fall Flat [PS4/XOne/PC] Coming Soon Trailer
- Battlefleet Gothic: Armada [PC] Eldar Trailer
- Neon Chrome [PS4/XOne/PC] PC Release Date Trailer
- Rocketbirds 2: Evolution [Vita/PS4] Launch Trailer
- Battleborn [PS4/XOne/PC] 12 Min Gameplay Trailer
- 7 Days to Die [PS4/XOne/PC] Console Trailer
- Total War: Warhammer [PC] The Empire vs Chaos Warriors Gameplay Trailer
- Umbrella Corps [PS4/PC] Mercenary Customization Trailer
- Niten [PC] Debut Trailer
- Stellaris [PC] Aiming for the Stars - Dev. Diary Trailer #1
- LawBreakers [PC] Dev Diary #4: Concept Art Evolutions
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer