VS2005创建CLR自定义触发器

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://linuxstyle.blog.csdn.net/article/details/1540002
第一步:在Visual Studio 2005中编写代码
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
// [SqlTrigger (Name="myTrigger", Target="Table1", Event="FOR UPDATE")]
public static void myTrigger()
{
SqlTriggerContext myTriggerContext = SqlContext.GetTriggerContext();
SqlPipe myPipe = SqlContext.GetPipe();
SqlCommand myCommand = SqlContext.GetCommand();
if (myTriggerContext.TriggerAction == TriggerAction.Insert)
{
myCommand.CommandText = "select * from spTestTbl";
}
myPipe.Execute(myCommand);
}
}

第二步:编译,部署(Build,Deploy)
第三步:注册到Yukon
语法:
CREATE TRIGGER trigger_name ON table_name FOR INSERT|UPDATE|DELETE
AS EXTERNAL NAME assembly_name.class_name.clr_trigger_name

示例:
CREATE TRIGGER myTrigger ON testTbl1 FOR INSERT
AS EXTERNAL NAME udt.Triggers.myTrigger 
 
展开阅读全文

clr 触发器回滚的问题

06-08

以下一个触发器,是用于判断表的F_DATE 字段(类型 SMALLDATETIME)是不是小于2016-06-01,如小于该日期就禁止插入和更新,全部代码如下rnusing System;rnusing System.Data;rnusing System.ComponentModel;rnusing System.Data.SqlClient;rnusing Microsoft.SqlServer.Server;rnusing System.Data.SqlTypes;rnusing System.Transactions;rnrnpublic partial class Triggersrn rn public static void TR_IU_CX_T_PROD_EFFICIENCY()rn rn using (SqlConnection connection = new SqlConnection(@"context connection=true"))rn rn SqlCommand command;rn SqlDataReader reader;rn DateTime dt;rn connection.Open();rn command = new SqlCommand(@"SELECT F_DATE FROM INSERTED", connection);rn reader = command.ExecuteReader();rn reader.Read();rn dt = (DateTime)reader[0];rn reader.Close();rnrn if (dt < DateTime.Parse("2016-06-01"))rn rn tryrn rn Transaction trans = Transaction.Current;rn SqlContext.Pipe.Send("日期不能小于2016-06-01");rn trans.Rollback();rn rn catch (SqlException ex)rn rn rn rn connection.Close();rn rn rnrn该代码在插入日期小于2016-06-01的数据时,会拦截操作,提示“日期不能小于2016-06-01”,但同时报错:rnrn消息 3991,级别 16,状态 1,过程 TR_IU_CX_T_PROD_EFFICIENCY,第 5 行rn在进入用户定义的例程、触发器或聚合 "TR_IU_CX_T_PROD_EFFICIENCY" 前处于活动状态的上下文事务在其中已终止,这是不允许的。请更改应用程序逻辑以强制实施严格的事务嵌套。rn语句已终止。rnrn不明白上述语句错在哪里?应如何更改?rnrn 论坛

vs2005自定义安装部署

07-09

在用户界面的启动中添加了文本框,我想接受输入,然后判断,比如说吧,输入了数据库用户和密码,验证是否正确,正确就继续,不正确就不显示下一个界面,可以控制吗rnrn用户界面里面的对话框可以控制吗,如何写代码呢,我就知道继承installer类,然后呢rn[code=C#]rn [RunInstaller(true)]rn public partial class Installer1 : Installerrn rn private string dbname;rn private string dbserver;rn private string username;rn private string password;rnrn public Installer1()rn rn InitializeComponent();rn rn public override void Install(IDictionary stateSaver)rn rnrn dbname = "DevTest";rn dbserver = this.Context.Parameters["dbserver"].ToString();rn username = this.Context.Parameters["username"].ToString();rn password = this.Context.Parameters["password"].ToString();rn base.Install(stateSaver);rn rn rn protected override void OnAfterInstall(IDictionary savedState)rn rn base.OnAfterInstall(savedState);rn Form1 frm = new Form1();rn frm.ShowDialog();rn rn protected override void OnBeforeInstall(IDictionary savedState)rn rnrn base.OnBeforeInstall(savedState);rnrn rn rn protected override void OnCommitting(IDictionary savedState)rn rn Form1 form1 = new Form1();rnrn if (form1.ShowDialog() == DialogResult.OK)rn rn using (SqlConnection conn = new SqlConnection(form1.ConnString))rn rn conn.Open();rn if (conn.State==ConnectionState.Open)rn rn Form2 frm = new Form2();rn frm.ShowDialog();rn rnrn rn rn base.OnCommitting(savedState);rn rn public override void Uninstall(IDictionary savedState)rn rn if (savedState == null)rn throw new ApplicationException("uninstall error");rn elsern base.Uninstall(savedState);rn rn [/code]rnrnForm1 form1 = new Form1();这个是我的自定义窗体,我写在那里方法下面才可以自定义呢,是安装有一个向导,配置好才可以继续,就像添加数据源还要先验证数据库登录那样,才可以选择表啊什么的,不知道我说的大家都明白吗,给点提示吧rnmsdn,google,我都找遍了,两天了,没有头绪啊,rn大部分的帖子都是说添加,但是没有控制啊,取值可以用rnrndbname = "DevTest";rn dbserver = this.Context.Parameters["dbserver"].ToString();rn username = this.Context.Parameters["username"].ToString();rn password = this.Context.Parameters["password"].ToString();rnrn我知道,但是我怎么才可以控制用户界面加入的窗体呢,就算不控制他的,我自己写一些,又怎么来和他的安装过程集成呢,就像先出现我的验证,然后继续他的安装进度,谢谢了!rn 论坛

没有更多推荐了,返回首页