通过Update型触发器对管理员信息进行修改

使用Update型触发器时，更新操作包括两部分，即先将更新的内容去掉，然后将新值插入。因此，对于一个定义了update触发器的表来讲，当进行更新操作时，在删除表中存放了旧值，然后在插入表中存放新值

SQL

View Code

/*打开数据库*/
USE db_Tome2
/*判断表中是否有名为“基本工资触发器”的触发器*/
IF EXISTS (SELECT name 
    FROM   sysobjects 
    WHERE  name = 'trigger_systemLog' 
    AND type = 'TR')
/*如果已经存在则删除*/
DROP TRIGGER trigger_systemLog
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/*创建新的触发器*/
CREATE   trigger trigger_systemLog--触发器名称
ON admInfo03
FOR update
AS
/*参数声明*/
DECLARE @AN varchar(50),@AP varchar(50),@MD datetime
/*参数赋值*/
set @MD=getdate()
/*应用触发器中的INSERTED表临时存储参数*/
select @AN='用户名：'+admName+'---密码修改为'+admPass From INSERTED
/*SQL语句操作*/
insert into systemLog (modifyDate,remark) values (@MD,@AN)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON

Default.aspx

View Code

<%@ 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 id="Head1" runat="server">
    <title>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table align="center" background="图片2 拷贝.jpg" border="0" cellpadding="0" cellspacing="0"
            style="width: 473px; height: 275px">
            <tr>
                <td colspan="2" style="height: 103px" width="100%">
                    <table background="图片2 拷贝.jpg" border="0" cellpadding="0" cellspacing="0" width="100%">
                        <tr>
                            <td align="center" colspan="1" rowspan="5" style="background-image: none; width: 169px">
                            </td>
                            <td align="center" colspan="2">
                                <span style="font-size: 9pt; color: red"><strong>修改管理员密码</strong></span></td>
                        </tr>
                        <tr>
                            <td align="right" style="width: 100px; height: 19px">
                                <span style="font-size: 9pt">&nbsp; </span></td>
                            <td style="width: 100px; height: 19px">
                                </td>
                        </tr>
                        <tr>
                            <td align="right" style="width: 100px; height: 24px;">
                                <span style="font-size: 9pt">管理员名称：</span></td>
                            <td style="width: 100px; height: 24px;">
                                <asp:TextBox ID="TextName" runat="server"></asp:TextBox></td>
                        </tr>
                        <tr>
                            <td align="right" style="width: 100px; height: 19px">
                                <span style="font-size: 9pt">&nbsp;管理员密码：</span></td>
                            <td style="width: 100px; height: 19px">
                                <asp:TextBox ID="TextPass" runat="server"></asp:TextBox></td>
                        </tr>
                        <tr>
                            <td align="right" style="width: 100px; height: 19px;">
                                <span style="font-size: 9pt"></span></td>
                            <td style="width: 100px; height: 19px;">
                                <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="修改密码" Width="65px" /></td>
                        </tr>
                    </table>
                    &nbsp;
                    <asp:Label ID="Label1" runat="server" BackColor="#FFC080" Font-Size="9pt" Text="修改后的信息显示如下:"></asp:Label></td>
            </tr>
            <tr>
                <td style="width: 106px; height: 226px">
                    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="190px" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" Font-Size="9pt">
                        <Columns>
                            <asp:BoundField DataField="admName" HeaderText="管理员名称">
                                <HeaderStyle Font-Size="12pt" />
                                <ItemStyle Font-Size="10pt" />
                            </asp:BoundField>
                            <asp:BoundField DataField="admPass" HeaderText="管理员密码">
                                <HeaderStyle Font-Size="12pt" />
                                <ItemStyle Font-Size="10pt" />
                            </asp:BoundField>                           
                        </Columns>
                        <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
                        <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
                        <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
                        <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
                        <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
                    </asp:GridView>
                </td>
                <td style="width: 100px; height: 226px">
                    <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" BackImageUrl="~/背景图10.jpg"
                        Width="270px" CellPadding="4" ForeColor="#333333" GridLines="None" Font-Size="9pt">
                        <Columns>
                            <asp:BoundField DataField="modifyDate" HeaderText="修改日期">
                                <HeaderStyle Font-Size="12pt" />
                                <ItemStyle Font-Size="10pt" />
                            </asp:BoundField>
                            <asp:BoundField DataField="remark" HeaderText="备注">
                                <HeaderStyle Font-Size="12pt" />
                                <ItemStyle Font-Size="10pt" />
                            </asp:BoundField>                           
                        </Columns>
                        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                        <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                        <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
                        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
                        <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                        <AlternatingRowStyle BackColor="White" />
                    </asp:GridView>
                </td>
            </tr>
        </table>
    
    </div>
    </form>
</body>
</html>

Default.aspx.cs

View Code

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            loadGridView1();
            loadGriView2();
        }
    }
    protected void loadGridView1()
    {
        SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["strCon"]);
        con.Open();
        SqlDataAdapter sda = new SqlDataAdapter("select * from admInfo03", con);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        GridView1.DataSource = ds;
        GridView1.DataBind();
        con.Close();
    }
    protected void loadGriView2()
    {
        SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["strCon"]);
        con.Open();
        SqlDataAdapter sda = new SqlDataAdapter("select * from systemLog03", con);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        GridView2.DataSource = ds;
        GridView2.DataBind();
        con.Close();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["strCon"]);
        con.Open();
        string str = "select count(*) from admInfo03 where admPass='" + TextPass.Text.ToString() + "'";
        SqlCommand mycom = new SqlCommand(str, con);
        int intcont = Convert.ToInt32(mycom.ExecuteScalar());
        if (intcont > 0)
        {
            Response.Write("<script language=javascript>alert('对不起!您已经修改了此密码！')</script>");
            return;
        }
        else
        {
            string sql = "update admInfo03 set admPass='" + TextPass.Text + "' where admName='" + TextName.Text + "'";
            try
            {
                SqlCommand com = new SqlCommand(sql, con);
                com.ExecuteNonQuery();
                Response.Write("<script language=javascript>alert('修改密码成功！')</script>");
            }
            catch (Exception error)
            {
                Response.Write(error.ToString());
            }
            finally
            {
                con.Close();
                loadGridView1();//在GridView1里显示管理员信息的方法；
                loadGriView2();//在GridView2里显示系统日志的方法；
            }
        }
    }
}

posted on 2013-03-25 00:56 松竹梅阅读(526) 评论(0) 编辑收藏举报