CLR触发器的原理比较简单，但是实现起来要花一番功夫，笔者总结了以下经验，还有一个异常的实例。
CLR触发器实现代码：
- [Microsoft.SqlServer.Server.SqlTrigger(Name = "Trigger1", Target =
- "ERP_STOCKYaoHuoDingDan", Event = "FOR INSERT")]
- public static void DingDanIDSameGongYingShangGUIDMustSame()
- {
- using (SqlConnection connection = new SqlConnection
- (@"context connection=true"))
- {
- connection.Open();
- SqlCommand command = new SqlCommand(@"SELECT COUNT(A.DingDanID)
- FROM ERP_STOCKYaoHuoDingDan AS
- A,INSERTED AS B WHERE A.DingDanID=B.DingDanID AND
- A.GongYingShangGUID<>B.GongYingShangGUID", connection);
- int i=(int)command.ExecuteScalar();
- if (i>0)
- {
- try
- {
- //如果要插入的记录不合法,则回滚.
- Transaction trans = Transaction.Current;
- trans.Rollback();
- }
- catch (SqlException ex)
- {
- connection.Close();
- }
- }
- }
- }
当在CLR触发器内部调用 Transaction.Rollback 方法时，将引发异常并显示不明确的错误消息，必须在try/catch 块中包装此方法或命令。您会看到如下错误消息：
Msg 6549, Level 16, State 1, Procedure trig_InsertValidator, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'trig_InsertValidator': System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting… User transaction, if any, will be rolled back.
此异常是预期行为，需要 try/catch 块才能继续执行代码。当完成执行CLR触发器代码时，将引发另一个异常。
Msg 3991, Level 16, State 1, Procedure trig_InsertValidator, Line 1 The context transaction which was active before entering user defined routine, trigger or aggregate "trig_InsertValidator" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting. The statement has been terminated.
此异常也是预期行为。
调用该CLR触发器的例子
尽管引发了两个异常，仍可以回滚事务，并且更改不会提交到表中。
- try
- {
- //用到此触发器的方法
- }
- catch (SqlException ex)
- {
- if (ex.Number == 3991)
- {
- LabelInfor.Text = "同一张订单必须是同一家供应商。";
- }
- }
- catch (Exception ex)
- {
- ......
- }
以上就是对CLR触发器的实战演练。
【编辑推荐】
