Know How SQL Server Works Using Stored Procedures & Triggers

Amongst databases, every database has some specific pre-defined functions which are used to execute a particular task on it. SQL server also uses these pre-defined functions to execute any task in its database. Similarly, Trigger is also a special kind of stored procedure .In SQL, Triggers automatically execute whenever an event occurs in the database .Below is a detailed description of Stored procedure and Triggers.

Stored Procedures in SQL server

On SQL server, the query execution for any operation (insert, delete, update…etc...) depends on some procedures. Such procedures are called Stored Procedures. Because these procedures are pre-defined in SQL server and they are the library functions of any program. Without Stored Procedures you cannot execute any task on SQL server.

When you perform or execute any task on SQL server, it doesn’t execute directly. Before the execution, it will be checked in the library of the function and if all the functions in the query are matched, then only the query will be executed. Otherwise, it shows an error.

A Stored Procedure is nothing more than an already defined SQL code that you save and can access it over and over again. So, to prevent writing an SQL code again and again and execute it, you can save it as a Stored Procedure instead. The following diagram shows how SQL works with stored procedures.

Types of Stored Procedure

1. User-defined
2. Temporary
3. System
4. Extended Stored Procedure

SQL server uses four types of stored procedure methods. below is a small description of each method.

1. User-defined

This type of procedure can be created in a user defined database or in all system databases, but cannot created in the resource database.

2. Temporary

Basically There are two types of temporary procedures are available in SQL server. First one is Local and second one is Global. A Local temporary stored procedure exists in the current session.

3. System

There are so many system stored procedure available in SQL server and it can be executed from any database without specification of database context.

4. Extended Stored Procedure

From Extended Stored Procedures you can create your choice external routines these stored procedures can dynamically load into the server and run easily.

Benefits of stored procedures

• Stored procedures are Reduce the network traffic between server and client.
• Maintain all the data structure and provide Stronger security.
• Provide security from SQL injection invasion.
• Stored procedures are pre-compiled statements and can be executed quickly.

Drawbacks

• Without visual studio it is difficult to debug the stored procedure.
• It is difficult to convert the stored procedure if it is written by the best developers.
• Stored procedures are slower than dynamic SQL’s.
• Business logic cannot be written at the database side.

When to use stored procedures

In some other systems, stored procedures may use to control the transaction management. Stored procedures mainly run inside of a transaction, such that transactions are effectively transparent to them. Stored procedures are just like with writing a good quality application if you can write a good quality stored procedure, then you can implement them otherwise, another solution might be best for you.

Issues with stored procedures

The stored procedures are very useful objects of SQL Server database and provide constitutive performance, abstraction, code rescue, reduced network traffic and it has some of its advantages. But there are a number of issues that are encountered while using the stored procedures.

Following Error message can be occur when performing a task on SQL Server:

Error 0: [SQL-DMO] Object 'dbo.SP_ProcName' was not scripted

In such condition, you cannot delete the stored procedure by selecting 'Delete' option. At this point, finding the cause of this issue and carrying out SQL Repair by resolving it becomes essential.

In such condition, you need to repair and restore damaged database file and retrieve data from it.

Manual method to recover Stored Procedures

1. Log on to the application-tier server.
2. Click Start and point to Control Panel and then click Add or Remove Programs.
3. Click Microsoft Visual Studio 2008 Team Foundation Server - LOC and then click on Change/Remove.
4. On the Maintenance Mode Options, click on Repair or Reinstall, and then click on Next.
5. On the Team Foundation Server Service Account page, check the type of account that is used for installation.
   • If it is specified to use a system account, then click on Next.
   • If Specify an account is specified, click the Password box, type the password for the specified account, and then click Next.
6. On the Reporting Services Data Source Account page, check the type of account that is used for installation.
   • If Use Team Foundation Server service account is specified, click Next.
   • If Specify an account is specified, click the Password box, type the password for the specified account, and then click Next.
7. On the Ready to Repair page, click Repair.

The Maintenance - Repair/Reinstall page shows installation progress.

You may ask to specify the location of the original installation media.

8. On the Maintenance - Setup Complete page, click Finish.
9. When prompted, click Restart Now, and wait for the server to restart.

The above method shows us how we can manually recover SQL servers lost Stored Procedures. Now we come to know about Triggers.

What is Trigger in SQL server?

As I told you before the SQL Trigger is an exclusive type of stored procedure which executes automatically whenever an event occurs in the SQL server database. It is special because it is called automatically not directly like a stored procedure.

The difference between a Trigger and a stored procedure is that whenever a DML or DDL event is made against the database a Trigger is called automatically whereas stored procedure must be called specifically.

Basic Syntax of Trigger is as follows

CREATE TRIGGER name ON table
   [WITH ENCRYPTION]
   [FOR/AFTER/INSTEAD OF]
   [INSERT, UPDATE, DELETE]
   [NOT FOR REPLICATION]
AS
BEGIN
--SQL statements
...
END

Types of Triggers in SQL server

There are three types of Triggers in SQL server mentioned below:

1. DDL Triggers
2. DML Triggers
3. LOGON Triggers

1. DDL Triggers

In SQL server database, whenever ALTER, CREATE, DROP...Etc operations are performed; the DDL Triggers are fired automatically. The DDL Triggers do not fire when an INSERT, UPDATE and DELETE operations are performed in the database. DDL Triggers used to create the database object schema.

2. DML Triggers

As opposed to DDL Triggers, DML Triggers do not execute in response to ALTER, CREATE, DROP, DENY, REVOKE, GRANT. First of all they execute in response to DML statements which include UPDATE, INSERT and DELETE statements on a table. These DDL statements, fire DML Triggers. DML triggers are often used for applying business rules and data integrity.

3. LOGON Triggers

These types of Triggers are executed when a LOGON event occurs on SQL server. LOGON Triggers often used to audit and control server sessions, by tracking login activity or extent the number of sessions for a specific login. This event executes when a user perform login operation on SQL server. This event depends on user’s login operation.

Triggers have some of its advantages and disadvantages. because Triggers come with some potential pitfalls. Here we can see the advantages and disadvantages of triggers:

Advantages of Triggers

• SQL, Triggers provide an option to check the integrity of data.
• Without SQL, Triggers you cannot insert any invalid data into database.
• Triggers are also used for calling stored procedures.
• You don’t need to invoke the Trigger. It executes automatically when an event occurs in the database.

Disadvantages of Triggers

• We can see all the executed events in SQL server but we cannot see Trigger’s execution event in SQL server.
• In case of transaction loss it is difficult to troubleshoot with Trigger.
• Triggers may increase the complexity of a database.

Triggers are very important to design a database because they are high maintenance database objects. But there are some cases, such as; sometimes you cannot see Triggers application on the desktop, although you see in Task Manager that the application is running.

Issue with Triggers

• Sometimes inside the Network Service account, The Message Queuing Triggers service runs by default; the desktop functionality, interaction is only possible with the Local System account.

Manual methods to solve the issue

• It is required to set up the Triggers service and switch to its interaction with desktop mode.
• Computer management > services > pane > Message Queuing Triggers(double click).
• Now Click the Log On tab and then allow service.

Conclusion

Triggers and Stored Procedures are very important parts of the database because these two primary events are one of many events in SQL server database.

If the both(Stored procedure and Triggers) recovery methods discussed above failed to resolve the problem, then use SysTools SQL Recovery Tool that helps to recover your SQL database quickly.