Answer / gangi naidu.k

In case of sql queries,it is possible to compile one query
at a time,but incase of stored procedure we can compile
bunch of queries at a time and also it is need at first
time only,suppose we need modification on queries,after
modifications no need to compile again in case of stored
procedures,but in case of sql queries we have to compile
every time.Stored procedure contain queries in compiled
format so execution also fast and tome saving.One more
thing is in case of stored procedures serurity also more.

Answer / amar goala

The main differnce is that S.P. is explicitly calling for
execute else Onece a Trigger is created Its fired
automatically when any event
occurred like update,insert,delete on table in run time.

Answer / vikas sinha

1)Stored Procedure can call.but trigger is automatically
invoked when action defined in trigger is done.

2)Stored procedure can not be inactive.but trigger can be
active and inactive.

3)Stored Procedure may or may not reture a value.but
trigger will not return a value.

Answer / j.shantha kumari

Set of Sql statements called stored procedure.

The SQL CREATE TRIGGER statement provides a way for the
database management system to actively control, monitor, and
manage a group of tables whenever an insert, update, or
delete operation is performed. The statements specified in
the SQL trigger are executed each time an SQL insert,
update, or delete operation is performed. An SQL trigger may
call stored procedures or user-defined functions to perform
additional processing when the trigger is executed.

Answer / thirupathi

1. Triggers are implicitly called by DB itself while SP has to be manually called by user.

2. SP can pass the parameters which is not a case with Triggers.

3. While creating a Trigger, triggering event n action has to be specified, which isn’t a case with SP.

4. A Trigger can call the specific SP in it but the reverse is not true.

Answer / veerababu

Stored Procedure is a method.
Trigger is a Procedure.
Stored Procedure a specific CALL or EXECUTE has be used.
Trigger is automatically execute some specific events occure in table/views.
Stored Procedure return a Value.
Trigger is not return a value.

Answer / manoj kopardekar

STORED PROCEDURE
A stored procedure is an already written SQL statement that
is saved in the database. We can run the stored procedure
from the database's command environment

1.Precompiled execution. SQL Server compiles each stored
procedure once and then reutilizes the execution plan. This
results in tremendous performance boosts when stored
procedures are called repeatedly.
2.Reduced client/server traffic. If network bandwidth is a
concern in your environment, you'll be happy to learn that
stored procedures can reduce long SQL queries to a single
line that is transmitted over the wire.
3.Efficient reuse of code and programming abstraction.
Stored procedures can be used by multiple users and client
programs. If you utilize them in a planned manner, you'll
find the development cycle takes less time.
4.Enhanced security controls. You can grant users permission
to execute a stored procedure independently of underlying
table permissions.


TRIGGER

A trigger is an object contained within an SQL Server
database that is used to execute a batch of SQL code
whenever a specific event occurs. As the name suggests, a
trigger is “fired” whenever an INSERT, UPDATE, or DELETE SQL
command is executed against a specific table.

Answer / bibhudatta panda

Triggers

Triggers provide a way of executing PL/SQL code on the occurrence of specific database events. For example, you can maintain an audit log by setting triggers to fire when insert or update operations are carried out on a table. The insert and update triggers add an entry to an audit table whenever the table is altered.

The actions that Informix Dynamic Server triggers perform are constrained to multiple insert, update, delete, and execute procedure clauses; whereas, Oracle allows triggers to execute arbitrary PL/SQL code. Oracle triggers are similar to stored procedures in that they can contain declarative, execution, and exception handling code blocks.

Additionally, Oracle enables triggers to be invoked by many events other than table insert, update and delete operations. However, there are restrictions.

Stored Procedures

Stored procedures provide a powerful way to code application logic that can be stored on the server. Informix Dynamic Server and Oracle both use stored procedures. Oracle also uses an additional type of subprogram called a function.

The language used to code stored procedures is a database-specific procedural extension of SQL. In Oracle it is PL/SQL and in Informix Dynamic Server it is Informix Dynamic Server Stored Procedure Language (SPL). These languages differ considerably. However, most of the individual SQL statements and the procedural constructs, such as if-then-else, are similar in both languages.

Answer / giliweed

they spell different .....dahhh!
one is "T R E G G E R" and another is "P R O C E D U R E".
you people make simple things too difficult.

:D

Answer / ajay panchal

Triggers are automatically run but stored procedures are not automatically run and they have to be called explicitly by the user.

we can write a stored procedure within a trigger but cannot write a trigger within a stored procedure.

Trigger is attached to table or view and is fired only when an INSERT, UPDATE, and/or DELETE occurs, while a stored procedure executes at any time when it is called.

How do I find the size of a sql server database?

0 Answers  

What is check constraint in sql server?

0 Answers  

Do you know what is sql service broker?

0 Answers  

How to use wildcard characters in like operations in ms sql server?

0 Answers  

How do you simulate a deadlock for testing purposes

1 Answers  

can an order by clause be used in a creation of a view?

0 Answers  

How do I find the port number for sql server?

0 Answers  

What information is maintained within the msdb database?

0 Answers  

When columns are added to existing tables, what do they initially contain?

0 Answers  

Why do we use non clustered index?

0 Answers  

What is order of B+tree?

0 Answers   HCL,

Explain Trigger with an example?

2 Answers