Answer / anil_abbireddy

1. Triggers are Table dependent,But Procedures are Not
2. We can not pass Parameters through Triggers,But in case
of sp we can pass no.of parameters
3. We can not execute triggers explicitly,but ps we can
4. we can not place transaction processing statments in
triggers(commit,collback,save point), but in ps. we can
5. we cannot overload triggers, but we can overload
functions & procedures using packages.

Answer / ravi singh

Procedures:
- A procedure is PL/SQL block which is used to process a value and need to execute explicitly.
- Procedure can be call when required and it don't have any dependency on any DML operations on any table.
- Procedure can be called from any area of the code.
- You can pass IN/OUT parameters to the procedure through which you can use the output of the procedures.

Triggers:
- Triggers are PL/SQL block which are based on the events and got executed on the happening of any DML event on the specific table.
- They cannot be called or you cannot stop them from execution.
- You can write commit in the triggers with the help of autonomous transaction only.
- You can refer the values of the table with whom the trigger is linked with the help of :new and :old variables.

Advantages and Disadvantages: They both are mentioned in the above two posted answers.
But one main advantage of triggers over procedures is if you want to perform any action on the DML event of any table you should use triggers as you dont need to make a seperate call for your code.
One main disadvantage of triggers you cannot stop them being executed if you want to do it you have to explicitly disable the trigger.

Answer / sohail

1.Procedure is a subrotine which completly utilises the
concept of stack.
2.Procedure can be called any time when required where as
trigger once created we cannot stop the trigger to fire
when not required.
3.triggers are fired implicitly where as procedure are
called explicitly by procedure name when required.
4.the p_code of procedure is stored in oracle SGA and can
be global to others.

Answer / jaya

1) Stored procedures can accept parameters and can return values. Triggers can neither accept parameters nor return values.

2) A Trigger is dependent on a table and the application has no control to not fire a trigger when not needed. On the other hand, a stored procedure can be called as needed.

3) Procedure runs only when one call them manually whereas a trigger runs when there is any activity(insert,update,delete) on table on which the trigger is written.
4) Firing of a stored procedure can be controlled whereas on the other hand trigger will get fired whenever any modification takes place on the table.

What is difference between nchar and nvarchar?

0 Answers  

---

What's the difference between a primary key and a clustered index?

0 Answers  

---

Write a procedure to return the month and the number of developers joined in each month (cursor )

2 Answers   Tech Mahindra,

---

How exception handling is done in advance pl/sql?

0 Answers  

---

need to split a string into separate values. eg. col1 col2 ---------- 100 - 'a,b,c' 200 - 'a,x,d,e' 300 - 'c' result: value count ------------- a - 2 b - 1 c - 2 etc.

2 Answers  

---

can we write stored function in out parameters? how to call through select statement? how to written more than one value plz give the exmple?

1 Answers  

---

How can you load microsoft excel data into oracle? : aql loader

0 Answers  

---

What are the data types allowed in a table?

3 Answers  

---

What does the sign mean in sql?

0 Answers  

---

a. Can you delete data from a View. b. If Yes, can you delete it if there are multiple tables c. If No, can you delete if there is single source table which is joining.

1 Answers   CGI,

---

What is lookup table in sql?

0 Answers  

---

What do you understand by case manipulation functions?

0 Answers  

---