SQL Server Difference FAQs-2

1. What are the differences between Instead of Triggers and After Triggers?

S.No	Instead of Triggers	After Triggers
1	Each table or view can have one INSTEAD OF trigger for each triggering action (UPDATE, DELETE, and INSERT)	A table can have several AFTER triggers for each triggering action.
2	INSTEAD OF triggers fire in place of the triggering action and before constraints are processed.	AFTER triggers fire after the triggering action (INSERT, UPDATE, or DELETE) and after any constraints are processed.

2. What are the differences between Views and User-Defined Functions?

S.No	Views	User-Defined Functions
1	Views cannot accept parameters.	User-Defined Functions can accept parameters.
2	Output of the Views cannot be directly used in the SELECT clause.	Output of the User-Defined Functions can be directly used in the SELECT clause.

3. What are the differences between Triggers and Stored Procedures?

S.No	Triggers	Stored Procedures
1	Triggers cannot return a value	Stored Procedures may return a value
2	We cannot pass parameters in Triggers	We can pass parameter in Stored Procedures
3	We can write a Stored procedure within a Trigger	We cannot write a Trigger within a Stored Procedure
4	Triggers are implicitly fired whenever insert, update or delete operations take place on table	Stored Procedures need to be explicitly called by the programmer
5	Triggers can only be implemented on Tables or Views	Stored procedures can be written for the Database
6	We cannot schedule a trigger.	Stored procedures can be scheduled through a job to execute on a predefined time
7	We cannot use the print command inside a trigger.	We can use the Print commands inside the stored procedure for debugging purpose
8	We cannot call a trigger from these files.	We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.)

Difference between DML Triggers and DDL Triggers

S.No	DML Triggers	DDL Triggers
1	Where it operates ? Operates on INSERT, UPDATE and DELETE.	Where it operates ? Operates on CREATE, DROP and ALTER.
2	Where it can be applied ? Applied on Tables and views.	Where it can be applied ? Applied on Databases and servers.
3	Whether it can be used as INSTEAD OF TRIGGERS ? Can be used as INSTEAD OF TRIGGERS.	Whether it can be used as INSTEAD OF TRIGGERS ? Cannot be used as INSTEAD OF TRIGGERS.
4	Whether Magic tables can be created ? Creates INSERTED and DELETED tables.	Whether Magic tables can be created ? Cannot create INSERTED and DELETED tables.
5	When it runs ? DML triggers run either Before or After a T-SQL statement is completed .	When it runs ? DDL triggers run only after a T-SQL statement is completed .