Creating an AFTER trigger

To understand what the trigger is, see it as automation. It fires whenever an event occurs.
A good example is an automated email that is sent out whenever someone submitted a form or registered to a website.

Trigger in SQL server act the same way. It is activated based on certain conditions.

We will use the table tblStudent below to explain the trigger.


You can read how to create this table here.

Step 1.

Create a Trigger for tblStudent table


To create a trigger for a table or view the following SQL syntax are used.

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
CREATE TRIGGER trigger_name   
ON { table | view }   
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }     
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  

Now let use the above syntax to create a trigger for the student table.

If the above step is executed correctly then it created a trigger inside the Triggers folder on the dbo.tblStudent.



Step 2.

Delete Data and Roll it Back


Now let us write an SQL delete statement to delete from the tblStudent where StudentNumber equals 1.


Execute the code and you will see two results. 
The first one is empty and the second is with the data that we tried to delete.
Trigger prints the two results based on the two select statements in the trigger named tr_tblStudent that we have created in step 1. The first select returns table with empty data because we haven't inserted any data.
The second select returns the data we tried to delete which did not delete anyways because of the rollback tran.



So note, we executed the delete statements then trigger fires up and prints the results.







No comments:

Post a Comment

Note: only a member of this blog may post a comment.

New Post

Create Store procedures

Store procedure has some similarities with views in the sense that it allows encapsulation. i.e. to retrieve for further, later use a series...