[转]SQL Triggers & Stored Procedures

Stored Procedures and triggers within a database are similar constructs. They can both perform the same SQL statements. The biggest difference between the two is how they are executed. A stored procedure has to be executed by a user, while a trigger is executed by the system as the result of an event. Events that cause triggers to be activated include data inserts, updates and deletes. One drawback to using triggers instead of stored procedures is that they cannot accept parameters.

Instructions:

Stored Procedures

A stored procedures is a pre-defined, reusable routine that is stored in a database.  SQL Server compiles stored procedures, which makes them more efficient to use.  Therefore, rather than dynamically building queries in your code, you can take advantage of the reuse and performance benefits of stored procedures.

• 1

  Use the statement below to create a basic stored procedure with no parameters.

  CREATE PROCEDURE mySproc
  AS
  BEGIN
  -- SQL Statements
  END

• 2

  Create a stored procedure with parameters by copying the code below. To pass a parameter with a default value, include an equals sign and the value desired.

  CREATE PROCEDURE mySproc
  {
  @Param1 varchar(50),
  @Param2 datetime = NULL,
  @Param3 int = 0
  }
  AS
  BEGIN
  -- SQL Statements
  END

• 3

  Update or modify a stored procedure by using the "Alter" command from the example below.

  ALTER PROCEDURE mySproc
  AS
  BEGIN
  -- SQL Statements
  END

• 4

  Remove a stored procedure from the database with the "Drop" command as shown below.

  DROP PROCEDURE mySproc;

• 5

  Execute your stored procedure within SQL by running the commands below. If you include parameters, put them in the order that is expected by the stored procedure. Any strings passed in should be in single quotes.

  Example with no parameters:
  EXEC dbo.mySproc;

  Example with parameters:
  EXEC dbo.mySproc 'string data', '1/1/1900', 1;

Triggers

• 1

  Add a basic trigger by copying the code below.

  CREATE TRIGGER myTrigger
  ON myTable
  AFTER INSERT
  AS
  BEGIN
  -- SQL Statements
  END

• 2

  Make a trigger that will run after multiple events with the commands below.

  CREATE TRIGGER myTrigger
  ON myTable
  AFTER INSERT, UPDATE, DELETE
  AS
  BEGIN
  -- SQL Statements
  END

• 3

  Modify your trigger using the "Alter" command.

  ALTER TRIGGER myTrigger
  ON myTable
  AFTER INSERT
  AS
  BEGIN
  -- SQL Statements
  END

• 4

  Execute the statements from a trigger instead of the commands that cause the event by using the "Instead Of" command. For example, if mySproc updated the table myTable, it would cause an UPDATE event. If you had a trigger set up to catch that event that included an "Instead Of" command, the code from the trigger would run, and the code from the stored procedure would never execute.

  CREATE TRIGGER myTrigger
  ON myTable
  INSTEAD OF UPDATE
  AS
  BEGIN
  -- SQL Statements
  END

• 5

  Remove your trigger from the database with the "Drop" command as shown below.

  DROP TRIGGER myTrigger;

 

Read more: Tutorial on SQL Triggers & Stored Procedures | eHow.com http://www.ehow.com/how_6521501_tutorial-sql-triggers-stored-procedures.html#ixzz1I9tBMHZk

See more info on MSDN:

http://msdn.microsoft.com/en-us/library/ms187926(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms189799.aspx

 

How to use them?

Stored Procedures

• In addition to commands built with strings, the SqlCommand type can be used to execute stored procedures.  There are two tasks require to make this happen: let the SqlCommand object know which stored procedure to execute and tell the SqlCommand object that it is executing a stored procedure.  These two steps are shown below:

	// 1.  create a command object identifying
	//     the stored procedure
	SqlCommand cmd  = new SqlCommand(
		"Ten Most Expensive Products", conn);

	// 2. set the command object so it knows
	//    to execute a stored procedure
	cmd.CommandType = CommandType.StoredProcedure;

While declaring the SqlCommand object above, the first parameter is set to "Ten Most Expensive Products".  This is the name of a stored procedure in the Northwind database.  The second parameter is the connection object, which is the same as the SqlCommand constructor used for executing query strings.

The second command tells the SqlCommand object what type of command it will execute by setting its CommandType property to the StoredProcedure value of the CommandType enum.  The default interpretation of the first parameter to the SqlCommand constructor is to treat it as a query string.  By setting the CommandType to StoredProcedure, the first parameter to the SqlCommand constructor will be interpreted as the name of a stored procedure (instead of interpreting it as a command string).  The rest of the code can use the SqlCommand object the same as it is used in previous lessons.

• Sending Parameters to Stored Procedures

Using parameters for stored procedures is the same as using parameters for query string commands.  The following code shows this:

	// 1.  create a command object identifying
	//     the stored procedure
	SqlCommand cmd  = new SqlCommand(
		"CustOrderHist", conn);

	// 2. set the command object so it knows
	//    to execute a stored procedure
	cmd.CommandType = CommandType.StoredProcedure;

	// 3. add parameter to command, which
	//    will be passed to the stored procedure
	cmd.Parameters.Add(
		new SqlParameter("@CustomerID", custId));

The SqlCommand constructor above specifies the name of a stored procedure, CustOrderHist, as its first parameter.  This particular stored procedure takes a single parameter, named @CustomerID.  Therefore, we must populate this parameter using a SqlParameter object.  The name of the parameter passed as the first parameter to the SqlParameter constructor must be spelled exactly the same as the stored procedure parameter.  Then execute the command the same as you would with any other SqlCommand object.

Copy from: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

Triggers

We cannot call a trigger from C# code. The trigger will be triggered automatically when DML, DDL or logon event happens.

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established. Triggers can be created directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server