触发器的使用
触发器的使用
一、格式：
CREATE TRIGGER TriggerName
ON dbo.Task
FOR /* INSERT, UPDATE, DELETE */
AS
/* IF UPDATE () ...*/
二、经典例子：
1、ALTER TRIGGER StaffTrigger
ON dbo.Staff
FOR INSERT, UPDATE, DELETE/*设置当何种操作进行时引起触发*/
AS
/*Update Trigger*/
IF UPDATE (StaffName) select NameResult= 'StaffName Modified'/*当对字段StaffName更新时*/
if update(RealName) select 'RealName Modified'
if update(Email) select 'Email Modified'
if update(Birthday) select 'Birthday Modifed'
if update(Password) select 'Password Modifed'
2、ALTER TRIGGER DeteleTrigger
ON dbo.Staff
FOR DELETE
AS
/*Delede Trigger*/
/*同时删除与要删除项有关系的数据*/
declare @StaffID int
select @StaffID=StaffID from Deleted /*Deleted表示要删除的缓冲表*/
DELETE FROM Task
WHERE (StaffID = @StaffID)
三、注意：
Deleted 要删除或更新的临时表，注意没有updated
Inserted 要插入的临时表
四、页面部分：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
更新前:<br />
<asp:GridView ID="gridBeforeUpdate" runat="server">
</asp:GridView>
StaffID:<asp:TextBox ID="txtStaffID" runat="server"></asp:TextBox>
StaffName:<asp:TextBox ID="txtStaffName" runat="server"></asp:TextBox><br />
<asp:Button ID="btnUpdate" runat="server" OnClick="btnUpdate_Click" Text="Update" /> <asp:Button
ID="txtDelete" runat="server" OnClick="txtDelete_Click" Text="Delete" />
<br />
<asp:Button ID="btnDeleteByName" runat="server" OnClick="btnDeleteByName_Click" Text="DeleteByName" /><br />
<br />
更新后:<br />
<asp:GridView ID="gridAfterUpdate" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
五、代码部分：
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataSet ds = new DataSet();
ds = this.GetStaffData();
this.gridBeforeUpdate.DataSource = ds;
this.gridBeforeUpdate.DataBind();
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
string connStr = ConfigurationManager.ConnectionStrings["StaffDBConnectionString1"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
string cmdString = "UPDATE Staff SET StaffName = '"+this.txtStaffName.Text+"' where(StaffID='"+Int32.Parse(this.txtStaffID.Text)+"')";
SqlCommand cmd = new SqlCommand(cmdString, conn);
int affectedRows = cmd.ExecuteNonQuery();//使用executeNonQuery()
string updateResult = cmd.ExecuteScalar().ToString();//使用executeScalar()
string updateResultbydr=string.Empty;//使用executeReader()
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
updateResultbydr = dr["NameResult"].ToString();
}
ClientScript.RegisterClientScriptBlock(typeof(Page), "", "alert('" + affectedRows.ToString() + "\\n" + updateResult + "\\n" + updateResultbydr + "')", true);
//Response.Write(updateResult);
conn.Close();
DataSet ds = this.GetStaffData();
this.gridAfterUpdate.DataSource = ds;
this.gridAfterUpdate.DataBind();
}
public DataSet GetStaffData()
{
string connStr = ConfigurationManager.ConnectionStrings["StaffDBConnectionString1"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from Staff", conn);
DataSet ds = new DataSet();
da.Fill(ds);
da.Dispose();
conn.Close();
return ds;
}
protected void txtDelete_Click(object sender, EventArgs e)
{
string connStr = ConfigurationManager.ConnectionStrings["StaffDBConnectionString1"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
string cmdString = "DELETE FROM Staff WHERE (StaffID='" + Int32.Parse(this.txtStaffID.Text) + "')";
SqlCommand cmd = new SqlCommand(cmdString, conn);
int affectedRows = cmd.ExecuteNonQuery();//使用executeNonQuery()
ClientScript.RegisterClientScriptBlock(typeof(Page), "", "alert('同时删除了Task表中与Staff表有关系的项\\n共删除的项数为(包括所有被删除的项):" + affectedRows.ToString() + "')", true);
conn.Close();
DataSet ds = this.GetStaffData();
this.gridAfterUpdate.DataSource = ds;
this.gridAfterUpdate.DataBind();
}
protected void btnDeleteByName_Click(object sender, EventArgs e)
{
string connStr = ConfigurationManager.ConnectionStrings["StaffDBConnectionString1"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
string cmdString = "DELETE FROM Staff WHERE (StaffName like'" + this.txtStaffName.Text.ToString() + "')";
SqlCommand cmd = new SqlCommand(cmdString, conn);
int affectedRows = cmd.ExecuteNonQuery();//使用executeNonQuery()
ClientScript.RegisterClientScriptBlock(typeof(Page), "", "alert('同时删除了Task表中与Staff表有关系的项\\n共删除的项数为(包括所有被删除的项):" + affectedRows.ToString() + "')", true);
conn.Close();
DataSet ds = this.GetStaffData();
this.gridAfterUpdate.DataSource = ds;
this.gridAfterUpdate.DataBind();
}
}
