Showing posts with label Triggers. Show all posts
Showing posts with label Triggers. Show all posts
0

Database Triggers

Posted by Danielle Smith on 11:45 in
During my experience at work, I have used a few triggers on various projects and I have demonstrated how to implement one in my own personal development portfolio. The purpose of today's blog post will be to explain what a Database Trigger actually is, discuss the different types of Trigger and cover their main concepts.

What is a Database Trigger? 

Triggers are a special type of stored procedure which is executed when an event occurs. They can contain exactly the same code as any other stored procedure, so if you haven't already, it may be worth looking at my 4-part blog series on Stored Procedures before you continue reading:


In those previous blog posts, I have covered a lot of the brunt work and syntax that may commonly be found in both Stored Procedures and Triggers; Triggers are basically an extended functionality of the Stored Procedure.  

Types of Database Trigger

There are 3 types of Trigger, which I am going to discuss and cover within this section. 

DML Triggers

Firstly, DML stands for Data Manipulation Language and is the syntax that is responsible for inserting, updating and deleting data within a database. So it probably wouldn't surprise you to know that DML Triggers are created against a database table or view and are fired when either an INSERT, UPDATE or DELETE statement occurs. This is quite possibly the most common trigger that you will come across, and in my case the only trigger type I have ever actually used (though it is still important to know what the other types are in case you ever have a need to use them!).

The example below shows a Trigger that I wrote in order to populate a custom full record number for each User Asset within the database, and runs whenever an INSERT or UPDATE event occurs . The image below quite large so you will have to click on it to see it full size (sorry!). Follow the green comments as they explain exactly what the trigger does:


As you can see, Triggers follow a very similar syntax and set up as any other Stored Procedure. The main difference is (obviously) declaring it as a TRIGGER instead of a PROCEDURE and also declaring when the trigger will be fired (so on INSERT, UPDATE, or DELETE):





When inserting data, a temporary table called inserted is created.
When updating data, 2 temporary tables are created; inserted and deleted.
When deleting data, a temporary deleted table is created.

When these events occur within the Trigger, the inserted and deleted tables are then made available to the Trigger itself so you can manipulate the result set within either, which can be incredibly useful.

The FOR clause, can also be interchanged:

  • FOR/AFTER - The trigger is fired after all constraints have been passed once the data has been added to the database. This is the default trigger type. FOR was used pre-SQL Server 2000 and AFTER was introduced later, however regardless on which one you use they both do the same thing. 
  • INSTEAD OF - The trigger is fired as an alternative to an INSERT, UPDATE or DELETE statement. This clause is most commonly used on Views because you cannot insert into them easily (as you can only modify tables and columns with no underlying data attached. The trigger can be used to pick out the individual up-datable parts and put them into the correct underlying sub-tables. 

On occasions, you may find that nothing has actually occurred within the trigger. So for performance reasons, just escape out of it to prevent that content from running unnecessarily using the following code (however, note that it must always be within the first line of the body of your trigger!):




DDL Triggers 

DDL stands for Database Definition Language and is the syntax that is responsible for controlling the database schema and structure. DDL Triggers are fired when either a DDL event occurs (such as creating new databases or modifying permissions to a database) or when a user logs onto the SQL Server Instance.  Here is a short list of example DDL Trigger Events, though note that there are more (thanks to Microsoft for providing the list in their book: Microsoft SQL Server 2008 - Database Development MCTS Exam 70-433 (ISBN: 978-0-7356-2639-3)):

  • CREATE_DATABASE
  • GRANT_DATABASE
  • REVOKE_DATABASE
  • DENY_DATABASE
  • GRANT_SERVER
  • REVOKE_SERVER
  • DENY_SERVER
  • DROP_LOGIN 
  • UPDATE_STATISTICS 
  • DROP_TRIGGER
  • ALTER_TABLE

DDL Triggers contain scope clauses, which allow you to define exactly in what instance that the trigger should fire:

  • ON ALL SERVER - is triggered on all database instances on that SQL Server.
  • ON DATABASE - is triggered only on that database.

In order to retrieve information within a DDL trigger, you will need to use the EVENTDATA function. This returns XML, which can be queried separately (there will be a future blog post to explain how to do that!).

LOGON Triggers

LOGON Triggers are only fired when a user logs onto the SQL Server Instance; after the authentication of the user but before the user session loads. LOGON Triggers are used when restricting access, so typically when trying to restrict the number of people attempting to log onto that particular SQL Server Instance at any one time. This type of trigger is more commonly only used by Database Maintenance staff and administrators. For more information on these, follow this link:

http://msdn.microsoft.com/en-us/library/bb326598.aspx

Concluding Note

I hope you have found this blog post (and my other posts!) useful. If you have any feedback or questions, please do not hesitate to leave a comment below and I will try to reply to you as quickly as I can. Thanks for reading!

0

An Introduction to T-SQL

Posted by Danielle Smith on 15:07 in , ,
Hi everyone! Right, now we are starting to get down to the more complicated stuff that SQL has to offer.

Recently, I have been working with a senior developer looking at Stored Procedures, User Defined Functions and Triggers and learning how to implement them successfully within a database project.

Although programmable objects can be written in both T-SQL (Transact-SQL) and CLR (Common Language Runtime), I have been learning primarily in T-SQL so that is what my examples will be formed in for the following blog posts. As time goes on - I'm sure I will cover them in CLR too so watch this space!

For the purpose of this mini interim post - I am going to write a little bit of background knowledge about T-SQL. Really this is just to give a flavour on what's to come.

What is T-SQL?

T-SQL (or Transaction-Structured Query Language) is an invaluable extension by Microsoft and Sybase to standard SQL which includes procedural programming concepts, the inclusion of local variables and various supporting functions for data processing. Speaking from experience, I find it like a cross between VB (Visual Basic) and standard SQL. However, it is not a standalone language and has to be used in conjunction with SQL.

What is it used for? 

T-SQL is a massive central hub of Microsoft SQL Server as all applications made to communicate with SQL Server will do so using T-SQL. It can be used in many situations, of which I intend on covering the following: 

  • Stored Procedures
  • Cursors 
  • Error Handling
  • User Defined Functions 
  • Triggers (DML, DDL and LOGON)
I am also splitting these posts up into smaller more digestible chunks too because, as you can imagine, there's quite a lot to cover (plus I am learning as I'm going along, so please bare with me!) If you have any questions please don't hesitate to comment on any of my posts and I should be able to respond fairly quickly.

Copyright © 2009 SQL Genius - Personal Development of a Junior All rights reserved. Theme by Laptop Geek. | Bloggerized by FalconHive.