.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Creating and managing Triggers in SQL Server [2005/2008]

Posted By:Vishal Nayan       Posted Date: September 01, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

Creating and managing Triggers in SQL Server [2005/2008]
 

Creating and managing Triggers in SQL Server [2005/2008]

 

What are triggers: Triggers are special type of stored procedure which is executed automatically based on the occurrence of a database event. These events can be categorized to as

a)  Data Manipulation Language (DML) and

b) Data Definition Language (DDL) events.

The benefit derived from the trigger is based in their events driven nature. Once created, the trigger automatically fire without user intervention based on an event in the database.

 

A) Using DML Triggers: DML triggers are invoked when any DML commands like INSERT, DELETE, and UPDATE happen on the data of table and or view.

Point to remember:

a) DML trigger are powerful objects for maintaining database integrity and consistency.

b) DML trigger evaluate data before it has been committed to the database.

c) During this evaluation following actions are performed.

·         Compare before and after versions of data

·         Roll back  invalid modification

·         Read from other tables ,those in other database

·         Modify other tables, including those in other database.

·         Execute local and remote stored procedures.

d) We cannot use following commands in DML trigger

·         ALTER DATABASE

·         CREATE DATABASE

·         DISK DATABASE

·         LOAD DATABASE

·         RESTORE DATABASE

e) Using the sys.triggers catalog view is a good way to list all the triggers in a database. To use it, we simply open a new query editor window in SSMS and select all the rows from the view as shown below;

select * from sys.triggers

So let us create DML trigger.

You can create and manage triggers in SQL Server Management Studio or directly via Transact-SQL (T-SQL) statements.

 

 

1) Using AFTER triggers:

 

·         An AFTER trigger is the original mechanism that SQL Server created to provide an automated response to data modifications

·         AFTER trigger fires after the data modification statement completes but before the statement's work is committed to the databases.

·         The trigger has the capability to roll back its actions as well as the actions of the modification statement that invoked it.

 

For all examples shared below I have used Pubs database. You can download its msi file from here and then attach .mdf file in your Sql Sever 2008.

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

 

CREATE TRIGGER tr_au_upd ON authors

AFTER UPDATE,INSERT,DELETE

AS

PRINT 'TRIGGER OUTPUT' +  CONVERT(VARCHAR(5),@@ROWCOUNT)

+ 'ROW UPDATED'

GO

 

UPDATE Statement

UPDATE authors

SET au_fname = au_fname

WHERE state ='UT'

 

Result:

----------------------------------------------------

TRIGGER OUTPUT2ROW UPDATED

 

(2 row(s) affected)

 

Point to remember:

1) If we have a constraint and trigger defined on the same column, any violations to the constraint abort the statement, and the trigger execution does not occur. For example, if we have a foreign key constraint on a table that ensures referential integrity and a trigger that that does some validation on that same foreign key column then the trigger validation will only execute if the foreign key validation is successful.

 

Can we create more than one trigger on one table?

a) We can create more than one trigger on a table for each data modification action. In other words, we can have multiple triggers responding to an INSERT, an UPDATE, or a DELETE command.

 

b) The sp_settriggerorder procedure is the tool we use to set the trigger order. This procedure takes the trigger name, order value (FIRST, LAST, or NONE), and action (INSERT, UPDATE, or DELETE) as parameters.

sp_settriggerorder tr_au_upd, FIRST, 'UPDATE'

 

c) AFTER triggers can be placed only on tables, not on views.

 

d)  A single AFTER trigger cannot be placed on more than one table.

 

e) The text, ntext, and image columns cannot be referenced in the AFTER trigger logic.

 

How to see inserted and deleted rows through Trigger:

 

a) We can find rows modified in the inserted and deleted temporary tables.

b) For AFTER trigger, these temporary memories -resident tables contains the rows modified by the statement.

c) With the INSTEAD OF trigger, the inserted and deleted tables are actually temporary tables created on-the-fly.

 

Lets us try and see how this works;

a) Create a table titles_copy

 

SELECT *

INTO titles_copy

FROM titles

GO

b) Create a trigger on this table

 

 

CREATE TRIGGER tc_tr ON titles_copy

FOR INSERT , DELETE ,UPDATE

AS

PRINT 'Inserted'

SELECT title_id, type, price FROM inserted -- THIS IS TEMPORARY TABLE

PRINT 'Deleted'

SELECT title_id, type, price FROM deleted -- THIS IS TEMPORARY TABLE

--ROLLBACK TRANSACTION

 

 

c) Let us UPDATE rows. After which trigger will get fired.

 

We have written below two statements in trigger, so these rows get printed. The inserted and deleted tables are available within the trigger after INSERT, UPDATE, and DELETE.

PRINT 'Inserted'

SELECT title_id, type, price FROM inserted -- THIS IS TEMPORARY TABLE

PRINT 'Deleted'

SELECT title_id, type, price FROM deleted -- THIS IS TEMPORARY TABLE

 

Result is based on below rule.

Statement    Contents of inserted     Contents of deleted

-----------------------------------------------------------------

INSERT        Rows added                  Empty

UPDATE       New rows                      Old rows

DELETE        Empty                          Rows deleted

 

2) INSTEAD of Trigger:

 

a) Provides an alternative to the AFTER trigger that was heavily utilized in prior versions of SQL Server.

b) It performs its actions instead of the action that fired it.

c) This is much different from the AFTER trigger, which performs its actions after the statement that caused it to fire has completed. This means you can have an INSTEAD OF update trigger on a table that successfully completes but does not include the actual update to the table.

d) Instead Of Triggers fire instead of the operation that fires the trigger, so if you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, they will not actually get deleted (unless you issue another delete instruction from within the trigger) as in below example:

 

 

Let us create INSTEAD OF trigger.

 

if exists (select * from sysobjects

where id = object_id('dbo.cust_upd_orders')

and sysstat & 0xf = 8)

drop trigger dbo.cust_upd_orders

go

 

CREATE TRIGGER trI_au_upd ON authors

INSTEAD OF UPDATE

AS

PRINT 'TRIGGER OUTPUT: '

+CONVERT(VARCHAR(5), @@ROWCOUNT) + ' rows were updated.'

GO

 

Let us write an UPDATE statement now;

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend