创建触发器

最新推荐文章于 2023-06-11 20:34:48 发布

小不点

最新推荐文章于 2023-06-11 20:34:48 发布

阅读量375

点赞数

分类专栏：技術文章标签： dataset string exception encoding insert object

本文链接：https://blog.csdn.net/xiaoxuehua200812345/article/details/6826189

版权

技術专栏收录该内容

39 篇文章 0 订阅

订阅专栏

1 创建触发器：

public string getInfo(string strConn)
    {
        DataSet ds = new DataSet();
        ds = sql.GetDateSet("select name from sysobjects where type='U'", strConn);
        StringBuilder strSqlInfo = new StringBuilder();
        if (ds.Tables[0].Rows.Count > 0)
        {
            for (int k = 0; k < ds.Tables[0].Rows.Count; k++)
            {

                DataSet dsInfo = new DataSet();
                string strSql = string.Empty;
                string strName = ds.Tables[0].Rows[k]["name"].ToString();
                strSql = "select a.name as [column],b.name as type from syscolumns a,systypes b   where a.id=object_id('" + strName + "') and a.xtype=b.xtype and ( b.name='nvarchar' or b.name='varchar')";

dsInfo = sql.GetDateSet(strSql, strConn);
int i = 0;

                if (dsInfo.Tables[0].Rows.Count > 0)
                {
                      strSqlInfo.Append("CREATE TRIGGER [" + strName + "_TRIGGER] ON [dbo].[" + strName + "] \r\n");
                    strSqlInfo.Append("for update,insert \r\n");
                    strSqlInfo.Append("as \r\n");

                    for (i = 0; i < dsInfo.Tables[0].Rows.Count; i++)
                    {
                        string column = dsInfo.Tables[0].Rows[i]["column"].ToString();

strSqlInfo.Append("declare @string" + i.ToString() + k.ToString()+" varchar(100) \r\n");
strSqlInfo.Append("select @string" + i.ToString() + k.ToString() + "=" + column + " from inserted \r\n");

                    }
                    for (int j = 0; j < dsInfo.Tables[0].Rows.Count; j++)
                    {
                        if (j == 0)
                        {
                            strSqlInfo.Append("if(@string" + j.ToString() + k.ToString()+ " like '%script%' ) \r\n");
                        }
                        else
                        {
                            strSqlInfo.Append(" or (@string" + j.ToString() + k.ToString() + " like '%script%' ) \r\n");
                        }
                    }

                    //strSqlInfo.Append("if(@+" + i.ToString() + " like '%script%' ) \r\n");
                    strSqlInfo.Append("BEGIN \r\n");
                    strSqlInfo.Append("ROLLBACK TRANSACTION \r\n ");
                    strSqlInfo.Append("exec P_sqllink \r\n ");
                    strSqlInfo.Append("END \r\n \r\n");
                    strSqlInfo.Append("GO \r\n \r\n");
                }

            }
        }
        return strSqlInfo.ToString();

    }

2 把创建的触发器保存到.sql文件中

public static bool SaveStrTofile(string filename, string StrContTent, string code)
    {
        bool boolstr = false;
        Encoding codestr = Encoding.GetEncoding(code);
        StreamWriter sw = null;
        try
        {

            sw = new StreamWriter(filename, false, codestr);
            sw.Write(StrContTent);
            sw.Flush();
            boolstr = true;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            sw.Close();
        }
        return boolstr;
    }

3 调用之上的方法：

SaveStrTofile(Server.MapPath("Trigger.sql"), getInfo(sql.conn), "utf-8");