原贴:
http://topic.csdn.net/u/20090909/21/5cd93f0c-c751-48d6-b90f-9876fd1100c1.html
jinjazz大哥给了方案。可是测试的结果是set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER trigger [trg_SubmitInsert] on [dbo].[CmppSubmit]
instead of insert
asbegin
declare @SequenceId char(10)
execute GetSequenceId @SequenceId out insert into CmppSubmit( Sequence_Id, Registered_Delivery, Service_Id, Fee_UserType,
Fee_terminal_Id, Fee_terminal_type, Msg_Fmt, FeeType, FeeCode,Src_Id,Dest_terminal_Id,
Dest_terminal_type, Msg_Content, LinkID)
select @SequenceId , Registered_Delivery, Service_Id, Fee_UserType,
Fee_terminal_Id, Fee_terminal_type, Msg_Fmt, FeeType, FeeCode,Src_Id,Dest_terminal_Id,
Dest_terminal_type, Msg_Content, LinkID
from insertedendCmppSubmit每行中Sequence_Id都一样
难道触发器不是行都执行吗?谢谢!
http://topic.csdn.net/u/20090909/21/5cd93f0c-c751-48d6-b90f-9876fd1100c1.html
jinjazz大哥给了方案。可是测试的结果是set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER trigger [trg_SubmitInsert] on [dbo].[CmppSubmit]
instead of insert
asbegin
declare @SequenceId char(10)
execute GetSequenceId @SequenceId out insert into CmppSubmit( Sequence_Id, Registered_Delivery, Service_Id, Fee_UserType,
Fee_terminal_Id, Fee_terminal_type, Msg_Fmt, FeeType, FeeCode,Src_Id,Dest_terminal_Id,
Dest_terminal_type, Msg_Content, LinkID)
select @SequenceId , Registered_Delivery, Service_Id, Fee_UserType,
Fee_terminal_Id, Fee_terminal_type, Msg_Fmt, FeeType, FeeCode,Src_Id,Dest_terminal_Id,
Dest_terminal_type, Msg_Content, LinkID
from insertedendCmppSubmit每行中Sequence_Id都一样
难道触发器不是行都执行吗?谢谢!
我单独测试过,每次都能生成不同id的
Fee_terminal_Id, Fee_terminal_type, Msg_Fmt, FeeType, FeeCode,Src_Id,Dest_terminal_Id,
Dest_terminal_type, Msg_Content, LinkID)
(
select GetSequenceId() ,Registered_Delivery, Service_Id, Fee_UserType,
Fee_terminal_Id, Fee_terminal_type, Msg_Fmt, FeeType, FeeCode,Src_Id,Dest_terminal_Id,
Dest_terminal_type, Msg_Content, LinkID from TestSubmit with (TABLOCKX)
)可是GetSequenceId()如果是函数就无法执行了(因为该函数中包含了Update语句)~~
只能把GetSequenceId做成存储过程,可是如果是存储过程,又不知如何放在上面GetSequenceId()的位置
难道插入CmppSubmit后再用游标一行一行加sequenceId
但受到了id的限制,我想函数GetSequenceId()最好不管里面包含什么都能执行。
go
--函数
if object_id('f_aa')>0 drop function dbo.f_aa
go
create function f_aa ()
returns int
begin
declare @t int
select @t=t from openrowset('SQLOLEDB' , 'Server=.;Trusted_Connection=yes;DataBase=tempdb' ,'exec p_aa')
return @t
end
go
--过程
if object_id('p_aa')>0 drop proc p_aa
go
create proc p_aa
as
begin
select checksum(newid()) as t
end
go
--测试
select top 10 dbo.f_aa(),name from sysobjects
/*
684451728 sysrowsetcolumns
-134388719 sysrowsets
1262235715 sysallocunits
1310489391 sysfiles1
1362565448 syshobtcolumns
-2121494822 syshobts
-337810681 sysftinds
-828063421 sysserefs
429940715 sysowners
633962801 sysprivs
*/
ALTER PROCEDURE [dbo].[GetCurrentNum]
asbegin tran declare @currentNum char(4) Select top 1 @currentNum=Sequence from Sequence with (TABLOCKX) update Sequence set Sequence=Sequence+1
update Sequence set Sequence=1 where Sequence > 10000commit tran
select @currentNum as currentNumalter function [dbo].[GetSequenceId]()
returns char(10)
asbegin declare @currentDate char(4)
declare @currentNum varchar(5)
declare @SequenceId char(10)select @currentNum = currentNum from openrowset('SQLOLEDB' ,'Server=.;Trusted_Connection=yes;DataBase=CmppSpBil' ,'exec GetCurrentNum')set @currentDate= right( Convert(char(8),GetDate(),112 ),4)declare @iIndex tinyint
set @iIndex=len(@currentNum)
while (@iIndex<5)
begin
set @currentNum='0'+@currentNum
set @iIndex=@iIndex+1;
endset @SequenceId = '2'+@currentDate+ @currentNum
return @SequenceId endselect dbo.GetSequenceId() 消息 7357,级别 16,状态 1,第 1 行
无法处理对象 "exec GetCurrentNum"。链接服务器 "(null)" 的 OLE DB 访问接口 "SQLNCLI" 指示该对象没有列,或当前用户没有访问该对象的权限。
我这里是好的,是阿在不行,如果是2005的话,你写clr函数吧
没用过CLR,但查了一下,是不是C#类中我就直接生成结果就可以了吗?
public static class CmppProtocol
{ private static UInt32 sequenceId =0 ;
public static UInt32 SequenceId
{
get
{ string strDate = string.Format("{0:MMdd}", DateTime.Now); if (sequenceId < 10000)
{
sequenceId++;
}
else
{
sequenceId = 0;
}
return UInt32.Parse("1" + strDate + sequenceId.ToString().PadLeft(5, '0'));
}
}}
http://blog.csdn.net/jinjazz/archive/2008/12/05/3455854.aspx
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions
{
private static UInt32 sequenceId = 0;
[Microsoft.SqlServer.Server.SqlFunction]
public static UInt32 GetSequenceId()
{
string strDate = string.Format("{0:MMdd}", DateTime.Now); if (sequenceId < 10000)
{
sequenceId++;
}
else
{
sequenceId = 0;
}
return UInt32.Parse("2" + strDate + sequenceId.ToString().PadLeft(5, '0')); }
};
按F5时 :
错误 1 由于 safe 程序集 'CreateSequenceID' 中 'UserDefinedFunctions' 类型的方法 'GetSequenceId' 将存储到静态字段,CREATE ASSEMBLY 失败。在 safe 程序集中不允许存储到静态字段。 CreateSequenceID我把属性改为不安全 按F5指示:错误 1 针对程序集 'CreateSequenceID' 的 CREATE ASSEMBLY 失败,因为程序集 'CreateSequenceID' 未获授权(PERMISSION_SET = UNSAFE)。 当符合以下两个条件之一时,将对程序集授权: 数据库所有者(DBO)具有 UNSAFE ASSEMBLY 权限,而且数据库的 TRUSTWORTHY 属性处于打开状态;或者,程序集签名时所使用的证书或非对称密钥所对应的登录名具有 UNSAFE ASSEMBLY 权限。 CreateSequenceID
go
CREATE ASSEMBLY testAss FROM 'E:\xxx.dll' WITH PERMISSION_SET = UnSAFE;
go
但在原贴中
http://topic.csdn.net/u/20090909/21/5cd93f0c-c751-48d6-b90f-9876fd1100c1.html guguda2008也出了不少了,但由于太早结帐,没看到他的回复.所以加分补回guguda2008~~谢谢大家。
特别是jinjazz与guguda2008