What is difference between Trigger and a Stored Procedure?

Showing Answers 1 - 7 of 7 Answers

ashraf

  • Sep 3rd, 2006
 

TRIGGER

1- when you create a trigger you have to identify event and action of your trigger but when you create s.p you don't identify event and action

2-trigger is run automatically if the event is occured but s.p don't run automatically but you have to run it manually

3- within a trigger you can call specific s.p but within a s.p you cann;t call atrigger

  Was this answer useful?  Yes

Raj

  • Sep 20th, 2006
 

Actually triger in action which is performed automatically before or after a event occur and stored procedure is a procedure which is executed when the it is called. Stored procedure is module.

  Was this answer useful?  Yes

vishu7vishu

  • Feb 19th, 2007
 

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.

  Was this answer useful?  Yes

gtomar

  • Jul 25th, 2008
 

Trigger :- It is a type of stored procedure invoked automatically for a predefined DML operation .cannot accept parameters.

Stored Procedure :- It includes trigger ,function .Stored procedure(Function and procedure)  need to execute explicitly by using EXEC Procedure name/ DML query.It can accept parameters.

  Was this answer useful?  Yes

Shubhangi Gaikwad

  • Jul 23rd, 2011
 

Trigger is calling Implicitly but Stored procedure is explicitly. Stored procedure is not accept a parameter, but in trigger is passing or accepting a parameter.

  Was this answer useful?  Yes

Rahul

  • Aug 9th, 2011
 

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.

  Was this answer useful?  Yes

alok

  • Sep 15th, 2011
 

triggers start their execution when given condition or event is met in prog. but a stored procedure occurs when a programmer make call to it .

  Was this answer useful?  Yes

vikas pathak

  • Apr 11th, 2015
 

store procedure can accept parameter while a trigger can not

  Was this answer useful?  Yes

Nupur

  • Apr 25th, 2015
 

Trigger is a block of Code that fires because of any event in the database. Triggers are stored in a different area in the database. It can fire implicitly because of some insert update or delete statement .you cannot directly call it. Triggers are generally used to check for values before insert update or delete Stored procedures are named blocks of code that are called by the user when the program needs to use it.

Some procedures return value and functions will return value.

  Was this answer useful?  Yes

qptopm

  • Nov 3rd, 2016
 

Commit cannot be done in case of triggers except for autonomous transactions. When you create a trigger you have to identify event and action of your trigger. Triggers are implicitly called by DB itself

  Was this answer useful?  Yes

Ashwini

  • Jan 25th, 2018
 

Procedure -
Definition - Procedure is a program unit or a module that performs a particular task.
Execution - A stored procedure is executed explicitly by issuing procedure call
Parameters - Stored procedure can take input parameters
Syntax -
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
Trigger
Definition - Triggers are stored programs, which are automatically executed or fired when some events occurs
Execution - The trigger is executed implicitly whenever any triggering event occurs
Parameters - Trigger cannot take input parameters
Syntax -
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions