Sql Server 2008 – What are the two ways to design the triggers

Sql Server provides two ways to design the triggers.

1) After Trigger (Default)

2) Instead of trigger

 

You can specify multiple After triggering actions (Insert, Update, Delete).It can be applied to only tables.

 

Instead of Triggers executes instead of triggering action. It can be applied to views and tables. The primary advantage of this trigger is one can update a view which is made up of multiple tables.

 

The two virtual tables that will be used by trigger are Inserted and Deleted tables.

 

Local variables are user-defined identified by @.The value can change during batch or stored procedure in which it is used.

and Global variables are system-supplied and predefined identified by @@.

 

Local Temporary table is used by single user and will be deleted when user disconnects

Where as global temporary tables can be used by multiple users and the tables will remain until all users disconnect.

 

The order for accessing object Server.Database.Owner.Table.Field.

 

A derived table is a select statement that is used as a table, It is always enclosed in parenthesis and follows from or join. Here is an example of a derived table.

SELECT *FROM Accounts a
INNER JOIN (SELECT CustomerID, CustomerName FROM Customers)c ON a.CustomerID = c.CustomerID .

The SELECT statement that gets data from the Customers table is the derived table.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s