SQL Server Difference FAQs-2
- 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.
- 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.
- 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 6We cannot schedule a trigger.Stored procedures can be scheduled through a job to execute on a predefined time 7We cannot use the print command inside a trigger.We can use the Print commands inside the stored procedure for debugging purpose 8We 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.NoDML TriggersDDL Triggers1Where it operates ?Operates on INSERT, UPDATE and DELETE.Where it operates ?Operates on CREATE, DROP and ALTER. 2Where it can be applied ?Applied on Tables and views.Where it can be applied ?Applied on Databases and servers. 3Whether 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. 4Whether Magic tables can be created ?Creates INSERTED and DELETED tables.Whether Magic tables can be created ?Cannot create INSERTED and DELETED tables. 5When 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 .