SQL Server Triggers

Scenario:

• Stop a query from execution
• Know from the query was being executed.

Solution:
I was a bit skeptical about this. About the part where you execute a query from execution.
We knew we had to create a trigger for this, but how on earth would you stop a query from execution.
Turned out, SQL Server does provide a way to do that.

So let's take a look at the syntax of creating a trigger as put in on MSDN:

CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH  [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME  }

 ::=
  [ ENCRYPTION ]
  [ EXECUTE AS Clause ]

 ::=
  assembly_name.class_name.method_name

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH  [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <>  [ ; ] }

 ::=
  [ ENCRYPTION ]
  [ EXECUTE AS Clause ]

 ::=
  assembly_name.class_name.method_name

Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH  [ ,...n ] ]
{ FOR | AFTER } LOGON
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <>  [ ; ] }
 ::=
  [ ENCRYPTION ]
  [ EXECUTE AS Clause ]

 ::=
  assembly_name.class_name.method_name

The part in bold is what is responsible for getting the trick done.

You use INSTEAD OF when you don't want the query which fired the event to execute. And you mention the category the query falls under (the one that is to be stopped from execution) after INSTEAD OF.

So the Trigger become something like

CREATE TRIGGER trgr_Name
ON Tbl_Name
INSTEAD OF DELETE
AS
BEGIN
//Your code
END

Now, let's come to the second part. The part where you discover from which machine the query was fired.

And guess what? That has a simple enough syntax.

HOST_NAME() give you the name of the machine.
So all you have to do is declare a variable

DECLARE @Host_Name VARCHAR(20);
@Host_Name = HOST_NAME();

There was a whole piece of code for getting the IP from Host_Name, but I don't remember it exactly, so will put that when I find it. :)

The whole point of this post was so that I myself can find it when I need it again, as I don't really trust my memory for such important stuff. So no offense to all the people from whose websites I collected this material from.

Debugging stored procedures...

A while ago one of my friends told me about how she made a stored procedure and didn't debug it as she did not know how to, to which she had to hear "Yeh kaise programming hai?"

Hmmm so not knowing how to debug a stored procedure makes you a bad programmer.. huhh?

But whatever it means.. you should know how to do all the stuff that is provided in the softwares in order to fully explore the powers of the language being used.



So here I'll be talking about Debugging SQL stored procedures.

As I didn't know how to debug sps I too searched on the net and came up with many good articles.



Debugging stored procedures is very similar to debugging a function in Visual Studio. You set breakpoints, and then you execute the program in debug mode, whenever a breakpoint is hit the execution is paused so the developer can have a look at the current values of the variables etc.



I was thinking of writing the whole thing myself. But why waste my time and yours as well. I don't myself experienced enough to be writing all this. So I'll just put in the links that I found useful.



Debugging Stored Procedures in SQL Server 2000:



http://www.15seconds.com/Issue/050106.htm



This gives a detailed explanation of debugging stored procedures in SQL Server 2000.

I was only able to debug SPs from database that was on my own machine. I wasn't able to do the same by taking a connection from a database at a remote system. There must be some way of doing that but I don't know actually what that is. :)



Debugging Stored Procedures in SQL Server 2005:



http://aspnet.4guysfromrolla.com/articles/051607-1.aspx



In SQL Server 2005, you can't debug the stored procedures from within SQL Server 2005. But don't worry you can do it through Visual Studio 2005. I found this article pretty useful.

-------------

Do keep in mind that some people argue that using "PRINT" statement is much better than debugging. You can PRINT all the values you want to and let the procedure run normally, without debugging.But I would recommend that you give debugging a shot as well, and decide which one is better for you. It is also possible that in some situations debugging is just another headache you are buying for yourself, but in some situations it might prove to be very useful. In any case, as I said, you should know everything you can about a language or a software to exploit it's full power.

Happy Debuggin!! :)

Programming in SQL Server 2005

A lot of programming can be done within Stored Procedures. Also when you are writing scripts to accomplish a complex task, it can be done more easily with programming. You can easily loose your way writing sub-queries, and you can end up compromising the actual task with something close to it but not really it.


There are a lot of things a person needs to learn in order to get the needed results and fast. And for that you need to have a little more knowledge then SELECT.

I haven't been able to find any good tutorials that would take me from the basics of programming in SQL to higher levels. There are a bulk of resources for Oracle but none that I could find for SQL. Yeah, maybe I lack adequate searching skills but whatever the reason, I have been restrained to use Oracle PL-SQL guide for learning SQL.
Now what I am doing is that I read something from Oracle Guide and then I try it out in SQL Server 2005 and when it doesn't work I search for that particular topic or syntax equivalent in SQL.

And what I am going to do here at my blog is that I'll write small posts giving the syntax for programming in SQL (something beyond the basic Transaction-SQL statements).

I don't have much to offer, but well.......

Anyone who can give me a reason for why I didn't get any decent search results is always welcome to explain it to me through comment.


Declaring Variables:

Syntax:

DECLARE @variable_name data_type

http://msdn.microsoft.com/en-us/library/ms188927(SQL.90).aspx


Declaring Cursors:
I don't know how to exactly define a cursor. (After all I am no DB Programming Guru, just in my learning phase.)

But cursors can be used when you want to select a number of rows and then iterate through each row one at a time, or manipulate them in any way.

Here's the
Sytanx:

DECLARE cursor_name CURSOR FOR sql_stmt

Example:
DECLARE my_cur CURSOR FOR SELECT * FROM Table_1

You don't have to start the cursor name with '@'.

http://msdn.microsoft.com/en-us/library/ms180169(SQL.90).aspx

In order to execute the statement and populate cursor OPEN statement is used.

OPEN:

Syntax:
OPEN cursor_name

http://msdn.microsoft.com/en-us/library/ms190500(SQL.90).aspx

After the rows have been retrieved and stored in cursor, they can be worked on by using the FETCH statement.

FETCH:

Syntax:
FETCH position FROM cursor_name

where
position can be any of the following:
NEXT
PRIOR
FIRST
LAST

http://msdn.microsoft.com/en-us/library/ms180152(SQL.90).aspx


Note:
I know you may not find this information very useful. It's all just basic information, and my resource is too the internet, where anyone could get it easily. But as I said I too am in learning stages. I'll try to update my blog with the new things I learn.
And these are just an overview of possibilities. You get to know what things you can do, and then you can refer to the links I have provided to learn more about them.
This post is not in any way a tutorial, just a baby-tutorial, to provide you with guidelines.

References:
I am using the following book for Oracle Sytax:
PL/SQL
User’s Guide and Reference
Release 2 (9.2)
March 2002
Part No. A96624-01

The following links were referred to for SQL Syntax (Some of these have been stated above as well)

• http://msdn.microsoft.com/en-us/library/ms188927(SQL.90).aspx
• http://msdn.microsoft.com/en-us/library/ms180169(SQL.90).aspx
  
• http://msdn.microsoft.com/en-us/library/ms190500(SQL.90).aspx
  
• http://msdn.microsoft.com/en-us/library/ms180152(SQL.90).aspx
  

SQL Sever Tips and Tricks

http://sqltips.wordpress.com/

http://www.projectdmx.com/tsql/strcleanup.aspx

http://www.fmsinc.com/free/NewTips/SQL/SQLTip2.asp