SQL Talk

Temporary Tables: NO
System Tables: Yes

Ref: 

    http://www.techbrothersit.com/2015/02/create-trigger-on-temp-tables-and.html

What are Inserted and Deleted Tables in SQL Server?

DML trigger statements use two special tables: the INSERTED and DELETED table. SQL Server automatically creates and manages these tables.Inserted and Deleted tables are temporary tables that are created by SQL Server in the context of a trigger. That means these two tables can only be available as part of a trigger. If you try to access these tables outside of a trigger, then you will get an error. The table structure of both inserted and deleted tables will be exactly the same as the table structure of the table on which the trigger is created.

Whenever you fire any insert, update, or delete statement on the table, all the new records are actually going to the inserted table i.e. all the updated and new records are present in the inserted table. On the other hand, all the old values are present in the deleted table.

For multiple DML operations being done in the trigger, these tables are affected in the following manner:

• If any record is being inserted into the main table, a new entry, of the record being created, is also inserted into the INSERTED table.
  
  
• If any record is being deleted from the main table, an entry of the record is being deleted, is inserted into the DELETED table.
  
  
• If any record is being updated in the main table, an entry of that record (before it was updated), is added to the DELETED table and another entry of that record (after it was updated), is inserted into the INSERTED table.

INSERTED table, is a special table used by DML triggers. When you add a new row into tblEmployee table, a copy of the row will also be made into inserted table, which only a trigger can access. You cannot access this table outside the context of the trigger. The structure of the inserted table will be identical to the structure of tblEmployee table.

DELETED table, is a special table used by DML triggers. When you delete a row from tblEmployee table, a copy of the deleted row will be made available in DELETED table, which only a trigger can access. Just like INSERTED table, DELETED table cannot be accessed, outside the context of the trigger and, the structure of the DELETED table will be identical to the structure of tblEmployee table.

The After trigger for INSERT event, makes use of inserted table. 

The After trigger for DELETE event, makes use of deleted table. 

The After trigger for UPDATE event, makes use of both inserted and deleted tables. 

What is a SQL Server Trigger?

A SQL Server trigger is a piece of procedural code, like a stored procedure which is only executed when a given event happens. There are different types of events that can fire a trigger. Just to name you a few, the insertion of rows in a table, a change in a table structure and even a user logging into a SQL Server instance.

There are three main characteristics that make triggers different than stored procedures:

• Triggers cannot be manually executed by the user.
• There is no chance for triggers to receive parameters.
• You cannot commit or rollback a transaction inside a trigger.

The fact that it's impossible to use parameters on triggers is not a limitation to receive information from the firing event. As you will see further on, there are alternatives to obtain information about the firing event.

In SQL server there are 3 types of triggers
1. DML triggers 
2. DDL triggers
3. Logon trigger

1. DML (Data Modification Language) triggers:

    This is the most used class of triggers. DML stands for Data Manipulation Language. INSERT, UPDATE, and DELETE statements are DML statements. DML triggers are fired, when ever data is modified using INSERT, UPDATE, and DELETE.  In this case the firing event is a data modification statement; it could be an insert, update or delete statement either on a table or a view.

DML triggers can be again classified into 2 types.

1. After triggers (Sometimes called as FOR triggers)
    After triggers, as the name says, fires after the triggering action. The INSERT, UPDATE, and DELETE statements, causes an after trigger to fire after the respective statements complete execution.

2. Instead of triggers
    On ther hand, as the name says, INSTEAD of triggers, fires instead of the triggering action. The INSERT, UPDATE, and DELETE statements, can cause an INSTEAD OF trigger to fire INSTEAD OF the respective statement execution. This is very useful in cases where you need to have cross database referential integrity.

2. DDL (Data Definition Language) triggers:
    
    This class of triggers fires upon events that change the structure (like creating, modifying or dropping a table), or in certain server related events like security changes or statistics update events. DDL triggers fire in response to DDL events - CREATE, ALTER, and DROP (Table, Function, Index, Stored Procedure etc...). Certain system stored procedures that perform DDL-like operations can also fire DDL triggers. Example - sp_rename system stored procedure

What is the use of DDL triggers

• If you want to execute some code in response to a specific DDL event
• To prevent certain changes to your database schema
• Audit the changes that the users are making to the database structure

Syntax for creating DDL trigger

DDL triggers scope : DDL triggers can be created in a specific database or at the server level. 

The following trigger will fire in response to CREATE_TABLE DDL event. 

3. Logon trigger

As the name implies Logon triggers fire in response to a LOGON event. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. 

Logon triggers can be used for
1. Tracking login activity
2. Restricting logins to SQL Server
3. Limiting the number of sessions for a specific login

Logon trigger example : The following trigger limits the maximum number of open connections for a user to 3.