Sql Server Trigger

SQL Server Trigger

- A type of stored procedure that run automatically when an event occurs within database server.

3 Type of Trigger:

1) DML: Data Manipulation Language

   - Run on manipulation of data event such as Insert, Update or Delete

2) DDL: Data Definition Language

   - Run on manipulation of data structure or schema event such as CREATE, ALTER and DROP

3) Logon:

   - Run on user Logon event when user session is being established.

   
You can get more detail from Microsoft website, but our site article is meant cover practicle most used code that include tips and tricks to solve some usual problem.
Below is a sample of a DML trigger that does the following:
1) One trigger that handle event for all 3 Insert, Update, Delete
2) Answer common questions such as:
   1)How to distinguish which event is fired from Insert, Update or Delete?
       - You determine that through the two tables provided by trigger result:
           1) Inserted: stores all new record being inserted
           2) Deleted: stores all record being deleted
           NOTE:
               In an update action, SQL server would do 2 actions:
                   1) delete the original row
                   2) inserted a new updated row
               -> Therefore, in an update statement, you will see old record in Deleted table, and updated row in Inserted table
   2) How to know which column is updated?
       -> There are a SQL Server called COLUMNS_UPDATED() which return binary value that represents all columns within a table and tell you which one is updated based on its bit position being (0/1) -> 0: not updated, 1: updated
           Example: I have 5 columns, and my 3rd column is updated and others are not:
               COLUMNS_UPDATED() = 00100 = 4
               To know if 3 column is updated, you mask the binary with 4 and if the result greater than 0, then it's updated.
               (COLUMNS_UPDATED() & 4)>0
                   -> this will be true if 3 columns is updated
       -> The example above is too much work and it's still doesn't give you the column name or anything related to the column so you know right there.
           -> Therefore, we'll make use of a function called sys.fn_IsBitSetInBitmask(), buildin function to get the detail data you want