Difference Between Stored Procedures and Triggers

Learn what is the difference between a SQL Server stored procedure and trigger.

A stored procedure in SQL Server is a collection SQL statements and sql command logic, which is compiled and stored on the database server. The use of stored procedures to implement SQL queries direct on the server itself rather than in the code and remove use of embedded SQL queries. Stored procedured can also improve performance of an application.

 

SQL CREATE PROCEDURE command is used to create a stored procedure. Here is the syntax of a stored procedure: 

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

If you're new to stored procedures, learn here Create and Execute Stored Procedures In SQL Server.

A SQL triggers are database objects similar to stored procedures. The difference is, triggers are set and fired (executed) on specific events such as when a record is inserted into a table, a trigger can be fired and the SQL query written in the trigger will be executed.

 

The CREATE TRIGGER SQL command is used to create a new trigger on a table. 

create trigger saftey
on database
for
create_table,alter_table,drop_table
as
print'you can not create ,drop and alter table in this database'
rollback;

If you're new to triggers, learn more here, Triggers In SQL Server.

 

Here are some of the key differences between SQL stored procedures and triggers. 

1. When you create a trigger, you must identify an event and action of your trigger when the trigger will be executed. Stored procedures are just SQL statements that don't require an event and action. Stored procedures can be called direct from an application or within other SQL commands including other stored procedures.
2. A trigger runs automatically when the event is fired. A stored procedure is executed manually or from a caller application.
3. Within a trigger you can call stored procedures but you cannot call a trigger from a stored procedure.
4. A trigger executes implicitly whereas a stored procedure is executed via a procedure call from another block.
5. We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger.
6. A stored procedure can take the input parameters, but we can't pass input parameters to a trgger.

Learn more here: 

• Create and Execute Stored Procedures In SQL Server. 
• Triggers In SQL Server.