Sunday , 25 June 2017
Home » SQL » Creating triggers in SQL Server

Creating triggers in SQL Server

Creating triggers in SQL Server

Creating triggers in SQL Server in SQL Server is similar to create a stored procedure. You can create triggers only when certain actions are fired. Triggers can be fired only before (insert, update, delete) and after (insert, update, delete).

Creating triggers in SQL Server is very easy. In this article you are going to create a trigger which will be fired after insert. So when you insert any data in the first table it will automatically insert the same data into the second table. First table is going to be the original table and I will name it as “contacts” and the second table is “contacts_2“.  If you didn’t create your tables yet you need to do it first. You can create your tables using the following query.

Creating table “Contacts”

Creating table “Contacts_2”

Now after you have created your tables it’s time to create your “trigger”. You can create triggers either by using SQL Server Management Studio or typing a query in SQL Editor. In this tutorial I will create a trigger by typing query in SQL editor. So to create the trigger type the query below.

Creating trigger after insert

Now you have successfully created your trigger. Now to test it you have to insert into your “contacts” table. You can insert by using SQL Server Management Studio or type the following insert command.
After you insert data into your contacts table you will see that the same data is inserted into your contacts_2 table. Check your contacts_2 table and you will see that the data is inserted in this table as well. You can create other triggers when delete or update operations are fired.

For more information about triggers click the link below.
Triggers

Creating triggers in SQL Server

Check Also

What is a Stored Procedure

What is a Stored Procedure What is a Stored Procedure? Stored procedures are a group …

3 comments

  1. [email protected]

    Great article,
    Can this example be extended with logical check let say if phone does not contain enough numbers that row to be placed in table contacts_wrong ?

    • Yes this can be done by using CHECK constraint.
      For your case you should create a constraint on your table in the ‘phone’ column.

      ALTER TABLE Contacts
      ADD CONSTRAINT chkPhone CHECK ( Phone LIKE ‘___-___-____’ );

      With the query above you define a format that is accepted in your table for the phones. In order insert to be successful you have to put the phone in ___-___-____ format.

      I am going to create an article about CHECK Constraints and I will show more examples about CHECK Constraints on that article.

  2. [email protected]

    Thx, that would be great.
    waiting to see more from you 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *