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
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.
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.
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.
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.