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.)