Nigel Whitworth's Development Site

You are currently not logged in.

SQL Server Triggers

In my view this feature of SQL Server is a misnomer, a trigger is something that starts something off. In the demos below, I may break one or two relational database rules but we are not particularly interested in whether we do or we don't. A SQL trigger occurs after something has happened. Its better if they had called them events like you do in Visual Basic. A trigger is nothing more than a stored procedure that occurs when something occurs to the data such as INSERT, DELETE or UPDATE. They are nothing more than a stored procedure but their limitation is that they can't change the structure or do anything in relation to the database itself such as CREATE, ALTER or DROP a database. Before I go further, let me show you a trigger. For this explanation of Trigger, create a table with three fields, name, age and sex.

    Create Trigger trgNewMember on tblPeople for Insert as
        Select Count(*) from Inserted

The above section of code can be run in the Query Analyzer or in the manage triggers section in the MMC. To get to the manage trigger section, highlight the table which is to have the trigger applied to it. Select 'All Tasks' option then Manage Triggers. A small modal window will now appear to allow you to edit the triggers or create one. Once the trigger has been created, return to the Query Analyzer program, select the database that you are using. Now insert a record into tblPeople like....

    Insert tblPeople(Name,Age,Sex) values('Carl Black',27,'Male');

In the results box below will contain the count of records inserted by the command which will be 1. If we then add two more records to the database using the following SQL commands...

    Insert tblPeople(Name,Age,Sex) values('Laura Carter',25,'Female');
    Insert tblPeople(Name,Age,Sex) values('Peter Strong',29,'Male');

We will now see that in the results box, two different record sets each with the same number of records returned as being 1. If we have another table called 'tblNewMembers' where we record when they joined. There will be two fields, NewMemberID will be a numeric field and JoinedDate will be a date field.

    CREATE Trigger trgNewMember on tblPeople for Insert as
        Insert into tblNewMember(NewMemberID,JoinedDate)
        Values(@@IDENTITY,GetDate())

When you look in the tblNewMember table, you will see that after you run the following command, a record will be created in the tblMembers table automatically. What if you want to validate the values, you can. Lets suppose the club whose records these belong to only admits people under forty so a trigger can be created to prevent a record with a age field of over 40. The code would look like this :-

    Create Trigger trgNewMember on tblPeople for Insert as
        If((Select count(*) from tblPeople where Age >40)>0))
        Begin
           Rollback transaction
        End

Run the following SQL script in the Query Analyzer.

    Delete from tblPeople
    Insert tblPeople(Name,Age,Sex) values('Carl Black',27,'Male');
    Insert tblPeople(Name,Age,Sex) values('Laura Black',27,'Female');
    Insert tblPeople(Name,Age,Sex) values('Sara Walker',47,'Female');
    Insert tblPeople(Name,Age,Sex) values('Peter Smythe',27,'Female');
    Select * from tblPeople
    Select * from tblNewMember
    Go

Once the third line has been executed, the processing has stopped and all the record including the first two are removed from the database. It is possible to create complex and advanced triggers but we're not going to go into that for now.

When using an Update trigger, it is possible to determine what fields have been changed. For the above, we could create a trigger to identify if the person has changed his\her name using the Update function. The following trigger will display a message when the name has changed.

    CREATE TRIGGER trg_CheckName ON [dbo].[tblPeople] FOR  UPDATE AS

        if Update(Name) 
            Begin
               Print 'Name Changed'
            end
        else
            begin
               print 'No Change'
            end

Use the following Update commands.

    Update tblPeople set Name='Nigel Whitworth' where UID = 1
    Update tblPeople set Name='Nigel Whitworth' where UID = 1

You will notice that in the first update, the name changes from Carl Black to Nigel Whitworth. In the second, the name of the user stays the same but it still causes a trigger to execute. The Update function will return true for every time the field is updated not whether it has changed. If you change the age of 'Nigel Whitworth' then no message, no notification will occur.

If you intend to vote or make a comment, please enter the security code.

Make a comment

*Comments are the views of individuals, they may or may not be correct.
All comments are reviewed and accepted or rejected.
If you give an email address, you will be sent an email when someone makes a comment on this page.*

Name :
Email :
Comments :

Only name and Code is compulsory.