what is stored procedure and types?

Stored procedures can help to improve web application performance and reduce database access traffic.

TYPES
User Defined stored procedure
Extended Stored procedure
System Stored Procedure

There are 4 types of Triggers

Row Triggers and Statement Triggers: A row trigger is fired each time the table is affected by the triggering statement.A statement trigger is fired once
on behalf of the triggering statement.

BEFORE and AFTER Triggers: BEFORE triggers run the trigger action before the triggering statement is run. AFTER triggers run the trigger action after the triggering statement is run.

INSTEAD OF Triggers:Instead of triggers fire instead of the operation that fires the trigger, so if you define an Instead of trigger on a table for the delete operation

Triggers on System Events and User Events

------------------------------------------------
Learn throughout life

Raj.Net, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

1) Stored Procedure is an Predefined collection of Sql Statements .
2) Its PreCompiled.
3) It will Reduce the client/Server Network Traffic.
4) Easy to Error Handling.
5) More Number of SQL Statements Can Execute the Single Stored Procedure.
6) Network Bandwith issus in your company u can use stored procedure.
7) Performance wise its good approach for Stored Procedure.


Types of Stored Procedure:

1) System Stored Procedure
2) user Defined Stored Procedure
3) Extended Stored Stored Procedure.
4) Remote Stored Procedure.
5) Temporary Stored procedure.

Trigger:

Trigger is an Self Contained block of Statements .that is Fired using Insert,Update,Delete Statements.

There are Two Types of Trigger Available:

1) Instead of Trigger
2) After Trigger


Hope this will Help

Regards
Sriram.R

sriram

Raj.Net, if this helps please login to Mark As Answer. | Alert Moderator

A stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific task..Stored procedures are special objects available in sql server. Its a precompiled statements where all the preliminary parsing operations are performed and the statements are ready for execution.Stored procedures can also improve performance

Syntax
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]

IS

Declaration section

BEGIN

Execution section

EXCEPTION

Exception section

END;

Benefits of Stored Procedures
Why should you use stored procedures? Let's take a look at the key benefits of this technology:

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.

Raj.Net, if this helps please login to Mark As Answer. | Alert Moderator

The classification of stored procedures is depends on the Where it is Stored. Based on this you can divide it in 4 sections.
1.System stored procedures
2.Local stored procedures
3.Temporary stored procedures
4.Extended stored procedures

1.System Stored Procedures:

System stored procedures are stored in the Master database and are typically named with a sp_ prefix. They can be used to perform variety of tasks to support SQL Server functions that support external application calls for data in the system tables, general system procedures for database administration, and security management functions.
For example, you can view the contents of the stored procedure by calling
sp_helptext [StoredProcedure_Name].

There are hundreds of system stored procedures included with SQL Server. For a complete list of system stored procedures, refer to "System Stored Procedures" in SQL Server Books Online.

2.Local stored procedures

Local stored procedures are usually stored in a user database and are typically designed to complete tasks in the database in which they reside. While coding these procedures don't use sp_ prefix to you stored procedure it will create a performance bottleneck. The reason is when you can any procedure that is prefixed with sp_ it will first look at in the mater database then comes to the user local database.

3.Temporary stored procedures

A temporary stored procedure is all most equivalent to a local stored procedure, but it exists only as long as SQL Server is running or until the connection that created it is not closed. The stored procedure is deleted at connection termination or at server shutdown. This is because temporary stored procedures are stored in the TempDB database. TempDB is re-created when the server is restarted.

4.Extended Stored Procedures

An extended stored procedure uses an external program, compiled as a 32-bit dynamic link library (DLL), to expand the capabilities of a stored procedure. A number of system stored procedures are also classified as extended stored procedures. For example, the xp_sendmail program, which sends a message and a query result set attachment to the specified e-mail recipients, is both a system stored procedure and an extended stored procedure. Most extended stored procedures use the xp_ prefix as a naming convention. However, there are some extended stored procedures that use the sp_ prefix, and there are some system stored procedures that are not extended and use the xp_ prefix. Therefore, you cannot depend on naming conventions to identify system stored procedures and extended stored procedures.

Raj.Net, if this helps please login to Mark As Answer. | Alert Moderator

Mark as Answer if it helpful to you..That helps other who search the same...

Raj.Net, if this helps please login to Mark As Answer. | Alert Moderator