通过insert型触发器对员工数据进行添加操作

SQL

View Code

/*打开数据库*/
USE db_Tome2
/*判断表中是否有名为“基本工资触发器”的触发器*/
IF EXISTS (SELECT name 
    FROM   sysobjects 
    WHERE  name = '员工工资触发器' 
    AND type = 'TR')
/*如果已经存在则删除*/
DROP TRIGGER 员工工资触发器
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/*创建新的触发器*/
CREATE  TRIGGER 员工工资触发器 ON dbo.[employeeInfo03]
FOR INSERT
AS
/*参数声明*/
DECLARE @eID int,@eName varchar(50),@ePay decimal(9,2)
/*给参数赋值*/
set  @ePay= 1000
/*应用触发器中的INSERTED表*/
select @eID=EmpID,@eName=EmpName From INSERTED
insert into employeePay03(employeeID,employeeName,employeePay)values(@eID,@eName,@ePay)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
--测试
insert into employeeInfo03 (EmpID,EmpName,Number,Phone)values(1,'撒旦','220104','1345678')
delete from employeeInfo03 where EmpName='撒旦'

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>
        &nbsp;<table align="center" border="0" cellpadding="0" cellspacing="0" style="width: 533px;
            height: 302px; border-left-color: #ff00ff; border-bottom-color: #ff00ff; border-top-style: ridge; border-top-color: #ff00ff; border-right-style: ridge; border-left-style: ridge; border-right-color: #ff00ff; border-bottom-style: ridge;" background="图片2 拷贝.jpg">
            <tr>
                <td colspan="2" style="height: 130px" width="100%">
                    <table border="0" cellpadding="0" cellspacing="0" width="100%" background="图片2 拷贝.jpg">
                        <tr>
                            <td align="center" colspan="1" style="width: 169px; background-image: none; " rowspan="6">
                            </td>
                            <td align="center"colspan="2">
                                <span style="font-size: 14pt; color: red"><strong>员工基本信息注册</strong></span></td>
                        </tr>
                        <tr>
                            <td align="right" style="width: 100px; height: 19px">
                                <span style="font-size: 10pt">&nbsp; 员工编号：</span></td>
                            <td style="width: 100px; height: 19px">
                                <asp:TextBox ID="TextID" runat="server"></asp:TextBox></td>
                        </tr>
                        <tr>
                            <td align="right" style="width: 100px">
                                <span style="font-size: 10pt">&nbsp; 员工姓名：</span></td>
                            <td style="width: 100px">
                                <asp:TextBox ID="TextName" runat="server"></asp:TextBox></td>
                        </tr>
                        <tr>
                            <td align="right" style="width: 100px; height: 19px">
                                <span style="font-size: 10pt">&nbsp;身份证号：</span></td>
                            <td style="width: 100px; height: 19px">
                                <asp:TextBox ID="TextNumber" runat="server"></asp:TextBox></td>
                        </tr>
                        <tr>
                            <td align="right" style="width: 100px">
                                <span style="font-size: 10pt">&nbsp; 联系电话：</span></td>
                            <td style="width: 100px">
                                <asp:TextBox ID="Textphone" runat="server"></asp:TextBox></td>
                        </tr>
                        <tr>
                            <td align="center" colspan="2" style="height: 24px">
                                <asp:Button ID="Button1" runat="server" Text="注册" Width="65px" OnClick="Button1_Click" Font-Size="9pt" /></td>
                        </tr>
                    </table>
                </td>
            </tr>
            <tr>
                <td style="width: 37px; height: 127px;">
                    &nbsp;<asp:GridView ID="GridView3" runat="server" AutoGenerateColumns="False" BackColor="#DEBA84"
                        BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2"
                        Font-Size="9pt" Width="322px">
                        <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
                        <Columns>
                            <asp:BoundField DataField="EmpId" HeaderText="员工编号" />
                            <asp:BoundField DataField="EmpName" HeaderText="员工姓名" />
                            <asp:BoundField DataField="Number" HeaderText="身份证号" />
                            <asp:BoundField DataField="Phone" HeaderText="电话" />
                        </Columns>
                        <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
                        <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
                        <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
                        <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
                    </asp:GridView>
                </td>
                <td style="width: 146px; height: 127px;">
                    &nbsp;<asp:GridView ID="GridView4" runat="server" AutoGenerateColumns="False" BackColor="LightGoldenrodYellow"
                        BorderColor="Tan" BorderWidth="1px" CellPadding="2" Font-Size="9pt" ForeColor="Black"
                        GridLines="None" Height="182px" Width="177px">
                        <FooterStyle BackColor="Tan" />
                        <Columns>
                            <asp:BoundField HeaderText="员工编号" DataField="employeeID" />
                            <asp:BoundField HeaderText="员工姓名" DataField="employeeName" />
                            <asp:BoundField HeaderText="工资" DataField="employeePay" />
                        </Columns>
                        <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
                        <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
                        <HeaderStyle BackColor="Tan" Font-Bold="True" />
                        <AlternatingRowStyle BackColor="PaleGoldenrod" />
                    </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)
        {
            this.loadGridView1();
            this.loadGridView2();
        }
    }
    protected void loadGridView1()
    {
        SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["strCon"]);

        try
        {
            con.Open();
            string sql = "select EmpID,EmpName,Number,Phone from employeeInfo03";
            SqlDataAdapter sda = new SqlDataAdapter(sql, con);
            DataSet ds = new DataSet();
            sda.Fill(ds, "employeeInfo");
            GridView3.DataSource = ds;
            GridView3.DataBind();

        }
        catch (Exception error)
        {
            Response.Write(error.Message.ToString());
        }
        finally
        {

            con.Close();
        }
    }
    protected void loadGridView2()
    {
        SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["strCon"]);

        try
        {
            con.Open();
            string sql = "select employeeID,employeeName,employeePay from employeePay03";
            SqlDataAdapter sda = new SqlDataAdapter(sql, con);
            DataSet ds = new DataSet();
            sda.Fill(ds, "employeeInfo");
            GridView4.DataSource = ds;
            GridView4.DataBind();

        }
        catch (Exception error)
        {
            Response.Write(error.Message.ToString());
        }
        finally
        {

            con.Close();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["strCon"]);
        con.Open();
        string str = "select count(*) from employeeInfo03 where EmpID='" + TextID.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 = "insert into employeeInfo03(EmpID,EmpName,Number,Phone)values('" + TextID.Text + "','" + TextName.Text + "','" + TextNumber.Text + "','" + Textphone.Text + "')";
            try
            {
                SqlCommand com = new SqlCommand(sql, con);
                com.ExecuteNonQuery();
                Response.Write("<script language=javascript>alert('插入成功')</script>");
            }
            catch (Exception error)
            {
                Response.Write(error.Message.ToString());
            }
            finally
            {
                con.Close();  //断开连接
                loadGridView1();  //在GridView1里显示员工信息的方法；
                loadGridView2();  //在GridView2里显示员工工资的方法；
            }
        }
    }
}

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