SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



SQL Trigger Example in SQL Server 2008

What is SQL Server Trigger

An MS SQL trigger can contain sql codes that are executed automatically by MS SQL Server engine when a certain event occurs.

Since this sql tutorial is concentrated on DML (Data Manipulation Language) concepts right now, our sql code examples will be a DML sql trigger. And the following MS SQL Server trigger definition will be valid for DML commands.

The events that trigger SQL Server triggers which are actually stored t-sql codes are sql INSERT, UPDATE and DELETE statements executed on a sql database table.

For example, if you create trigger to execute after Insert statement on a sql table, when a new row or a set of new rows are inserted into the database table then the t-sql trigger will execute.
The execution of SQL Server trigger means that the stored sql codes will run and processed by the sql engine automatically.

SQL developers can also create sql triggers that will work instead of Insert, Update, Delete commands by using the INSTEAD OF hint during SQL Server trigger creation.
Instead Of triggers contain t-sql codes that will run instead of the original triggering event.

SQL Server Trigger Example

Let's continue our sql trigger tutorial with a SQL trigger example. We will manage this case using trigger in SQL Server database table.
Here is the scenario or the business case.
Our sample company has a Customers table in a SQL Server database instance.
The transactions of company sales to the customers are stored in sql database table called Sales.
Each sale's total amount is stored in [Net Amount] field in sql table Sales.

The business requirement is as follows :
If a customer's overall sales amount is less than 10,000 $ then mark the customer priority field with 3.
If the total amount is between 10,000 and 50,000 then the customer priority should be set to 2.
If a more sales amount is reached for that customer, the customer priority database field should be 1.
Note that if no sales transaction has been created yet, that customer will not have any priority (priority sql field will be NULL).

Here is the sql database design or the sql tables create scripts that will be used in mssql 2008 trigger example.
Note that the customer priority field which sql trigger will update is in Customers table.

CREATE TABLE Customers (
 CustomerId smallint identity(1,1),
 Name nvarchar(255),
 Priority tinyint
)

CREATE TABLE Sales (
 TransactionId smallint identity(1,1),
 CustomerId smallint,
 [Net Amount] int,
 Completed bit
)

First we will create an SQL Server AFTER trigger. And this AFTER trigger will work after each sql INSERT, UPDATE and DELETE statement.

So here is the SQL Server trigger definition in our example case.
This is how we create trigger in sql codes.

CREATE TRIGGER dbo.Update_Customer_Priority ON dbo.Sales
AFTER INSERT, UPDATE, DELETE
AS
... -- we will complete here soon

Now we should consider to update all customers affected by the sql trigger event (Insert, Update or Delete statement execution on sql table Sales).
So the t-sql code inside the SQL Server trigger code will contain a similar sql code block to shown below:

WITH CTE AS (
  select CustomerId from inserted
  union
  select CustomerId from deleted
)
UPDATE Customers
SET
  Priority = ... -- we will complete here soon
FROM Customers c
INNER JOIN CTE ON CTE.CustomerId = c.CustomerId

The sql CTE select statement returns the list of customers affected by Insert, Delete and Update sql command.

Now, we can develop t-sql code to fetch the total sales amount to that customer or those customers affected by the latest SQL Server DML command.
Here is an sql code from SQL Server 2008 trigger example that will get sum of sales transaction amount.

select
  CustomerId,
  SUM([Net Amount]) Total
from Sales
inner join CTE on CTE.CustomerId = Sales.CustomerId
where
  Completed = 1
group by Sales.CustomerId

TSQL Code of SQL Trigger AFTER Insert, Update, Delete

And now we are ready to join these t-sql code blocks to create a total solution.
The solution is actually consists of a single sql trigger in SQL Server 2005 or in SQL Server 2008, etc.
The resultant SQL trigger that is working on my MS SQL Server 2008 R2 database instance is as follows :

CREATE TRIGGER dbo.Update_Customer_Priority
 ON dbo.Sales
AFTER INSERT, UPDATE, DELETE
AS

WITH CTE AS (
 select CustomerId from inserted
 union
 select CustomerId from deleted
)
UPDATE Customers
SET
 Priority =
  case
   when t.Total < 10000 then 3
   when t.Total between 10000 and 50000 then 2
   when t.Total > 50000 then 1
   when t.Total IS NULL then NULL
  end
FROM Customers c
INNER JOIN CTE ON CTE.CustomerId = c.CustomerId
LEFT JOIN (
 select
  Sales.CustomerId,
  SUM([Net Amount]) Total
 from Sales
 inner join CTE on CTE.CustomerId = Sales.CustomerId
 where
  Completed = 1
 group by Sales.CustomerId
) t ON t.CustomerId = c.CustomerId

GO

Sample SQL Server Trigger AFTER Insert, Update, Delete


How SQL Server Trigger Works ?

To complete this sql trigger tutorial, let's populate Customers sql table with sample data using Insert statement.

insert into Customers select N'MS SQL Server Team', NULL
insert into Customers select N'MS Windows Team', NULL
insert into Customers select N'MS Internet Explorer Team', NULL

SQL trigger customer sample data in SQL Server database table

After we insert a sales transaction for customer 1 with an amount of 5000$ using INSERT statement :

insert into Sales select 1, 5000, 1

The related customer priority field is updated as 3 by SQL Server trigger.

SQL Server trigger after TSQL insert statement

You can now execute the below T-SQL Insert statement which will trigger sql update statement on Customers table on priority field of Customer 2.

insert into Sales select 2, 45000, 1

Another tsql command we will execute together is as follows:

insert into Sales
select CustomerId, 7500, 1 from Customers

This t-sql INSERT statement will add more than 1 row into the sql Sales transaction table.
Actually the above statement will create 1 row for each customer in one statement at the same time.
And the output of sql SELECT statement on both sql database tables are as seen in the below screenshot.

mass update trigger SQL Server after insert update delete command

You can now sql UPDATE all sales transactions and set Completed mark to false.
In this case we assume that there is not yet any transaction completed.
So all customers' priority field values must be equal to NULL value.

update Sales set Completed = 0

And the result of the SQL trigger is just as we suggested.

TSQL trigger in SQL Server 2008 example

I hope t-sql developers will like this short MSSQL trigger tutorial with samples.
For an other SQL Server trigger in SQL Server 2008 example please refer to t-sql tutorial Log Price Changes using SQL Server Trigger in SQL Server 2008.






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems