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.