Trigger and Stored procedure in SQL SERVER
Benefits of stored procedure
It reduces the amount of information sent to the database server. It can become a more important benefit when the bandwidth of the network is less. Compilation step is required only once when the stored procedure is created.
Stored Procedure in SQL Server can be defined as
the set of logical group of SQL statements which are grouped to perform a specific task.
A trigger is
a special kind of a stored procedure that executes in response to certain action on the table like insertion, deletion or update of data. It is a database object which is bound to a table and is executed automatically. You can't explicitly invoke it
DDL triggers
execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.
DML triggers
execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.
Logon triggers
fire in response to the LOGON event that is raised when a user session is being established.
INSTEAD OF triggers
override the standard actions of the triggering statement: an INSERT, UPDATE, or DELETE.